How to Navigate Records In the DataGridView Using Visual Basic 2008 And MySQL Database

0
686

In this tutorial I will teach you how to navigate records in the DatagridView using Visual Basic 2008 and MySQL Database. This will show you that you can limit your displayed records in the Datagridview. And there’s no need for you to scroll down to it whenever you have plenty of records. All you have to do is to click next and previous buttons. And the records will be displayed in chronological order.

 

 

Let’s begin:

 

 

First, create a database named “payroll”.

[mysql] CREATE DATABASE payroll ;
[/mysql]

Second, create a table named “employees”.

[mysql] CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID int(11) NOT NULL,
FIRST_NAME varchar(255) DEFAULT NULL,
LAST_NAME varchar(255) DEFAULT NULL,
SALARY int(11) DEFAULT NULL,
PRIMARY KEY (EMPLOYEE_ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[/mysql]

Third, insert all data in the database.

[mysql] INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY) VALUES
(102, ‘lex’, ‘De Haan’, 17000),
(103, ‘alexander’, ‘Hunold’, 9000),
(104, ‘Bruce’, ‘Ernst’, 6000),
(107, ‘Diana’, ‘Lorents’,4200),
(124, ‘Kevin’, ‘Mourgos’, 5800),
(141, ‘Trenne’, ‘Rajs’, 3500),
(142, ‘Curtis’, ‘Davies’, 3100),
(143, ‘Randal’, ‘Matos’, 2600),
(144, ‘Peter’, ‘Vargas’, 2500),
(149, ‘Ellen’, ‘Zlotkey’, 10500),
(174, ‘Jonathan’, ‘Abel’, 11000),
(176, ‘Kimberly’, ‘Taylor’, 8600),
(178, ‘Jinnefer’, ‘Grant’, 7000),
(200, ‘Michael’, ‘Whalen’, 4400),
(201, ‘Pat’, ‘Hartstein’, 13000),
(205, ‘Shelley’, ‘Fay’, 6000),
(206, ‘William’, ‘Higgins’, 12000),
(207, ‘hatch’, ‘Glets’, 8300);
[/mysql]

Now, open your Visual Basic 2008, create a project and set the Form just like this.

datagridviewcontrolnavrecords_form1

After that double click the Form and do this code above the Form_Load for declaring the classes ,variables and setting up the connection.
[vbnet] ‘reference
Imports MySql.Data.MySqlClient
Public Class Form1

‘setting up the string connection of MySQL Database
Dim con As MySqlConnection = _
New MySqlConnection(“server=localhost;user id=root;database=payroll”)
‘a set of command in MySQL
Dim cmd As New MySqlCommand
‘Bridge between a database and the datatable for retrieving and saving data.
Dim da As New MySqlDataAdapter
‘a specific table in the database
Dim dt As New DataTable
‘declaring the variable as integer
Dim max As Integer
‘declaring the variable as integer and store it the value which is 0
Dim incVal As Integer = 0
‘declaring the string variable
Dim sql As String

End Class
[/vbnet]

In the Form_Load do this code for displaying the records in the DataGridView for the first load.
[vbnet]

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

‘Opening the connection
con.Open()
‘store the query with the limit to a string variable
sql = “Select * from employees limit 0,5”
‘set a new spicific table in the database
dt = New DataTable
‘set your commands for holding the data.
With cmd
.Connection = con
.CommandText = sql
End With
‘filling the table in the database
da = New MySqlDataAdapter(sql, con)
da.Fill(dt)
‘store the total rows of records in the database to a variable max
max = dt.Rows.Count
‘getting the datasource that will display on the datagridview
DataGridView1.DataSource = dt
con.Close()

End Sub
[/vbnet]

Go back to the Design Views, double click the next button and do this code for navigating the “next” records.
[vbnet] Private Sub btn_next_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_next.Click

Try
‘declaring the variable that count the
‘number of records and it will be displayed in the datagridview
Dim resultPerPage As Integer = 5
‘declaring the variable for the first results
Dim startResult As Integer

If incVal <> max – 2 Then ‘checking if the max row is not equal to the incremented value
‘result
‘formula on how to increment the value of a variable that has been declared
incVal = incVal + 1
‘formul of the list of records that will display in the datagridview
startResult = incVal * resultPerPage
Else
If max Then ‘checking if the total rows is in its maximum rows.
‘result
Return ‘stands for stop executing
End If
End If
‘Opening the connection
con.Open()
‘store the query with the limit to a string variable
sql = “Select * from employees limit ” & startResult & “,” & resultPerPage & “”

‘set a new spicific table in the database
dt = New DataTable
‘set your commands for holding the data.
With cmd
.Connection = con
.CommandText = sql
End With
‘filling the table in the database
da = New MySqlDataAdapter(sql, con)
da.Fill(dt)
‘getting the datasource that will display on the datagridview
DataGridView1.DataSource = dt

Catch ex As Exception
MsgBox(ex.Message)
End Try
‘closing connection
con.Close()

End Sub
[/vbnet]

Then, go back to the Design Views again, double click the previous button and do this code for navigating the “previous” records.
[vbnet]

Private Sub prev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_prev.Click

Try
‘declaring the variable that count the
‘number of records and it will be displayed in the datagridview
Dim resultPerPage As Integer = 5
‘declaring the variable for the first list
‘of records that will display in the datagridview
Dim startResult As Integer

If incVal = 0 Then ‘checking if the incremented variable is equals to zero
Return ‘stands for stop executing
ElseIf incVal > 0 Then ‘checking if the incremented variable is greater than zero
incVal = incVal – 1 ‘formula on how to decrease the value of a variable that has been declared

‘formula of the list of records that will display in the datagridview
startResult = incVal * resultPerPage
End If
‘Opening the connection
con.Open()
‘set a new spicific table in the database
dt = New DataTable
‘set your commands for holding the data.
With cmd
.Connection = con
.CommandText = “Select * from employees limit ” & startResult & “,” & resultPerPage & “”
End With
‘filling the table in the database
da = New MySqlDataAdapter(“Select * from employees limit ” & startResult & “,” & resultPerPage & “”, con)
da.Fill(dt)
‘getting the datasource that will display on the datagridview
DataGridView1.DataSource = dt

Catch ex As Exception
MsgBox(ex.Message)
End Try
‘closing connection
con.Close()

End Sub
[/vbnet]

 

 

Readers might read also:

 

 

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.