Navigate Record In the DataGridView Control

Submitted by janobe on Saturday, January 25, 2014 - 11:17.
In this tutorial I will teach you how to navigate records in 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: The Database of this project can be found in my previous tutorial which is the Displaying and Counting the Total Value of the Records in the DataGridView. Now, open your Visual Basic 2008, create a project and set the Form just like this. firstform After that double click the Form and do this code above the Form_Load for declaring the classes ,variables and setting up the connection.
  1. 'reference
  2. Imports MySql.Data.MySqlClient
  3. Public Class Form1
  4.  
  5.     'setting up the string connection of MySQL Database
  6.     Dim con As MySqlConnection = _
  7.     New MySqlConnection("server=localhost;user id=root;database=payroll")
  8.     'a set of command in MySQL
  9.     Dim cmd As New MySqlCommand
  10.     'Bridge between a database and the datatable for retrieving and saving data.
  11.     Dim da As New MySqlDataAdapter
  12.     'a specific table in the database
  13.     Dim dt As New DataTable
  14.     'declaring the variable as integer
  15.     Dim max As Integer
  16.     'declaring the variable as integer and store it the value which is 0
  17.     Dim incVal As Integer = 0
  18.     'declaring the string variable
  19.     Dim sql As String
  20.  
  21. End Class
In the Form_Load do this code for displaying the records in the DataGridView for the first load.
  1.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2.      
  3.         'Opening the connection
  4.         con.Open()
  5.         'store the query with the limit to a string variable
  6.         sql = "Select * from employees limit 0,5"
  7.         'set a new spicific table in the database
  8.         dt = New DataTable
  9.         'set your commands for holding the data.
  10.         With cmd
  11.             .Connection = con
  12.             .CommandText = sql
  13.         End With
  14.         'filling the table in the database
  15.         da = New MySqlDataAdapter(sql, con)
  16.         da.Fill(dt)
  17.         'store the total rows of records in the database to a variable max
  18.         max = dt.Rows.Count
  19.         'getting the datasource that will display on the datagridview
  20.         DataGridView1.DataSource = dt
  21.         con.Close()
  22.  
  23.     End Sub
Go back to the Design Views, double click the next button and do this code for navigating the “next” records.
  1. Private Sub btn_next_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_next.Click
  2.  
  3.         Try
  4.             'declaring the variable that count the
  5.             'number of records and it will be displayed in the datagridview
  6.             Dim resultPerPage As Integer = 5
  7.             'declaring the variable for the first results
  8.             Dim startResult As Integer
  9.  
  10.  
  11.             If incVal <> max - 2 Then 'checking if the max row is not equal to the incremented value
  12.                 'result
  13.                 'formula on how to increment the value of a variable that has been declared
  14.                 incVal = incVal + 1
  15.                 'formul of the list of records that will display in the datagridview
  16.                 startResult = incVal * resultPerPage
  17.             Else
  18.                 If max Then 'checking if the total rows is in its maximum rows.
  19.                     'result
  20.                     Return 'stands for stop executing
  21.                 End If
  22.             End If
  23.             'Opening the connection
  24.             con.Open()
  25.             'store the query with the limit to a string variable
  26.             sql = "Select * from employees limit " & startResult & "," & resultPerPage & ""
  27.  
  28.             'set a new spicific table in the database
  29.             dt = New DataTable
  30.             'set your commands for holding the data.
  31.             With cmd
  32.                 .Connection = con
  33.                 .CommandText = sql
  34.             End With
  35.             'filling the table in the database
  36.             da = New MySqlDataAdapter(sql, con)
  37.             da.Fill(dt)
  38.             'getting the datasource that will display on the datagridview
  39.             DataGridView1.DataSource = dt
  40.  
  41.         Catch ex As Exception
  42.             MsgBox(ex.Message)
  43.         End Try
  44.         'closing connection
  45.         con.Close()
  46.  
  47.     End Sub
Then, go back to the Design Views again, double click the previous button and do this code for navigating the “previous” records.
  1.     Private Sub prev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_prev.Click
  2.  
  3.         Try
  4.             'declaring the variable that count the
  5.             'number of records and it will be displayed in the datagridview
  6.             Dim resultPerPage As Integer = 5
  7.             'declaring the variable for the first list
  8.             'of records that will display in the datagridview
  9.             Dim startResult As Integer
  10.  
  11.             If incVal = 0 Then 'checking if the incremented variable is equals to zero
  12.                 Return 'stands for stop executing
  13.             ElseIf incVal > 0 Then 'checking if the incremented variable is greater than zero
  14.                 incVal = incVal - 1 'formula on how to decrease the value of a variable that has been declared
  15.  
  16.                 'formula of the list of records that will display in the datagridview
  17.                 startResult = incVal * resultPerPage
  18.             End If
  19.             'Opening the connection
  20.             con.Open()
  21.             'set a new spicific table in the database
  22.             dt = New DataTable
  23.             'set your commands for holding the data.
  24.             With cmd
  25.                 .Connection = con
  26.                 .CommandText = "Select * from employees limit " & startResult & "," & resultPerPage & ""
  27.             End With
  28.             'filling the table in the database
  29.             da = New MySqlDataAdapter("Select * from employees limit " & startResult & "," & resultPerPage & "", con)
  30.             da.Fill(dt)
  31.             'getting the datasource that will display on the datagridview
  32.             DataGridView1.DataSource = dt
  33.  
  34.         Catch ex As Exception
  35.             MsgBox(ex.Message)
  36.         End Try
  37.         'closing connection
  38.         con.Close()
  39.  
  40.     End Sub
Complete Source Code is included. You can download it and run on your computer.
Tags
datagridview
Navigate Records
MYSQL
database
vb.net

Add new comment

About text formats
  • 466 views