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.
What is Visual Basic’s purpose?
The third-generation programming language was created to aid developers in the creation of Windows applications. It has a programming environment that allows programmers to write code in.exe or executable files. They can also utilize it to create in-house front-end solutions for interacting with huge databases. Because the language allows for continuing changes, you can keep coding and revising your work as needed.
However, there are some limits to the Microsoft Visual Basic download. If you want to make applications that take a long time to process, this software isn’t for you. That implies you won’t be able to use VB to create games or large apps because the system’s graphic interface requires a lot of memory and space. Furthermore, the language is limited to Microsoft and does not support other operating systems.
What are the most important characteristics of Visual Basic?
Microsoft Visual Basic for Applications Download, unlike other programming languages, allows for speedier app creation. It has string processing capabilities and is compatible with C++, MFC, and F#. Multi-targeting and the Windows Presentation Framework are also supported by the system, allowing developers to create a variety of Windows apps, desktop tools, metro-style programs, and hardware drivers.
Let’s begin in How to Navigate Records In DataGridView in VB.net And MySQL Database:
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.(How to Navigate Records In DataGridView in VB.net And MySQL Database)
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:
- How to Create a Login system using Visual Basic.Net and MySQL Database
- How to Export DataGridView Data to Excel using VB.Net
If you have any questions or suggestions about How to Navigate Records In DataGridView in VB.net And MySQL Database, please feel free to leave a comment below.