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" .
You may also have to add a reference to "System.data.dll". Now inport the Connector/NET to use its Namespace.
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.
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.
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.
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 UnSuccessful - using a password for a non-password protected connection.
Thanks for reading.