MySQL Database with Visual Basic .NET 2010

Using MySQL Database with Visual Basic .NET 2010

Target: Target of this tutorial is to establish connection to
               MySQLserver Database from Visual Basic .NET 2010.

Assumption: I am assuming that you are familiar with the following

1.    Creating Database, Table in MySQL

2.    Adding data to MySQL database.

3.    Creating projects, saving projects, writing codes in VB.net.

Prerequisites: To achieve the target, you will have to have the following things,

1.    The MySQL Server

          Download Directly From Here

          Click Here to Choose Your Download

2.    The MySQL .NET Connector

          Download Directly From Here

          Click Here to Choose Your Download

3.    An IDE for DB Management. Such as SQLyog, PHPmyadmin etc. If you don’t have any, then you can download a free tool.

          Click Here to Download MySQL Query Browser

4.    Finally, Visual Basic .NET 2010 [ 2008 will also work ]

Once you have the above things, install all of them.

The Database: For the sake of simplicity, create a database with your IDE or whatever the way you like as “my_test_projects” and a table in it with the name “testing_mysql_vb” with the following structure.

Field Name	Data Type	Primary	Auto_increment
id	int	yes	Yes
user_name	text	no	no

Ok, now open VB.net and create a new project and name it whatever you like and save it. Now do as the following.

Go to menu : 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 Files\MySQL\MySQL Connector Net 6.5.4\Assemblies\v2.0

Note: “C:\Program Files\” may change as per where you have installed MySQL Connector.

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

The Form: Design the form as follow,

The above form contains the following controls and settings.

Control	Name	Value	Description
Label	Label1	User Name	This is the label for the input field of User Name
Textbox	text_user_name		This will be used to have the user name to be stored
Button	btn_save	Save	This button will save the inputted user name in DB
Listbox	list_users		This will list the saved user names when pulled out from DB
Button	btn_get	Get Data	This button will pull out the user names from DB and will show then in the “list_users” list.

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 your_class_name write the followings

Public dbconn As New MySqlConnection

Public sql As String

Public dbcomm As 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.

Ok, Now we are going to make the connection. We will open the connection to the DB in the “load” 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;database=my_test_projects;")

        Try

            dbconn.Open()

        Catch ex As Exception

            MsgBox("Error in connection, please check Database and connection server.")

        End Try

Here, look at the connection string which as follows

          dbconn = New MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")

Here, “Data Source” means where the database is stored. 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” is your ID, you better know what you have used while installing the MySQL Server.

“database” is the name of the database on which we will be working on.

Note: If you have also set a password for your DB Server then you will have put that in your connection string. Then the connection string will look like this

          dbconn = New MySqlConnection("Data Source=localhost ; user id=root ; password=your_password_here ; database=my_test_projects;")

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("Error in connection, please check Database and connection server.")

Run your program to see if you get the Error Message, if don’t then lets go ahead.

Double click the “btn_save” button to write the following codes in its click event.

sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"

        Try

            dbcomm = New MySqlCommand(sql, dbconn)

            dbread = dbcomm.ExecuteReader()

            dbread.Close()

        Catch ex As Exception

            MsgBox("Error in saving to Database. Error is :" & ex.Message)

            dbread.Close()

            Exit Sub

        End Try

        MsgBox("The User Name was saved.")

        text_user_name.Text = ""

First of all we are writing our SQL Query String as

          sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"

This query will insert a “NULL” value for the “id” and the TEXT of the text field text_user_name as the value for the “user_name” field in the table testing_mysql_vb. Well, not now, this is just a string. First we have assigned this sql and our opened connection as a MySQL Command with the following line

          dbcomm = New MySqlCommand(sql, dbconn)

Then we have executed the command by

          dbread = dbcomm.ExecuteReader()

The “dbread” has nothing to do here as we are inserting data, not fetching. However we will still have to close this by

          dbread.Close()

We have kept these lines again in a Try…Catch block, where in the Catch block we showed a message regarding that the data insertion failed and closed the “dbread” and also got out of he sub with “End Sub”. After the Try…Catch block we have plotted another message regarding that the data has been successfully inserted and we have also cleared the text of the “text_user_name” textbox.

So, now you can run your program and try putting something in the “text_user_name” textbox and then clicking on “Save” to see which message appears. If the msgBox("Error in saving to Database. Error is :" & ex.Message) appears, this means the data wasn’t saved, Else if the MsgBox("The User Name was saved.") appears, this means the data has been saved successfully.

To get the saved names back from the DB, double click the “btn_get” button and write the following codes in it

           list_users .Items .Clear

        sql = "SELECT * FROM testing_mysql_vb"

        Try

            dbcomm = New MySqlCommand(sql, dbconn)

            dbread = dbcomm.ExecuteReader()

            While dbread.Read

                list_users.Items.Add(dbread("user_name"))

            End While

            dbread.Close()

        Catch ex As Exception

            MsgBox("Error in collecting data from Database. Error is :" & ex.Message)

            dbread.Close()

            Exit Sub

        End Try

First of all we are clearing the list_us to list the names that will be pulled out from the DB

          list_users .Items .Clear

Then we have written the SQL Query string to pull out everything from the “testing_mysql_db” 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 “btn_save” event.

But after that, we have started a while loop as

          While dbread.Read

              list_users.Items.Add(dbread("user_name"))

       End While

Here we made an infinite loop which will run until our reader “dbread” has data. In each loop we add one user name from “dbread” to our list “list_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 now, run your program again and click the “Get Data” button and the list on its left will be filled up with the names you have inserted earlier.

And that’s it. The Full code is as follows,

Imports MySql.Data.MySqlClient

Public Class Form1

    Public dbconn As New MySqlConnection

    Public sql As String

    Public dbcomm As MySqlCommand

    Public dbread As MySqlDataReader

   

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dbconn = New MySqlConnection("Data Source=localhost;user id=root;database=my_test_projects;")

        Try

            dbconn.Open()

        Catch ex As Exception

            MsgBox("Error in connection, please check Database and connection server.")

        End Try

    End Sub

    Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click

        sql = "INSERT INTO testing_mysql_vb(id,user_name) VALUES(NULL,'" & text_user_name.Text & "')"

        Try

            dbcomm = New MySqlCommand(sql, dbconn)

            dbread = dbcomm.ExecuteReader()

            dbread.Close()

        Catch ex As Exception

            MsgBox("Error in saving to Database. Error is :" & ex.Message)

            dbread.Close()

            Exit Sub

        End Try

        MsgBox("The User Name was saved.")

        text_user_name.Text = ""

    End Sub

    Private Sub btn_get_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_get.Click

        list_users .Items .Clear

        sql = "SELECT * FROM testing_mysql_vb"

        Try

            dbcomm = New MySqlCommand(sql, dbconn)

            dbread = dbcomm.ExecuteReader()

            While dbread.Read

                list_users.Items.Add(dbread("user_name"))

            End While

            dbread.Close()

        Catch ex As Exception

            MsgBox("Error in collecting data from Database. Error is :" & ex.Message)

            dbread.Close()

            Exit Sub

        End Try

    End Sub

End Class

資料來源:http://tanmayonrun.blogspot.tw/2013/01/using-mysql-database-with-visual-basic.html