ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

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

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”.

  1. CREATE DATABASE `payroll` ;

Second, create a table named “employees”.

  1. CREATE TABLE IF NOT EXISTS `employees` (
  2. `EMPLOYEE_ID` int(11) NOT NULL,
  3. `FIRST_NAME` varchar(255) DEFAULT NULL,
  4. `LAST_NAME` varchar(255) DEFAULT NULL,
  5. `SALARY` int(11) DEFAULT NULL,
  6. PRIMARY KEY (`EMPLOYEE_ID`)
  7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Third, insert all data in the database.

  1. INSERT INTO `employees` (`EMPLOYEE_ID`, `FIRST_NAME`, `LAST_NAME`, `SALARY`) VALUES
  2. (102, 'lex', 'De Haan', 17000),
  3. (103, 'alexander', 'Hunold', 9000),
  4. (104, 'Bruce', 'Ernst', 6000),
  5. (107, 'Diana', 'Lorents',4200),
  6. (124, 'Kevin', 'Mourgos', 5800),
  7. (141, 'Trenne', 'Rajs', 3500),
  8. (142, 'Curtis', 'Davies', 3100),
  9. (143, 'Randal', 'Matos', 2600),
  10. (144, 'Peter', 'Vargas', 2500),
  11. (149, 'Ellen', 'Zlotkey', 10500),
  12. (174, 'Jonathan', 'Abel', 11000),
  13. (176, 'Kimberly', 'Taylor', 8600),
  14. (178, 'Jinnefer', 'Grant', 7000),
  15. (200, 'Michael', 'Whalen', 4400),
  16. (201, 'Pat', 'Hartstein', 13000),
  17. (205, 'Shelley', 'Fay', 6000),
  18. (206, 'William', 'Higgins', 12000),
  19. (207, 'hatch', 'Glets', 8300);

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.

  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. If incVal <> max - 2 Then 'checking if the max row is not equal to the incremented value
  11. 'result
  12. 'formula on how to increment the value of a variable that has been declared
  13. incVal = incVal + 1
  14. 'formul of the list of records that will display in the datagridview
  15. startResult = incVal * resultPerPage
  16. Else
  17. If max Then 'checking if the total rows is in its maximum rows.
  18. 'result
  19. Return 'stands for stop executing
  20. End If
  21. End If
  22. 'Opening the connection
  23. con.Open()
  24. 'store the query with the limit to a string variable
  25. sql = "Select * from employees limit " & startResult & "," & resultPerPage & ""
  26.  
  27. 'set a new spicific table in the database
  28. dt = New DataTable
  29. 'set your commands for holding the data.
  30. With cmd
  31. .Connection = con
  32. .CommandText = sql
  33. End With
  34. 'filling the table in the database
  35. da = New MySqlDataAdapter(sql, con)
  36. da.Fill(dt)
  37. 'getting the datasource that will display on the datagridview
  38. DataGridView1.DataSource = dt
  39.  
  40. Catch ex As Exception
  41. MsgBox(ex.Message)
  42. End Try
  43. 'closing connection
  44. con.Close()
  45.  
  46. 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
Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!