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.
Double click tsEmployee button and add the code below. This is to call the frmListofEmployee form.
[vbnet]
Private Sub tsEmployee_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsEmployee.Click
frmListofEmployee.Show()
End Sub
[/vbnet]
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.
[vbnet]
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Dim dt As New DataTable
Dim dReader As MySqlDataReader
[/vbnet]
We declare first our variables for MySqlCommand, MysqlDataAdapter, DataTable, and MysqlDataReader. Then, the code below show our public sub findthis().
[vbnet]
Public Sub findthis(ByVal sql As String)
Try
con.Open()
With cmd
.Connection = con
.CommandText = sql
End With
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation)
Finally
da.Dispose()
con.Close()
End Try
End Sub
[/vbnet]
Last modification would be our public sub LoadData for our data reader definition.
[vbnet]
Public Sub LoadData(ByVal obj As Object, ByVal param As String)
Try
con.Open()
dReader = cmd.ExecuteReader()
Select Case param
Case “EmployeeList”
Do While dReader.Read = True
obj.Rows.Add(dReader(0), dReader(1), dReader(2), dReader(3))
Loop
End Select
Catch ex As Exception
MsgBox(ex.Message)
Finally
da.Dispose()
con.Close()
End Try
End Sub
End Module
[/vbnet]
Go now to your frmListofEmployee and define the form load event. Add the following code below.
[vbnet]
Private Sub frmListofEmployee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dgvListofEmployee.Rows.Clear()
findthis(“SELECT `EMPLOYEEID`, concat(ucase(`LASTNAME`), ‘, ‘, ucase(`FIRSTNAME`), ‘ ‘, ucase(`MIDDLENAME`)) as Name, `EMPADDRESS`, `EMPCONTACTNO` FROM `tblemployee` WHERE `SCHOOLID`=” & SCHOOLIDNO & ” order by Name”)
LoadData(dgvListofEmployee, “EmployeeList”)
End Sub
[/vbnet]
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.