109 – Update Statement Using Visual Basic Application for Managing Payroll System Module

The principles covering information standards covers the accuracy of personal data. The Data Protection Act imposes obligations to ensure the accuracy of the personal data you process. It must also be kept up to date where necessary.

This requirement is closely linked with the requirement under our Payroll System specifically on the management of Employee Information. Ensuring this accuracy we need to have an application to update information which during the encoding committed by human errors.

Payroll system
Payroll system

As shown in our image above, the procedure is to select one data in the DataGridview, click the Edit Button and Update the No. of Dependents into 2 then click the Update Button.

We add now the code upon selecting the DataGridview which is the CellClick event.


Private Sub dgvListofEmployee_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvListofEmployee.CellClick

EmployeeIDno = dgvListofEmployee.CurrentRow.Cells(0).Value

End Sub


Note that we have to declare EmployeeIDno variable and CASESWITCH in our PayrollMod.vb Module.


Public EmployeeIDno As Integer

Public CASESWITCH As String = “”



Next is the Edit button event and add this following code.


Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
CASESWITCH = “EditEmployee”
frmNewEmployee.btnSave.Text = “Update”
End Sub


The purpose of the  CASESWITCH is to determine the query and loading of data once the frmNewEmployee form is called. That is why in the frmNewEmployee form load event you add the following code below.


Private Sub frmNewEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

If CASESWITCH = “EditEmployee” Then

findthis(“SELECT * FROM `tblemployee` WHERE `SCHOOLID` = ” & SCHOOLIDNO & ” and `EMPLOYEEID` =” & EmployeeIDno & “”)

End If

End Sub



You have notice in this coding presentation we have a new sub procedure which is the LoadSingleResult(). This is to retrieve the data information and place it in our frmNewEmployee fields object that is ready for updates. Go now to our PayrollMod.vb and update the following code.

Public Sub LoadSingleResult(ByVal param As String)

dReader = cmd.ExecuteReader()

Select Case param

Case “EmployeeDetails”

With frmNewEmployee
Do While dReader.Read = True

.txtEmplD.Enabled = False
.txtEmplD.Text = dReader(“EMPLOYEEID”)
.txtLastName.Text = dReader(“LASTNAME”)
.txtFirstName.Text = dReader(“FIRSTNAME”)
.txtMiddleName.Text = dReader(“MIDDLENAME”)
.txtAddress.Text = dReader(“EMPADDRESS”)
.txtContactNo.Text = dReader(“EMPCONTACTNO”)
.cbCivilStatus.Text = dReader(“EMPSTATUS”)

.cbdepartment.SelectedValue = dReader(“DEPARTMENTID”)
.dtpDateHired.Text = dReader(“DATEHIRED”)
.txtNoDependents.Text = dReader(“NOOFDEPENDENTS”)

Dim empsex As String

empsex = dReader(“EMPSEX”)
If empsex = “Male” Then
.rbMale.Checked = True
ElseIf empsex = “” Then
.rbFemale.Checked = True
End If

End With

Catch ex As Exception
End Try

End Sub



Next is to go to our btnSave button click. Since, we have coded already our doUpdate() in our else condition, we are now ready to add the doUpdate() sub procedure in our class.


Public Sub doUpdate()

DATEHIRED = Format(dtpDateHired.Value, “yyyy-MM-dd”)

If txtLastName.Text = “” Then
ErrorProvider1.SetError(txtLastName, “Please provide Last Name.”)
ErrorProvider1.SetError(txtLastName, String.Empty)
If txtFirstName.Text = “” Then
ErrorProvider1.SetError(txtFirstName, “Please First Name.”)
ErrorProvider1.SetError(txtFirstName, String.Empty)
If txtMiddleName.Text = “” Then
ErrorProvider1.SetError(txtMiddleName, “Please provide Middle Name.”)
ErrorProvider1.SetError(txtMiddleName, String.Empty)

If rbFemale.Checked Then
empsex = “Female”
ElseIf rbMale.Checked Then
empsex = “Male”
End If
IsSuccessResult = UpdateInfo(“update `tblemployee` SET `LASTNAME` = ‘” & txtLastName.Text & “‘, `FIRSTNAME`='” & txtFirstName.Text & “‘, ” & _
” `MIDDLENAME`= ‘” & txtMiddleName.Text & “‘, `EMPADDRESS`='” & txtAddress.Text & “‘, `EMPCONTACTNO`='” & txtContactNo.Text & “‘, ” & _
” `EMPSTATUS`='” & cbCivilStatus.Text & “‘, `EMPSEX` ='” & empsex & “‘, `DATEHIRED` ='” & DATEHIRED & “‘, `DEPARTMENTID` =” & cbdepartment.SelectedValue & “, `NOOFDEPENDENTS` =” & txtNoDependents.Text & ” WHERE `EMPLOYEEID` =” & txtEmplD.Text & “”)
If IsSuccessResult = True Then

End If
End If
End If
End If

End Sub


Here we go, we are now ready to process the four procedure for Updating our employee information. Proceed now to our next lesson for removing a particular data in the database.

Leave a Comment