Updating Records of the Student’s Registration Form

Submitted by janobe on Sunday, December 29, 2013 - 17:44.
This is the continuation of my previous tutorial which is the Navigation of a Student’s Registration Form. This time, I will teach you how to update the records in a MySQL Database. I change some features in the Registration Form and the Data type of the year in a MySQL Database. To start with: Change your Int data type into VarChar in the yr field in MySQL Database. So that, you can put characters and numbers on it.
  1. ALTER TABLE  `student` CHANGE  `yr`  `yr` VARCHAR( 11 ) NOT NULL
Open the Visual Basic 2008, open the file of Student's Registration Form and add the Button for the update. It will look like this. First Form After that, double click the Form and do the following code in the Form1_Load for the dropdown style of a Combobox. If you run the project, the ComboBox will look like this.
  1. 'change the dropdown style of the Combobox
  2.  cboStatus.DropDownStyle = ComboBoxStyle.DropDownList
  3.  cbosy.DropDownStyle = ComboBoxStyle.DropDownList
  4.  cboYear.DropDownStyle = ComboBoxStyle.DropDownList
Second Form Click the Module named "studReg" and create a Sub procedure for updating the records in MySQL Database. Just like this.
  1. Public Sub updates(ByVal sql As String)
  2.         Try
  3.             'open the connection
  4.             con.Open()
  5.             'set up your commands
  6.             'it holds the data to be executed
  7.             With cmd
  8.                 'pass on the value of con to the MySQL command which is Connection
  9.                 .Connection = con
  10.                 'role of this is to return text presented by a command object
  11.                 .CommandText = sql
  12.  
  13.                 'executes the data to update in the database
  14.                 result = cmd.ExecuteNonQuery
  15.                 'if the data executes less than 0 it will not be updated
  16.                 'but if the data executes greater than 0 it will be updated
  17.                 If result = 0 Then
  18.                     MsgBox("Error for updating student.")
  19.                 Else
  20.                     MsgBox("Student has been updated.")
  21.                 End If
  22.             End With
  23.         Catch ex As Exception
  24.             MsgBox(ex.Message)
  25.         End Try
  26.  
  27.         con.Close()
  28.     End Sub
Go back to the Design Views, double click the Update Button and do the following code for updating the records of the student.
  1. Private Sub btn_Update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Update.Click
  2.         Try
  3.  
  4.             'declaring variable sql as string
  5.             Dim sql As String
  6.             'declaring variable radio as string
  7.             Dim radio As String
  8.  
  9.             'conditioning the radiobutton to set the male and female
  10.             ' if the radiobutton is checked it is equals to Female and if not it is equals to male
  11.             If rdoFemaleMale.Checked = True Then
  12.                 radio = "Female"
  13.             Else
  14.                 radio = "Male"
  15.             End If
  16.  
  17.             'Put the Update query to a variable sql
  18.             sql = "Update student set `s_fname` = '" & txtFname.Text _
  19.                                 & "', `lastname` = '" & txtLname.Text _
  20.                                 & "', `middlename`='" & txtMname.Text _
  21.                                 & "', `s_address`= '" & rchAddress.Text _
  22.                                 & "', `s_age`='" & txtAge.Text _
  23.                                 & "', `s_bday`='" & dtpDbirth.Text _
  24.                                 & "', `s_bplace`='" & rchPbirth.Text _
  25.                                 & "', `s_gender`='" & radio _
  26.                                 & "', `s_status`='" & cboStatus.Text _
  27.                                 & "', `s_guardian`='" & txtGuardian.Text _
  28.                                 & "', `s_guardian_relation`='" & txtRelation.Text _
  29.                                 & "', `s_guardian_add`='" & rchGaddress.Text _
  30.                                 & "', `s_guardian_contact`='" & txtContact.Text _
  31.                                 & "', `sy`='" & cbosy.Text _
  32.                                 & "', `yr`='" & cboYear.Text _
  33.                                 & "'  where `s_id` = '" & txtid.Text & "'"
  34.             updates(sql) 'call your public SubName for updatin data and put the sql in the parameters list
  35.  
  36.             cleartext(Me) ' call a public SubName for clearing and put the object form in the parameters list
  37.  
  38.             Call Form1_Load(sender, e) 'call the form_load to set up again the AutoNumber
  39.         Catch ex As Exception
  40.             MsgBox(ex.Message)
  41.         End Try
  42.     End Sub
After that, go back to the Design Views again, double click the Textbox(txtid) and do the following code for conditioning the save and add buttons which to be enable or disable it.
  1.  Private Sub txtid_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtid.TextChanged
  2.         Try
  3.             'If the value of TextBox change.
  4.             If txtid.Text = maxrows.ToString Then 'check if the value of the TextBox  is equal to the total number of rows in the table
  5.                 'result:
  6.                 btn_Update.Enabled = False 'disable the Update Button
  7.                 btnSave.Enabled = True 'enable the Save Button
  8.             Else 'The TextBox is less than to the total number of rows in the table
  9.                 'result:
  10.                 btn_Update.Enabled = True 'enable the Update Button
  11.                 btnSave.Enabled = False 'disable the Save Button
  12.             End If
  13.         Catch ex As Exception
  14.             MsgBox(ex.Message)
  15.         End Try
  16.        
  17.     End Sub
Complete Source Code is included.
Tags
visual basic
MYSQL
database
Students Registration

Add new comment

About text formats
  • 847 views