VB.Net Connection With MySQL

Before we begin, to use MySQL with VB.NET you will need to download the MySQL Connector/NET which is available from
http://dev.mysql.com.../connector/net/. At the time of writing, the newest version is 6.1. The installer will install
the connector on your system ready for use.

Once the connector is installed, start a new project in VB.NET (Im using 2008) and you will then need to add a reference to the connector.

Choose "Add Reference" from the Project menu, then select "Browse" and browse to the installation folder where the connector was installed,
choose "MySQL.Data.dll" .

Resized to 65% (was 781 x 390) - Click image to enlarge

You may also have to add a reference to "System.data.dll". Now inport the Connector/NET to use its Namespace.

1	Imports MySql.Data.MySqlClient

Save the project.

Now lets design the form,
Place 3 labels, 3 text boxes and 2 buttons onto your form. Change the labels to the following "Server", "Username" and "Password".
Name the textboxes to the following "txtServer", "txtLogin", "txtPassword". Change the buttons to "Login" and "Cancel".

It should look like the picture below.


Double click the Cancel button and add the following code into the Sub Function. This will close the application when the Cancel button us pressed.

1	Application.Exit()

Now we need to add a MySQLConnection object, to do this - add the following to the "Public Class" of the form. Click the Login button and add the following.

1	Dim MysqlConn as MySQLConnection

Next, add the following to instanciate the MySQLConnection object.

1	MysqlConn = New MySqlConnection()

Now to set the connection string which will be used - this is similar to say - connecting to an MS Access database.
Here is an example of a connection string. This is a reflection on my test database I have set up, hence there is no password.

1	server=localhost; user id=root; password=; database=test

Instead of having a hard coded connection (which of course you can for security), we will be using the textboxes that are on the form like so.

1	MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _

2	& "user id=" & txtUsername.Text & ";" _

3	& "password=" & txtPassword.Text & ";" _

4	& "database=test"

Now we want to actually Open the connection, so we add this to the Login button precedure.

1	MysqlConn.Open()

Lets add a message box to show if the connection has been open successfully.

1	MessageBox.Show("Connection to Database has been opened.")

Lastly, now that we have opened the connection, and because we wont be using any tables in this tutorial, we will close the connection and then
free the resources used.

1	MysqlConn.Close()

2	MysqlConn.Dispose()

We can if we wanted to and it is good programming, is to catch any errors - when the connection cannot be opened, you will be notified by a message box,
so we will use the Try, Catch and Finally method.

1

Try

2	Mysql.conn.Open()

3	MessageBox.Show("Connection to Database has been opened.")

4	Mysqlconn.Close()

5	Catch myerror As MySqlException

6	MessageBox.Show("Cannot connect to database: " & myerror.Message)

7

Finally

8	Mysql.conn.Dispose()

9

End Try

And this is what all your code should look like,

view sourceprint?

01	Imports MySql.Data.MySqlClient

02	Public Class frmLogin

03

04	Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click

05	Application.Exit()

06

End Sub

07	Dim MysqlConn As MySqlConnection

08	Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

09

10

11	MysqlConn = New MySqlConnection()

12

13	MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _

14	& "user id=" & txtUsername.Text & ";" _

15	& "password=" & txtPassword.Text & ";" _

16	& "database=test"

17

18

Try

19	MysqlConn.Open()

20	MessageBox.Show("Connection to Database has been opened.")

21	MysqlConn.Close()

22	Catch myerror As MySqlException

23	MessageBox.Show("Cannot connect to database: " & myerror.Message)

24

Finally

25	MysqlConn.Dispose()

26

End Try

27

28

End Sub

29

End Class

Now lets save the application, and then run it and try it.

Connection Successful.

Resized to 96% (was 524 x 234) - Click image to enlarge

Connection UnSuccessful - using a password for a non-password protected connection.

Resized to 67% (was 752 x 233) - Click image to enlarge

Posted in: Programming, Tutorial