This example helps you to learn how to deal with database in order to search & navigate the records using
Database Name : employee.accdb
Table Name : empinfo
No. of fields in table :
-
Employee Id as Id
-
First Name as FName
-
Last Name as LName
-
Designation as Designation
-
Salary as Salary
Code:
Imports System.Data.OleDb
Module Module1
Public Con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=.\\employee.accdb")
Public Dad As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM empinfo ORDER BY Id", Con)
Public Dst = New DataSet
Public CurrentRow As Integer
End Module
Imports System.Data.OleDb
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
CurrentRow = 0
'MsgBox("Provider = " & Con.Provider & " " & "Source = " & Con.DataSource & MsgBoxStyle.OkOnly)
Dad.Fill(Dst, "employee")
ShowData(CurrentRow)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles ExitNow.Click
Me.Close()
End Sub
Private Sub Id_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) HandlesId.GotFocus
Id.SelectAll()
End Sub
Private Sub ShowData(ByVal CurrentRow)
Id.Text = Dst.Tables("employee").Rows(CurrentRow)("Id")
FName.Text = Dst.Tables("employee").Rows(CurrentRow)("FName").ToString.ToUpper
LName.Text = Dst.Tables("employee").Rows(CurrentRow)("LName").ToString.ToUpper
Designation.Text = Dst.Tables("employee").Rows(CurrentRow)("Designation").ToString.ToUpper
Salary.Text = Dst.Tables("employee").Rows(CurrentRow)("Salary").ToString.ToUpper
End Sub
Private Sub First_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesFirst.Click
CurrentRow = 0
ShowData(CurrentRow)
End Sub
Private Sub Forward_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles Forward.Click
If CurrentRow <> Dst.Tables("employee").Rows.Count - 1 Then
CurrentRow += 1
ShowData(CurrentRow)
Else
MsgBox("Last Record is Reached!!!")
End If
End Sub
Private Sub Previous_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles Previous.Click
If CurrentRow <> 0 Then
CurrentRow -= 1
ShowData(CurrentRow)
Else
MsgBox("First Record is Reached!!!")
End If
End Sub
Private Sub Last_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesLast.Click
CurrentRow = Dst.Tables("employee").Rows.Count - 1
ShowData(CurrentRow)
End Sub
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles Search.Click
Dim SearchId As Integer
SearchId = Id.Text
Dim i, j As Integer
j = Dst.Tables("employee").Rows.Count - 1
i = 0
While i <> j + 1
If SearchId = Dst.Tables("employee").Rows(i)("Id") Then
ShowData(i)
Exit While
ElseIf i = j Then
Clear()
MsgBox("Record Not Found!!!")
i = 0
ShowData(i)
Exit While
End If
i += 1
End While
CurrentRow = i
End Sub
Private Sub Clear()
Id.Text = ""
FName.Text = ""
LName.Text = ""
Designation.Text = ""
Salary.Text = ""
End Sub
Private Sub Id_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) HandlesId.LostFocus
Dim i As Integer
If Id.Text = "" Or IsNothing(Id.Text) = True Or IsNumeric(Id.Text) = False Then
Clear()
MsgBox("Integer Value Required!!!")
i = 0
ShowData(i)
CurrentRow = i
End If
End Sub
End Class
Output:
View of Form at Starting
When you are at first record & try to see previous record a message will pop-up as shown in below figure
When you are at last record & try to see next record a message will pop-up as shown in below figure
The error (shown in below figure) will encountered if you insert the value apart from integer in "Id" field to search the records
