106 – MySQL Query Select Statement for Managing Payroll System Module

0
373

The MySQL Query Select Statement returns a result set of records from one or more tables. A select statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data manipulation language (DML) command.

MySQL Query Select Statement

In our case, our tblEmployee contains sample data for the select statement purposes. See image below for the employee information details. I assume that you know how to add some data values to our XAMPP in the localhost server.

The purpose of this presentation is to load these data in our frmListofEmployee which will test our Select statement module to its functionality. The image below shows the procedure for this testing.

select statement
select statement

Double click tsEmployee button and add the code below. This is to call the frmListofEmployee form.

  1. Private Sub tsEmployee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsEmployee.Click
  2. frmListofEmployee.Show()
  3. End Sub

Before we define our frmListofEmployee load event, we have to modify first our Payroll Module in order to define the select query and the loading of data to our DataGridView object.

  1.  
  2.  
  3. Dim cmd As New MySqlCommand
  4. Dim da As New MySqlDataAdapter
  5. Dim dt As New DataTable
  6. Dim dReader As MySqlDataReader

We declare first our variables for MySqlCommand, MysqlDataAdapter, DataTable, and MysqlDataReader. Then, the code below show our public sub findthis().

  1. Public Sub findthis(ByVal sql As String)
  2. Try
  3. con.Open()
  4. With cmd
  5. .Connection = con
  6. .CommandText = sql
  7. End With
  8. Catch ex As Exception
  9. MsgBox(ex.Message, MsgBoxStyle.Exclamation)
  10. Finally
  11.  
  12. da.Dispose()
  13. con.Close()
  14.  
  15. End Try
  16.  
  17. End Sub

Last modification would be our public sub LoadData for our data reader definition.

  1. Public Sub LoadData(ByVal obj As Object, ByVal param As String)
  2. Try
  3. con.Open()
  4. dReader = cmd.ExecuteReader()
  5.  
  6. Select Case param
  7.  
  8. Case "EmployeeList"
  9. Do While dReader.Read = True
  10. obj.Rows.Add(dReader(0), dReader(1), dReader(2), dReader(3))
  11. Loop
  12. End Select
  13.  
  14. Catch ex As Exception
  15. MsgBox(ex.Message)
  16. Finally
  17. da.Dispose()
  18. con.Close()
  19. End Try
  20. End Sub
  21. End Module

Go now to your frmListofEmployee and define the form load event. Add the following code below.

  1. Private Sub frmListofEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.  
  3. dgvListofEmployee.Rows.Clear()
  4. findthis("SELECT `EMPLOYEEID`, concat(ucase(`LASTNAME`), ', ', ucase(`FIRSTNAME`), ' ', ucase(`MIDDLENAME`)) as Name, `EMPADDRESS`, `EMPCONTACTNO` FROM `tblemployee` WHERE `SCHOOLID`=" & SCHOOLIDNO & " order by Name")
  5. LoadData(dgvListofEmployee, "EmployeeList")
  6.  
  7. End Sub

The first line is to clear first the rows in our DataGridView. We call now our findthis sub which is define in our Payroll Module and write our select statement. And lastly, we call our LoadData public sub to display the data from our database particularly from the tblemployee and display it in our DatagridView as shown in our image above.

If you have any question or suggestion about MySQL Query Select Statement, please feel free to contact me at our contact page. Or you can check this sample about How to Load MySQL Data Using VB.Net.

 

Facebook Comments
(Visited 417 times, 1 visits today)

LEAVE A REPLY

Please enter your comment!
Please enter your name here