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
Select, Insert, Update and delete Operation in VB.Net and MySQL












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.....


Comments

Guest Author: Adele13 Jan 2012

I feel satisfied after reading that one.

Guest Author: Syawal124 Apr 2012

Sir may i ask where do "product" come from and
i have error it said Fill : SelectCommand.Connection property has not been initialized.
which is at dataAdapter1.Fill(ds,"product")

Author: Suresh25 Apr 2012 Member Level: Gold   Points : 2

Hi Syawal,

Please check the Data base connection whether you are giving correct or not.


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)



you can the using following code dataAdapter1.Fill(ds)
instead dataAdapter1.Fill(ds,"product")

Thanks and Regards
S.Suresh

Guest Author: shiko24 Jul 2012

Errore 2 L'espressione è un valore, quindi non può essere la destinazione di un'assegnazione.


Query = "INSERT INTO clienti(nome)VALUES("
Query = Query() + TextBox1.Text + ")"

Author: vijay31 Jul 2012 Member Level: Bronze   Points : 1

I want to do something tricky. I hope you could help me.

I have a database with self given id's. So, if I delete a record from the middle , for example I have id's like 1,2,3,4,5... and if I delete a record with id 3, records from 4 and 5 should shift up. so 4 becomes 3 and 5 becomes 4.
Is it possible in vb.net ?

Guest Author: 11 Aug 2012

Hi Syawal,
hello im a new programmer..and im working with vb.net connected to mysql ..and with regards with that..i used your code in insert data to mysql database but it produces error to my system ...saying that i have to use mysql version of EXECUTENONQUERY please help me sir....

Guest Author: Sarfaraz09 Jan 2013

Hi Sir
I have some confusion in the below line. Why you have uses query=query+............
Please explain

Query = "INSERT INTO userreg(idUserReg,UserName, Age)VALUES("
Query = Query + txtUserRegId.Text + ",'" + txtUserName.Text + "'," + txtAge.Text + ")"

Thank you

Author: Suresh23 Jan 2013 Member Level: Gold   Points : 0

Hi shiko,

Don't use Query(). Use Query . it is a string variable not method.

Query = "INSERT INTO clienti(nome)VALUES("
Query = Query + TextBox1.Text + ")"

Thanks

Author: Suresh23 Jan 2013 Member Level: Gold   Points : 3

Hi Sarfaraz,

"+" Operator is concatenation in C# programming language. Here I am using "+" Operator for concatenate the query.

you can use like this

Query = "INSERT INTO userreg(idUserReg,UserName, Age)VALUES(" + txtUserRegId.Text + ",'" + txtUserName.Text + "'," + txtAge.Text + ")"

but it is lengthy query. that's why I am using "+" Operator.

I think now you get my point.

Thanks

Guest Author: AJI17 Feb 2013

Very Very Thanks...
A Very Good Article.

How to Create a New Database(in MySQL) through VB.Net???
(also check the database is already exists)

Guest Author: nancy alajarmeh10 Mar 2013

Great tutorial,
benefited from it a lot Much appreciation Nancy

Guest Author: alvin john b dela cr01 Apr 2013

Dim con As MySqlConnection = New MySqlConnection("Data Source=localhost;Database=usereg;User ID=root;Password=vin247;")
Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM userreg", con)

Dim ds As DataSet = New DataSet()

Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter()

con.Open()

I have a error in MySqlConnection, MySqlCommand, MySqlDataAdapter do i need something to declare?

Guest Author: 19 Apr 2013

hi friends
i have a soluation please visit the below link
http://ahmadkhalid44.blogspot.com/

Guest Author: satosh19 Jun 2013

I want editing the marks details in c#.net using MySQL db

Guest Author: nerdygalz02 Oct 2013

why is the HTML part not shown?? show the HTML part with asp:buttons/textboxes, and how is the HTML code part written as well? show the whole HTML code that goes with this please!

Guest Author: student2625 Oct 2013

Hello, I have a problem with:

Dim i As Integer = cmd.ExecuteNonQuery()
If (i = 0) Then
I have this warning about this when i want to add Date in my system. Please help me. I am still learning

Guest Author: anwar22 Dec 2013

i have problem in update
if i have more than 3 textbox what i should do?



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: