Page 1 of 1

Use VB.NET to connect to MySQL Rate Topic: ***** 3 Votes

#1 PDUNZ  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 376
  • Joined: 17-July 09

Post icon  Posted 20 July 2009 - 12:00 AM

In this tutorial, I am only going to show you how to make a connection to MySQL, Next tutorial I will show
how to actually get records from a table.

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" .
Attached Image

You may also have to add a reference to "System.data.dll". Now inport the Connector/NET to use its Namespace.
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.
Attached Image

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.
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.
Dim MysqlConn as MySQLConnection


Next, add the following to instanciate the MySQLConnection object.
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.
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.
MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _
	& "user id=" & txtUsername.Text & ";" _
	& "password=" & txtPassword.Text & ";" _
	& "database=test"


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


Lets add a message box to show if the connection has been open successfully.
 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.
 MysqlConn.Close()
 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.
 Try
	Mysql.conn.Open()
	MessageBox.Show("Connection to Database has been opened.")
	Mysqlconn.Close()
  Catch myerror As MySqlException
	MessageBox.Show("Cannot connect to database: " & myerror.Message)
  Finally
	Mysql.conn.Dispose()
  End Try



And this is what all your code should look like,
Imports MySql.Data.MySqlClient
Public Class frmLogin

	Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click
		Application.Exit()
	End Sub
	Dim MysqlConn As MySqlConnection
	Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click


		MysqlConn = New MySqlConnection()

		MysqlConn.ConnectionString = "server=" & txtServer.Text & ";" _
		& "user id=" & txtUsername.Text & ";" _
		& "password=" & txtPassword.Text & ";" _
		& "database=test"

		Try
			MysqlConn.Open()
			MessageBox.Show("Connection to Database has been opened.")
			MysqlConn.Close()
		Catch myerror As MySqlException
			MessageBox.Show("Cannot connect to database: " & myerror.Message)
		Finally
			MysqlConn.Dispose()
		End Try

	End Sub
End Class


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

Connection Successful.
Attached Image

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


Thanks for reading.

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 2
  • +

Replies To: Use VB.NET to connect to MySQL

#2 abdul_kuku  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-August 09

Posted 14 August 2009 - 11:30 PM

:D :D :D That was very helpfull,

When is the next tutorial(about geting records from a table) going to be posted??
Was This Post Helpful? 0
  • +
  • -

#3 PDUNZ  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 376
  • Joined: 17-July 09

Posted 18 August 2009 - 08:35 AM

Maybe in the next week or so, just have to tidy it up a bit first.
Was This Post Helpful? 0
  • +
  • -

#4 Chronicade  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 57
  • Joined: 05-September 09

Posted 05 September 2009 - 06:15 PM

Thanks mate, beautiful ;)
Was This Post Helpful? 0
  • +
  • -

#5 briambo  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 59
  • Joined: 09-November 09

Posted 26 November 2009 - 06:34 AM

thanks mate this helped alot!
Was This Post Helpful? 0
  • +
  • -

#6 arkerio  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 27-November 09

Posted 20 January 2010 - 12:32 PM

NYC tutorial :^:
plz post ur another tutorial how to add dl and edit
records in data base :blink: :P
Was This Post Helpful? 0
  • +
  • -

#7 buetsss  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-May 11

Posted 11 May 2011 - 05:23 AM

Concise and simple. Very well written. Thanks.
Was This Post Helpful? 0
  • +
  • -

#8 customize  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 12-September 11

Posted 12 September 2011 - 08:31 PM

i can only connect to localhost. i would like to be able to connect to an external server, i know that that server needs to have remote sql enabled on it.

1. My question is there any way around that without having me calling up the sql server people and asking them to enable sql remote on my server.

2. there is one more problem that i have at the moment, i told you that i can connect to my localhost server but i dont know how to make a user field for in form1 to access the data ID, username, password and just to be able to login.

I think it would be best if anyone could provide some code for an example for me and other people like me to learn from.

Thanks in advance
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1