Data Binding Example in VB.Net and MYSQL
In this article i will show you an example of Data Binding in VB.Net and MYSQL with Loading of Records in the TextBoxes,Labels or any other form controls.In addition of Adding new records,i will show you how to Deletion and update records in VB.Net and MYSQL Server Database.
In the previous article I gave you introduction about:
Data binding: -It is a way for programmers to create a link between the controls on a form and the data source.
Binding Context :-Each VB.Net form has a built in Binding Context object that manages the bindings of the VB.Net form controls. It is already built into each VB.Net form and needs not to be setup.
Currency Manage: -It manages the current position of the binding to the Data Source. The current even fires when there is a change in it.
Binding Controls : -If we want to bind a data source to a control on a form, we set the data binding property of that control.
In this article I will show you how to Bind controls on a VB.Net form to MySQL database. How to Load Record on the Form TextBoxes, how to Add a new Record, how to Update a record and how to Delete a record in VB.Net and MYSQL.
First create a Database (students) in Mysql and create a table (users) with the fields as
St_id
Stname
Roll_No
Now design an interface in VB.Net form as shown in the picture.
Now 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 MySqlDataAdapter = New MySqlDataAdapter
Dim cmd As MySqlCommand = New MySqlCommand()
Dim datareader As MySqlDataReader
Load Records: - This is used to load the last record in the database in the corresponding Textboxes on the form.
Doubleclick on the Load Button on the form and add the following code on its Click Event.
Dim sql As String
Try
sql = "SELECT * FROM users"
con.Open()
cmd.CommandText = sql
cmd.Connection = con
dataadapter.SelectCommand = cmd
datareader = cmd.ExecuteReader
While datareader.Read
datareader.Read()
TextBox1.Text = datareader("st_id")
TextBox2.Text = datareader("stname")
TextBox3.Text = datareader("Roll_No")
End While
Catch ex As Exception
End Try
con.Close()
Add Record: -
This is used to add a new record in the database by using the details in the corresponding Textboxes on the form.
Doubleclick on the Addnew Button on the form and add the following code on its Click Event.
Dim query As String
query = "insert into users(st_id,stname,Roll_No) values ('" & TextBox1.Text & " ','" & TextBox2.Text & "','" & TextBox3.Text & "')"
con.Open()
Dim cmd As MySqlCommand = New MySqlCommand(query, con)
cmd.ExecuteNonQuery()
MsgBox("Inserted")
con.Close()
Update Record: -
This is used to update an existing record database by using the details in the corresponding Textboxes on the form.
Doubleclick on the Update Button on the form and add the following code on its Click Event.
Dim query As String
query = "update users set stname= ' " & TextBox2.Text & " ', Roll_No=' " & TextBox3.Text & " ' where st_id=' " & TextBox1.Text & " '"
con.Open()
Dim cmd As MySqlCommand = New MySqlCommand(query, con)
cmd.ExecuteNonQuery()
MsgBox("Updated")
con.Close()
Delete Record: -
This is used to delete a record from the database table whose st_id is shown in textbox1on the form.
Doubleclick on the Delete Button on the form and add the following code on its Click Event.
Dim query As String
query = "delete from users where st_id=' " & TextBox1.Text & " '"
con.Open()
Dim cmd As MySqlCommand = New MySqlCommand(query, con)
cmd.ExecuteNonQuery()
MsgBox("Deleted")
con.Close()
cmd.Dispose()
con.Dispose()
In the next article I will show you how to Move to first, Next, Previous,and last record in the database and also i will show you how to search a particular record in the database table.
Regards
Keep Coding
Thanks that was useful :)