Navigation of Records in VB.Net and MYSQL Server Database.
In this article I am going to show you how you can navigate from one record to another in MYSQL database table in VB.Net. In addition of that I will also show you how to search a particular record in the Database Table in VB.Net and MYSQL Server.
In the previous article I show you an example of Data Binding in VB.Net and MYSQL with Loading of Records in the TextBoxes on a VB.Net Form.In addition to Adding new records, I show you how to Delete and update records in VB.Net and MYSQL Server Database.
In this article I am going to show you how you can Navigate from one record to another in MYSQL database table using VB.Net.
First we created a Database (students) in Mysql and create a table (users) with the fields as
St_id
Stname
Roll_No
Also we design an interface in VB.Net form as shown in the picture.
In the code window of the form write the code as under but first of all you need to add a reference of MYSQL.Data in your project. First Click on Project then on Project properties then click on reference finally click on Add button and select MYSQL.Data.
Imports MySql.Data.MySqlClient
Public Class Form1
Dim con As MySqlConnection = New MySqlConnection("data source=localhost;database=students;user id=root;password=")
Dim ds As DataSet = New DataSet
Dim dataadapter As New MySqlDataAdapter("select * from users", con)
Dim cmd As MySqlCommand = New MySqlCommand()
Dim dv As DataView
Dim cm As CurrencyManager
Dim datareader As MySqlDataReader
'FILLDATASETANDVIEW
This procedure is used to intialise a new instance of the DataSet object and DataView objects.
Public Sub filldatasetandview()
ds = New DataSet
dataadapter.Fill(ds, "users")
dv = New DataView(ds.Tables("users"))
cm = CType(Me.BindingContext(dv), CurrencyManager)
End Sub
'BINDINGFIELDS
This procedure will actually bind the controls on your form to your DataView object.
Public Sub bindfields()
TextBox1.DataBindings.Clear()
TextBox2.DataBindings.Clear()
TextBox3.DataBindings.Clear()
TextBox1.DataBindings.Add("text", dv, "st_id")
TextBox2.DataBindings.Add("text", dv, "stname")
TextBox3.DataBindings.Add("text", dv, "Roll_No")
End Sub
'SHOWPOSITION
This procedure will actually display the current record position on your form.
Public Sub showposition()
TextBox4.Text = cm.Position + 1 & " " & "of" & " " & cm.Count()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
filldatasetandview()
bindfields()
showposition()
End Sub
Private Sub previousrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previousrecord.Click
cm.Position = cm.Position - 1
showposition()
End Sub
Private Sub nextrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextrecord.Click
cm.Position = cm.Position + 1
showposition()
End Sub
Private Sub lastrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lastrecord.Click
cm.Position = cm.Count - 1
showposition()
End Sub
Private Sub firstrecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstrecord.Click
cm.Position = 0
showposition()
End Sub
'Searching a particular Record from the Database
Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
Dim x As Integer
dv.Sort = "st_id"
x = dv.Find(TextBox5.Text)
cm.Position = x
bindfields()
showposition()
End Sub
Below screen print will show you the page design,
Attachments
WOW... You really know your code; I can not express my appreciation enough... I am working on a project that your knowledge, insight, and keen understanding have literally allowed me to bring it to fruition...
Thank You, So... So... So... Much!!!