Student's Registration Form with AutoIncrement/AutoNumber in a TextBox

Submitted by janobe on Friday, December 20, 2013 - 06:26.

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”.

CREATE TABLE IF NOT EXISTS `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. Resgistration 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.

Add new comment

1420 views