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.

[vbnet]

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

[/vbnet]

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

[vbnet]

Public EmployeeIDno As Integer

Public CASESWITCH As String = “”

 

[/vbnet]

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

[vbnet]

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

[/vbnet]

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.

[vbnet]

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 & “”)
LoadSingleResult(“EmployeeDetails”)

End If

End Sub

 

[/vbnet]

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.

[vbnet]
Public Sub LoadSingleResult(ByVal param As String)

Try
con.Open()
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

Loop
End With

Catch ex As Exception
MsgBox(ex.Message)
Finally
da.Dispose()
con.Close()
End Try

End Sub

 

[/vbnet]

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.

[vbnet]

Public Sub doUpdate()

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

If txtLastName.Text = “” Then
ErrorProvider1.SetError(txtLastName, “Please provide Last Name.”)
Else
ErrorProvider1.SetError(txtLastName, String.Empty)
If txtFirstName.Text = “” Then
ErrorProvider1.SetError(txtFirstName, “Please First Name.”)
Else
ErrorProvider1.SetError(txtFirstName, String.Empty)
If txtMiddleName.Text = “” Then
ErrorProvider1.SetError(txtMiddleName, “Please provide Middle Name.”)
Else
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
doLoad()
Me.Close()

Else
MsgBox(ERRORPROCESS)
End If
End If
End If
End If

End Sub

[/vbnet]

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