Last updated on June 13th, 2020 at 08:50 pm
Introduction:
How to use MySQL with Visual Basic .NET? Watch this tutorial for basic mysql connection with visual basic .net 2013/2010/2008. Visual basic database can be anything like MS Access, MSSql etc. but if you are already working with MySQL a lot then probably you would like to use MySQL with your vb.net application. To enable vb.net application to communicate to MySQL databases, MySQL has already provided a connector .DLL that you need to import to your Visual Basic .NET application which will give all the classes and methods that you will be using to create an application in visual basic .net that connects to MySQL database in order process database related requirements of the application.
Bored with Text? Better watch the video tutorial 🙂
Target
Target of this tutorial is to establish connection to MySQL server Database from Visual Basic .NET 2010.
Software Requirements
- MySQL Server for Windows or Any other installation of MySQL Server [DOWNLOAD]
- MySQL .NET Connector [DOWNLOAD]
- MySQL GUI – MySQL Workbench or Any other GUI or any way you prefer to work on MySQL [DOWNLOAD]
- Visual Basic .NET 2008/2010/2013
Concept Requirements
- Working with MySQL
- Creating database & tables etc.
- Insert, Select, Update and Delete queries
- Working with Visual Studio
- Creating projects
- Forms and elements in Visual Basic .NET
- Basic coding
The Database
For the sake of simplicity, create a database with your IDE or whatever the way you like as vb_mysql and a table in it with the name _users with the following structure.
Field
|
Type
|
Primary
|
Auto Increment
|
NULL
|
u_id
|
BIGINT
|
YES
|
YES
|
NO
|
u_firstName
|
TEXT
|
NO
|
NO
|
NO
|
u_lastName
|
TEXT
|
NO
|
NO
|
NO
|
So we are done with the database, let’s create the form for the project.
Number
|
Type
|
Name
|
1
|
TEXTBOX
|
txt_firstName
|
2
|
TEXTBOX
|
txt_lastName
|
3
|
BUTTON
|
Button1
|
4
|
BUTTON
|
Button2
|
5
|
LISTBOX
|
listBox_users
|
Initializing and Connecting to Database:
Now Double-Click on the form to bring the code editor.
Above everything, write this
Imports MySql.Data.MySqlClient
This will allow us to use various types, methods and classes of the Connector.
Now, just below Public Class Form1 write the followings
Public dbconn As New MySqlConnection
Public sql As String
Public dbcomm As New MySqlCommand
Public dbread As MySqlDataReader
Here we are declaring 4 public objects that we will use.
- dbconn is the connection to the database.
- sql is a string, which will actually hold our query.
- dbcomm is the MySqlCommand that will execute the query.
- dbread is the one which will hold those data that we will fetch using query from DB.
Okay, now we are going to make the connection. We will open the connection to the DB in the load event of our form which now named as form1 by default. The code looks like the following
dbconn = New MySqlConnection("Data Source=localhost;user id=root;password=demo;port=3310;database=vb_mysql")
Try
dbconn.Open()
Catch ex As Exception
MsgBox("Connection Error: " & ex.Message.ToString)
End Try
Here, look at the connection string which as currently have 5 parts as follows
- Data Source: We have our database in our local machine, thus I have used “localhost”, if you have your DB at some other computer connected by LAN to your machine, you will put the IP address of that machine instead of localhost
- user id: The user of the mysql database server
- password: The password of the user of the mysql database server. This is optional, if your user doesn’t have any password, you don’t need this.
- port: This is also optional, the port number for the server. I have two server running, so I used the port number to connect to the specific one. The default value for this is 3306.
- database: The name of the database in the mysql database server to connect to.
After that, in a Try..Catch block, we have opened the connection as
dbconn.Open()
This will open up the connection, if failed, then the message of the catch block will be showed.
MsgBox("Connection Error: " & ex.Message.ToString)
Run your program to see if you get the Error Message, if don’t then stop debugging and come back to IDE again. If you see the error message, then also, come back to the IDE and check if you did everything in the same way this far, try checking from bottom to top.
If everything is good then we are also good to go with saving the data to the database in the next part.
Saving Data in Database:
Double click the button with label SAVE (name Button1) to write the following codes in its click event.
sql = "INSERT INTO _users(u_id,u_firstName,u_lastName) VALUES(NULL,'" & txt_firstName.Text & "','" & txt_lastName.Text & "')"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
dbread.Close()
MsgBox("Data inserted.")
Catch ex As Exception
MsgBox("Failed to insert data: " & ex.Message.ToString())
End Try
dbread.Close()
First of all we are writing our SQL Query String as
sql = "INSERT INTO _users(u_id,u_firstName,u_lastName) VALUES(NULL,'" & txt_firstName.Text & "','" & txt_lastName.Text & "')"
Nothing to explain here, but in the next line in the Try…Catch block
dbcomm = New MySqlCommand(sql, dbconn)
We are instantiating a MySqlCommand with our sql and the database connection, dbconn, that we created in the form load event. In the next line
dbread = dbcomm.ExecuteReader()
We have executed the command and stored the return in the dbread, which is the declared MySqlDataReader. By this command we have opened the MySqlDataReader dbread. We can not use this opened mysql data reader anymore to store the return of any other mysql command unless we close it. So that’s what we do in the next line
dbread.Close()
After that, we have displayed a message to show that the data has been successfully inserted without any error.
However, if any of this goes wrong, we have a message to show in the Catch block which will tell us what has gone wrong.
So, now you can run your program and try putting something in the txt_firstname and txt_lastName TEXTBOX and then clicking on SAVE to see which message appears. If the error message appears, this means the data wasn’t saved, else means the data has been saved successfully.
Data is saved, but of no use if can not pull the data back for processing, right. Move on to the next part.
Get Data from Database:
To get the saved names back from the DB, double click the Button2 button and write the following codes in it
listBox_users.Items.Clear()
sql = "SELECT * FROM _users"
Try
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
While dbread.Read
listBox_users.Items.Add(dbread("u_firstName").ToString() & " " & dbread("u_lastName").ToString())
End While
Catch ex As Exception
MsgBox("Problem loading data: " & ex.Message.ToString)
End Try
dbread.Close()
First of all we are clearing the listBox_users to list the names that will be pulled out from the mysql database
listBox_users.Items.Clear()
Then we have written the SQL Query string to pull out everything from the _users table. Then we started a Try…Catch block to start processing the query.
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
Description of the above two lines are same as it was in the Button1 event.
But after that, we have started a while loop as
While dbread.Read
listBox_users.Items.Add(dbread("u_firstName").ToString() & " " & dbread("u_lastName").ToString())
End While
Here we made a loop which will run until our mysql data reader, dbread, has data. In each loop we add one user name from dbread to our list listBox_users.
After that, we have closed the dbread. In the Catch block of this Try…Catch block we have showed a message regarding the data reading process’s failure though we don’t think that this will come up if you have done what you have been instructed to.
So that’s it, all done, we connected to DB, saved data and also pulled back data from DB. In the last part, we will get the full source code and will download the project to see things in action.
Okay, so the full code is supposed to look like the following
So now, run your program again and click the GET button and the list on its left will be filled up with the names you have inserted earlier.
Let me know if you are facing any issue implementing this tutorial. Please come up with screenshot of the error message or bugs so that I can see what’s going on.
You can download the Application also, click on the following link.
Thanks dude….good v v good
im doing my project in vb 2010 anna..i've some challenge when i retrieving data fro mysql data..
f.eg i wanna to crate class scheduling based number of student zat retreived from database anna to assign section A,B,C………….plz, help me….
Thanks dude … for leaving comments …
Cool, I also had a School Management project when I was in campus, I can directly help but I don't think you can wait, as I am going out for long vacation of nearly 15 days, But still I will try if you can draw a flowchart or pseudo code of the thing you wanna do … but just can't confirm it.
However, what is the meaning of "anna"?
i install the mysql connector but it not showing the that file i am using the visual studio 2010 with mysql community server and create database and made connection now unable to connect use the database in the application
What if there was a proxy on my network, how can i bypass it?
Nice work… Simple and concise
I never used "visual studio 2010 with mysql community server" though, I would suggest you to forget everything you do generally in a DB related application and just follow the tutorial. I hope things will go fine.
I don't know that, I hope you have tried the situation you are asking at your own. I mean, before writing this question, I hope, you have tried to implement the tutorial on a system with proxy network. Now what is the problem? If the problem is connecting to the DB, then make some google search on "mysql connection string for proxy network server" or something like that. I hope, only the connection string might be in need to be changed.
Thanks.
Fantastic—-How can you add data to multiple tables in the same database?
Write multiple SQL INSERT queries in that case, where in the queries change the name of the table and the fields.
I found your article very usefull and is very good job you done. I have a quetion . What code i need to use so i can check to database if the user name allready exists so i can avoid doubles?
SELECT COUNT(user_name) as found FROM users_table WHERE user_name = 'given_user_name'
If now 'found' is more than 0, the given_user_name exist.
There is another way also, that is, make the DB field unique and while the running the insert query catch the error that mysql will throw for duplicate entry.
Thank you for your reply i will try that and if i need more help i will post again. Thank you once more.
You are welcome
hey plz help me out…
Iam getting error at dbconn and executeReader in
dbcomm = New MySqlCommand(sql, dbconn)
dbread = dbcomm.ExecuteReader()
I can't figure out the problem exactly….please check it out
You might haven't referenced correctly to the MySQL Dot Net Connector. Start from the beginning of the tutorial and also Post the error message screenshot or text next time.
thank you..
i use phpMyadmin for database and Vb.Net 2008 for application and my database is on other computer which connect by lan….i put the IP address on Data Source=192.168.0.104…….but it's not work….ple help me…My Email ID:[email protected]
Welcome.
First check if you are really connected to the server by trying to find it in the Network folder. Then try to put the server computer name as the data source on connection string. If this does not work, then it may be that the server computer's firewall need exception for you to get in. If none these works, then google the problem. I will be able to reply you exactly on Monday, I will remain away from my work in these days, so see you on Monday. Don't forget to let me know, if you have done that already.
Thanks Bro (y)
Thanks …
hi, do you have a tutorial on login using mysql database in vb. net 10?
Check out this one, here using the MS Access DB, alter this with your mysql DB, that will do.
Tutorial :: Creating a LogIn/Registration Form for your software [Basic]
ok, thanks 🙂
Hi,
I had a problem regarding inserting the username and age (that i added into my work). It became unsuccessful then popup the result the result like this..
http://i.imgur.com/32s7BjM.jpg
Is it any problem with my MySQL connectors version? I'm using windows7 64 bit.
thanks.
Its clearly saying that the problem is with your sql query string. Examine the query string, you will find the problem.
if my database is online then how to connect mysql database with vb.net
Please tell me how to connect.
Just use the IP address as the host, but you will need access to this IP from anywhere, generally this is not given., only hosted applicaton can get access to DB. However, contact the admin of the server about this.
Really as useful and simple tutorial,with great explanation.
I hope it helped.
Muy buena aportación, gracias
Usted es bienvenido.
Indeed.Thanks a lot.
thz a lof brother…
Welcome
How we Manuplate id in save funcation
Means if we don't want to enter null value in it then how we auto increment the id in this table while saving the data
A bit confusing question, however, if u want to auto increment a field the field must have to be a auto increment field and value for field in your insert query have to be NULL.
thanks !
welcome
This solution is not for Express edition of visual basic. Am I right?
thanks dear.. nicely expressed and good job …
I don't know, why don't you try it out.
Welcome DR
whats the purpose of mysql server? and how to use it?
Its a specialty that there are tons of articles over the net describing the ways to use MySQL, a single reply wont suffice.
I guess you have never heard of SQL Injection the way you are building your SQL statements?
I guess you have never written or read a target oriented tutorial. Even in the tutorials of using sql, SQL INJECTION will not be included because service and security is a completely different chapter. I hope that helps you.
You should always use good programming practice. You are showing people how to use MySQL with Visual Basic so you should follow all sensible rules. Don't forget you are targetting your post at people looking at how to do something and you are showing them the wrong way.
You should never ever show someone how to create an SQL statement like this
sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"
That is just bad programming. Security should always come first, always.
Hi : Thank you for your post regarding visual basic, I try and working fine.. Thanks
Happy Backlinking
@ Tanmay Chakrabarty, In Kannada language "anna" means "Brother".
Good Job. Keep it up
Good to know .. .thanks
This article just saved my project . Thanks a lot !
Thank you too.
Hello are u still on this post? I am working with my vb.net application using MySQL database. I have connected two PCs in LAN and wanna make one of them as a server for the database. For the client, I used server's IP address instead of 'localhost' but the error is still occured that it cant connect to the database. Can you help me?
Try use the following as the mysql connection string
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Thanks for the tutorial and for keeping up with the questions for so long.
Am I correct that MySqlDataReader's .Read() method is a function that advances the reader to the next row and returns true if the row exist, false if it doesn't? This means that in between calling .Read(), I can access the data as many times as I want, retrieve column data in any order and retrieve the same column data more than once, all without causing the reader to move to the next row, is that right?
Welcome.
And, yes you are right. As long as you are inside the loop, you can use the field values of that row again and again.
Hey good guide but I want to make it online. I replaced the localhost with my public ip but it is not working when tested on another pc using the vb.net application to connect to the database. Extra info can be seen here. http://stackoverflow.com/questions/31739561/how-to-connect-to-mysql-database-from-another-pc-online-vb-net
Tnx Bro…
Nice Job Man!!!!!
Hello there!
You explained really well. But I have a problem, it doesn't appear any error message and says I've saved it correctly, but then, when I look into phpMyadmin, nothing appears.
Why does this happen?
Hi sir,
If i already have mysql, with .sql extension, how can I use this database in VB?? Your tutorial seems for new DB, not the existing one.
This is tutorial is on how to connect and user mysql DB with VB.NET.
At the "Prerequisites" part of the tutorial, I have provided a freely available mysql DB IDE software's download link. You can use that software to import your .SQL db file to mysql server.
[ I AM NOT ANYONE'S SIR ]
Something is obviously going wrong 😛
Thanks.
WC Bro
It must work simply. Anyway, what have you installed, any WAMP, XAMP etc. or only the mysql server?
Super!!!! It WORKS!!! You are SUPER!!!!
what is the code for search in??
Basic searching is nothing but a SELECT query.
Thanks.