76 comments Tutorial, Visual Basic Tutorial

Using MySQL Database with Visual Basic .NET 2010

Posted by
Total Read 5,295

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

  1. MySQL Server for Windows or Any other installation of MySQL Server [DOWNLOAD]
  2. MySQL .NET Connector [DOWNLOAD]
  3. MySQL GUI – MySQL Workbench or Any other GUI or any way you prefer to work on MySQL [DOWNLOAD]
  4. Visual Basic .NET 2008/2010/2013

Concept Requirements

  1. Working with MySQL
    1. Creating database & tables etc.
    2. Insert, Select, Update and Delete queries
  2. Working with Visual Studio
    1. Creating projects
    2. Forms and elements in Visual Basic .NET
    3. 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
Okay, now open VB.net and create a new project and name it whatever you like and save it. Now do as the following.
From the top menu go to, Project > Add Reference

This action will open a new dialog box as follows

Here, select the Browse tab and locate the following location

C:Program FilesMySQLMySQL Connector Net 6.5.4Assembliesv2.0

Note: This directory may change as per where you have installed MySQL Connector.

Okay, now select MySQL.Data.dll as shown in the above screenshot and click OK. This action will add MySQL Dot Net Connector with our project.

So we are done with the database, let’s create the form for the project.

The Form

Design the form as follow,

Number
Type
Name
1
TEXTBOX
txt_firstName
2
TEXTBOX
txt_lastName
3
BUTTON
Button1
4
BUTTON
Button2
5
LISTBOX
listBox_users

That’s it. We are done with the database and form so far, lets start coding in the next part.
 

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.

  1. dbconn is the connection to the database.
  2. sql is a string, which will actually hold our query.
  3. dbcomm is the MySqlCommand that will execute the query.
  4. dbread is the one which will hold those data that we will fetch using query from DB.

    5.  

     

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

  1. 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
  2. user id: The user of the mysql database server
  3. 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.
  4. 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.
  5. 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.

76 comments

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

  4. 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"?

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

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

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

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

  16. Thank you for your reply i will try that and if i need more help i will post again. Thank you once more.

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

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

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

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

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

  31. if my database is online then how to connect mysql database with vb.net
    Please tell me how to connect.

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

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

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

  50. I guess you have never heard of SQL Injection the way you are building your SQL statements?

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

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

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

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

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

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

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

Comments are closed.