Form_Load
for declaring the classes ,variables and setting up the connection.
'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
Form_Load
do this code for displaying the records in the DataGridView for the first load.
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 End Sub
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 End Try 'closing connection End Sub
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 End Try 'closing connection End Sub