Resources » .NET programming » .NET Framework

Select, Insert, Update and delete Operation in VB.Net and MySQL


Last Updated:   Category: .NET Framework    
Author: Member Level: Gold    Points: 10


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


Did you like this resource? Share it with your friends and show your love!

Responses to "Select, Insert, Update and delete Operation in VB.Net and MySQL"
Guest Author: Adele     13 Jan 2012
I feel satisfied after reading that one.


Guest Author: Syawal1     24 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: Suresh    25 Apr 2012Member 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: shiko     24 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: vijay    31 Jul 2012Member 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 ?



Author: grace    11 Aug 2012Member Level: Bronze   Points : 1
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: Sarfaraz     09 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: Suresh    23 Jan 2013Member 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: Suresh    23 Jan 2013Member 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: AJI     17 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 alajarmeh     10 Mar 2013
Great tutorial,
benefited from it a lot Much appreciation Nancy



Guest Author: alvin john b dela cr     01 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?



Author: ahmad khalid    19 Apr 2013Member Level: Bronze   Points : 0
hi friends
i have a soluation please visit the below link
http://ahmadkhalid44.blogspot.com/



Guest Author: satosh     19 Jun 2013
I want editing the marks details in c#.net using MySQL db


Guest Author: nerdygalz     02 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: student26     25 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: anwar     22 Dec 2013
i have problem in update
if i have more than 3 textbox what i should do?



Feedbacks      

Post Comment:




  • 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    kintu gandhi
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India