Posted 20 July 2009 - 12:00 AM
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.
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.
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.
Connection UnSuccessful - using a password for a non-password protected connection.
Thanks for reading.
MultiQuote
| 