Select, Insert, Update and delete Operation in VB.Net and MySQL
I am Creating Windows application using VB.Net. In this Article you will learn about Select, Insert, Update and delete Operation in VB.Net and MySQL. Here I am displaying Records in Datagridview from MySQL Database.
Introduction
I am Creating one VB.NET Windows application.In this Application I am going to explain about Select, Insert, Update and delete Operation in VB.Net and MySQL. Here I am displaying Records in Datagridview from MySQL Database.
I am Creating windows application in vb.net named Myproject. In this application I am using Three Textbox, Datagridview, Four Button controls and Four Labels.
I am using Following Textbox Controls and its name :
txtUserid.Text: It is used to get the user id from users.
txtUserName.Text : It is used to get the user name from users.
txtAge.Text : It is used to get the age of the users from users.
I am using Following Button Controls and its name :
DisplayRecords : if you click the DisplayRecords button, the user details will display in DataGridview.
AddRecords : if you click the AddRecords button, the user details will store in data base table.
UpdateRecords : if you click the UpdateRecords button,the user details will update from data base table.
DeleteRecords : if you click the DeleteRecords button, the user details will delete from data base table.
DataGridview1 is used to display user details.
If you want to Connect VB.Net with MySQL Data base, You Should use MySql.Data.MySqlClient Namespace. It Contains MySQL Connection class and Methods.
MySqlConnection :it is used connect VB.NEt with MySQL Data Base.
MySqlCommand : It is used to excute the command
MySqlDataAdapter :it is acting as bridge between MySQL and VB.Net.
Imports MySql.Data.MySqlClient
MySqlConnection Class is used to connect to MySQL database.
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=mysql;")
Diaplay Records in DataGridview from MySQL Database.Display Records in VB.Net from MySQL Database
Private Sub DisplayRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=mysql;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM userreg", con)
Dim ds As DataSet = New DataSet()
Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()
con.Open()
DataAdapter1.SelectCommand = sql
DataAdapter1.Fill(ds, "Product")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "Product"
con.Close()
End Sub
Insert Records in MySql Database.
Private Sub AddRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim Query As String
'Query = "INSERT INTO userreg"
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=mysql;")
'Dim sql As MySqlCommand = New MySqlCommand(Query, con)
Query = "INSERT INTO userreg(idUserReg,UserName, Age)VALUES("
Query = Query + txtUserRegId.Text + ",'" + txtUserName.Text + "'," + txtAge.Text + ")"
con.Open()
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then
lblMsg.Text = "Record is Successfully Inserted"
Else
lblMsg.Text = "Record is not Inserted"
End If
con.Close()
End Sub
Update Records from MySQL Database.
Private Sub UpdateRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim Query As String
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=mysql;")
con.Open()
Query = "UPDATE userreg SET UserName ='" + txtUserName.Text + "',"
Query = Query + "Age = " + txtAge.Text
Query = Query + " WHERE idUserReg = " + txtUserRegId.Text
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
MsgBox(Query)
Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then
lblMsg.Text = "Record is Successfully Updated"
Else
lblMsg.Text = "Record is not Updated"
End If
con.Close()
End Sub
Delete Records from MySQL Database
Private Sub DeleteRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim Query As String
Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=test;User ID=root;Password=mysql;")
con.Open()
Query = "Delete FROM userreg WHERE idUserReg =" + txtUserRegId.Text
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
MsgBox(Query)
Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then
lblMsg.Text = "Record is Successfully Deleted"
Else
lblMsg.Text = "Record is not Deleted"
End If
con.Close()
End Sub
MySQL Database
Here I am creating table in MySQL Data base with Following Fields. The Table Name is userreg.
idUserReg : int
UserName : Varchar
Age : int
OutPut
Finally We Create Windows Application in VB.Net with MySQL Database. Here Insert, update, Delete and display Records in grid view.Creating Windows application using MySQL Database Connection in VB.Net
if you want to know about How to Upload Excel file in ASP.Net Applications. Please Refer Following Link.
Upload Excel file in ASP.Net Applications
Thanks for spending your valuable time.if you have any doubts or any Suggestion , Let Me know. I am Happy to update here.
Hope you will enjoy this article and provide your valuable suggestion and feedback.
Thanks
Happy Coding.....
I feel satisfied after reading that one.