Weev
Cyber Threat Analyst
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
300 XP
In this tutorial I will create a Student's Registration Form. I will show you how to use a Module in setting up your codes and minimize bunch of codes in your Student's Registration Form. And at the same time, it AutoIncrement a TextBox and generates the AutoNumber of the Student's Id.
First create a Table in the MySQL Database named “studentdb”.
Then, open Visual Basic 2008 , create a Project and a Student's Registration Form.
Description: I set the properties of theTextBox(txtid) into disabled so that you cannot set or change the id and the program will set it automatically.
Create a Module named “studReg” .Set up your connection in MySQL Database and Visual Basic 2008 then declare the variables and all the classes that you needed.
Create your Sub Procedure for saving Student's record.
Then, create a Sub Procedure for retrieving the total number of rows in the table of your Database, which will appear in the TextBox. I know you are confused of how this procedure AutoIncrement/AutoNumber the value in the TextBox. The technique is, set this procedure (you can see it below) in the first load and put this after saving your data. You will know and see what I’m talking about when you came up in the next step.
Go back to the Design Views , double click the Form and call your Sub name for your AutoIncrement/AutoNumber.
Go back to the Design Views again, double click the Save Button and call the Sub name for saving the data. Then, call again the Sub name of your AutoIncrement/AutoNumber procedure to increment the value in the TextBox.
The complete Source Code is included. Download and run it on your computer.
Download
First create a Table in the MySQL Database named “studentdb”.
- CREATE
TABLE
[url=http://dev.mysql.com/doc/refman/%35%2E%31/en/control-flow-functions.html]IF
NOT
EXISTS
[/url] `student`
(
- `s_
id`
int
(
11
)
NOT
NULL
,
- `s_
name`
varchar
(
40
)
NOT
NULL
,
- `lastname`
varchar
(
40
)
NOT
NULL
,
- `middlename`
varchar
(
40
)
NOT
NULL
,
- `s_
address`
varchar
(
30
)
NOT
NULL
,
- `s_
age`
int
(
11
)
NOT
NULL
,
- `s_
bday`
varchar
(
30
)
NOT
NULL
,
- `s_
bplace`
varchar
(
30
)
NOT
NULL
,
- `s_
gender`
varchar
(
30
)
NOT
NULL
,
- `s_
status`
varchar
(
30
)
NOT
NULL
,
- `s_
contact`
varchar
(
30
)
NOT
NULL
,
- `s_
guardian`
varchar
(
30
)
NOT
NULL
,
- `s_
guardian_
add`
varchar
(
40
)
NOT
NULL
,
- `s_
guardian_
contact`
varchar
(
30
)
NOT
NULL
,
- `sy`
varchar
(
20
)
NOT
NULL
,
- `yr`
int
(
5
)
NOT
NULL
,
- PRIMARY KEY
(
`s_
id`
)
- )
ENGINE
=
InnoDB
DEFAULT
CHARSET
=
latin1;
Then, open Visual Basic 2008 , create a Project and a Student's Registration Form.
Description: I set the properties of theTextBox(txtid) into disabled so that you cannot set or change the id and the program will set it automatically.
Create a Module named “studReg” .Set up your connection in MySQL Database and Visual Basic 2008 then declare the variables and all the classes that you needed.
- 'add reference
- Imports
MySql.
Data
.
MySqlClient
- Module
studReg
- 'create a connection in public function
- Public
Function
mysqldb(
)
As
MySqlConnection
- 'to return new connection
- Return
New
MySqlConnection(
"server=localhost;user id=root;database=studentdb"
)
- End
Function
- 'to pass on the mysql connection in the con
- Public
con As
MySqlConnection =
mysqldb(
)
- 'the set of commands in MySQL
- Public
cmd As
New
MySqlCommand
- 'brigde between the database and datatable in retrieving and saving the data
- Public
da As
New
MySqlDataAdapter
- 'represents a specific table in the database
- Public
dt As
New
DataTable
- 'declare variable result as interger
- Public
result As
Integer
- End
Class
Create your Sub Procedure for saving Student's record.
- 'create a sub procedure with parameters type of string
- Public
Sub
create(
ByVal
sql As
String
)
- Try
- 'open the connection
- con.
Open
(
)
- 'set up your commands
- 'it holds the data to be executed
- With
cmd
- 'pass on the value of con to the MySQL command which is Connection
- .
Connection
=
con
- 'role of this is to return text presented by a command object
- .
CommandText
=
sql
- 'executes the data to save in the database
- result =
cmd.
ExecuteNonQuery
- 'if the data executes less than 0 it will not be saved
- 'but if the data executes greater than 0 it will be saved
- If
result =
0
Then
- MsgBox
(
"Error in Registration!"
, MsgBoxStyle.
Exclamation
)
- Else
- MsgBox
(
"You have successfully registered."
)
- End
If
- End
With
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- 'close the connection
- con.
Close
(
)
- End
Sub
Then, create a Sub Procedure for retrieving the total number of rows in the table of your Database, which will appear in the TextBox. I know you are confused of how this procedure AutoIncrement/AutoNumber the value in the TextBox. The technique is, set this procedure (you can see it below) in the first load and put this after saving your data. You will know and see what I’m talking about when you came up in the next step.
- Public
Sub
autoNumber(
ByVal
sql As
String
, ByRef
txt As
Object
)
- Try
- 'open the connection
- con.
Open
(
)
- 'to set a new specific table in the database
- dt =
New
DataTable
- 'set up your commands
- 'it holds the data to be executed
- With
cmd
- 'pass on the value of con to the MySQL command which is Connection
- .
Connection
=
con
- 'role of this is to return text presented by a command object
- .
CommandText
=
sql
- End
With
- 'for retrieval of data and filling the table
- da =
New
MySqlDataAdapter(
sql, con)
- da.
Fill
(
dt)
- 'to put the maxrow of a table in the textbox
- txt.
text
=
"000"
&
dt.
Rows
.
Count
- Catch
ex As
Exception
- MsgBox
(
ex.
Message
)
- End
Try
- da.
Dispose
(
)
- 'close the connection
- con.
Close
(
)
- End
Sub
Go back to the Design Views , double click the Form and call your Sub name for your AutoIncrement/AutoNumber.
- Private
Sub
Form1_Load(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
MyBase
.
Load
- ''set your autonumber on the first load
- autoNumber(
"SELECT * FROM student"
, txtid)
- End
Sub
Go back to the Design Views again, double click the Save Button and call the Sub name for saving the data. Then, call again the Sub name of your AutoIncrement/AutoNumber procedure to increment the value in the TextBox.
- Private
Sub
btnSave_Click(
ByVal
sender As
System.
Object
, ByVal
e As
System.
EventArgs
)
Handles
btnSave.
Click
- 'declaring variable sql as string
- Dim
sql As
String
- 'declaring variable radio as string
- Dim
radio As
String
- 'conditioning the radiobutton to set the male and female
- ' if the radiobutton is checked it is equals to Female and if not it is equals to male
- If
rdoFemaleMale.
Checked
=
True
Then
- radio =
"Female"
- Else
- radio =
"Male"
- End
If
- 'put the insert query to variable sql as string
- sql =
"INSERT INTO student (`s_id`, `s_fname`, `lastname`, `middlename`,"
_
- &
"`s_address`, `s_age`, `s_bday`, `s_bplace`, `s_gender`,"
_
- &
"`s_status`, `s_guardian`, `s_guardian_relation`,`s_guardian_add`,"
_
- &
"`s_guardian_contact`,`sy`, `yr`) VALUES ('"
&
txtid.
Text
&
"','"
_
- &
txtFname.
Text
&
"','"
&
txtLname.
Text
&
"','"
&
txtMname.
Text
&
"','"
_
- &
rchAddress.
Text
&
"','"
&
txtAge.
Text
&
"','"
&
dtpDbirth.
Text
&
"','"
_
- &
rchPbirth.
Text
&
"','"
&
radio &
"','"
&
cboStatus.
Text
&
"','"
_
- &
txtGuardian.
Text
&
"','"
&
txtRelation.
Text
&
"','"
&
rchGaddress.
Text
&
"','"
_
- &
txtContact.
Text
&
"' ,'"
&
cbosy.
Text
&
"','"
&
cboYear.
Text
&
"')"
- 'call your public sub name and put the sql in the parameters list
- create(
sql)
- 'for clearing the textbox
- cleartext(
Me
)
- 'call your autonumber to set a new value
- autoNumber(
"SELECT * FROM student"
, txtid)
- End
Sub
The complete Source Code is included. Download and run it on your computer.
Download
You must upgrade your account or reply in the thread to view the hidden content.