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

0
449

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.

  1. Private Sub dgvListofEmployee_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvListofEmployee.CellClick
  2.  
  3. EmployeeIDno = dgvListofEmployee.CurrentRow.Cells(0).Value
  4.  
  5. End Sub

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

  1. Public EmployeeIDno As Integer
  2.  
  3. Public CASESWITCH As String = ""
  4.  
  5.  

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

  1. Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
  2. CASESWITCH = "EditEmployee"
  3. frmNewEmployee.btnSave.Text = "Update"
  4. frmNewEmployee.Show()
  5. 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.

  1. Private Sub frmNewEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.  
  3. If CASESWITCH = "EditEmployee" Then
  4.  
  5. findthis("SELECT * FROM `tblemployee` WHERE `SCHOOLID` = " & SCHOOLIDNO & " and `EMPLOYEEID` =" & EmployeeIDno & "")
  6. LoadSingleResult("EmployeeDetails")
  7.  
  8. End If
  9.  
  10. End Sub
  11.  
  12.  

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.

  1. Public Sub LoadSingleResult(ByVal param As String)
  2.  
  3. Try
  4. con.Open()
  5. dReader = cmd.ExecuteReader()
  6.  
  7. Select Case param
  8.  
  9. Case "EmployeeDetails"
  10.  
  11. With frmNewEmployee
  12. Do While dReader.Read = True
  13.  
  14. .txtEmplD.Enabled = False
  15. .txtEmplD.Text = dReader("EMPLOYEEID")
  16. .txtLastName.Text = dReader("LASTNAME")
  17. .txtFirstName.Text = dReader("FIRSTNAME")
  18. .txtMiddleName.Text = dReader("MIDDLENAME")
  19. .txtAddress.Text = dReader("EMPADDRESS")
  20. .txtContactNo.Text = dReader("EMPCONTACTNO")
  21. .cbCivilStatus.Text = dReader("EMPSTATUS")
  22.  
  23. .cbdepartment.SelectedValue = dReader("DEPARTMENTID")
  24. .dtpDateHired.Text = dReader("DATEHIRED")
  25. .txtNoDependents.Text = dReader("NOOFDEPENDENTS")
  26.  
  27. Dim empsex As String
  28.  
  29. empsex = dReader("EMPSEX")
  30. If empsex = "Male" Then
  31. .rbMale.Checked = True
  32. ElseIf empsex = "" Then
  33. .rbFemale.Checked = True
  34. End If
  35.  
  36. Loop
  37. End With
  38.  
  39. Catch ex As Exception
  40. MsgBox(ex.Message)
  41. Finally
  42. da.Dispose()
  43. con.Close()
  44. End Try
  45.  
  46. End Sub
  47.  
  48.  

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.

  1. Public Sub doUpdate()
  2.  
  3. DATEHIRED = Format(dtpDateHired.Value, "yyyy-MM-dd")
  4.  
  5. If txtLastName.Text = "" Then
  6. ErrorProvider1.SetError(txtLastName, "Please provide Last Name.")
  7. Else
  8. ErrorProvider1.SetError(txtLastName, String.Empty)
  9. If txtFirstName.Text = "" Then
  10. ErrorProvider1.SetError(txtFirstName, "Please First Name.")
  11. Else
  12. ErrorProvider1.SetError(txtFirstName, String.Empty)
  13. If txtMiddleName.Text = "" Then
  14. ErrorProvider1.SetError(txtMiddleName, "Please provide Middle Name.")
  15. Else
  16. ErrorProvider1.SetError(txtMiddleName, String.Empty)
  17.  
  18. If rbFemale.Checked Then
  19. empsex = "Female"
  20. ElseIf rbMale.Checked Then
  21. empsex = "Male"
  22. End If
  23. IsSuccessResult = UpdateInfo("update `tblemployee` SET `LASTNAME` = '" & txtLastName.Text & "', `FIRSTNAME`='" & txtFirstName.Text & "', " & _
  24. " `MIDDLENAME`= '" & txtMiddleName.Text & "', `EMPADDRESS`='" & txtAddress.Text & "', `EMPCONTACTNO`='" & txtContactNo.Text & "', " & _
  25. " `EMPSTATUS`='" & cbCivilStatus.Text & "', `EMPSEX` ='" & empsex & "', `DATEHIRED` ='" & DATEHIRED & "', `DEPARTMENTID` =" & cbdepartment.SelectedValue & ", `NOOFDEPENDENTS` =" & txtNoDependents.Text & " WHERE `EMPLOYEEID` =" & txtEmplD.Text & "")
  26. If IsSuccessResult = True Then
  27. doLoad()
  28. Me.Close()
  29.  
  30. Else
  31. MsgBox(ERRORPROCESS)
  32. End If
  33. End If
  34. End If
  35. End If
  36.  
  37. 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.

Facebook Comments
(Visited 469 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here