How to Connect Visual Basic to Mysql
- 1). Download the MySql Connector/Net driver file from the MySql website (see Resources). Follow the instructions provided on the page to install the package on your computer.
- 2). Open Visual Studio.Net (2003 or later) and create a new console application project called "mySqlSample."
- 3). Add a reference to the MySql Connector/Net package. Click "Project" then "Add Reference." After a short wait, choose "MySql.Data" from the list of Dot Net references and click the "Add Reference" button. Also, add a line to the top of the program to import the "MySql.Data.MySqlClient" namespace. This namespace includes MySql versions of the connection, command, data adapter and other standard ADO data objects.
Imports MySql.Data.MySqlClient
Module Module1
Sub Main() - 4). Define a connection string that points to your MySql database server.
Dim connString As String = "Server=192.168.0.13;UID=myUserID; PWD=myPassword; Database=myDatabase;"
Replace the server IP address (192.168.0.13) with the url or IP address of the MySql server machine then change myUserID, myPassword and myDatabase to the credentials needed to connect with this database server. - 5). Open a connection to the database using the connection string.
Dim conn As New MySqlConnection
Try
conn.ConnectionString = connString
conn.Open()
Catch ex As Exception
Console.WriteLine("Cannot open the database connection " & ex.Message)
Return
End Try
The MySqlConnection conn uses the connection string to open the database. If any problems occur, the catch block reports the error and the program ends. - 6). Define a query to test this connection.
Dim sqlString As String = "Select table_name "
sqlString &= "FROM information_schema.tables "
sqlString &= "Order by table_name;"
This query requests a list of table names from the information_schema database. MySql will return a list of the tables that are accessible with the credentials provided in the connection string. See the reference on INFORMATION_SCHEMA Tables below for more information describing how to retrieve MySql meta-data. - 7). Run the query using the SqlString and the MySqlConnection.
Dim rdr As MySqlDataReader = Nothing
Dim tblName As String
Try
Dim cmd As New MySqlCommand(sqlString, conn)
rdr = cmd.ExecuteReader
While rdr.Read
tblName = rdr.GetString(0)
Console.WriteLine(tblName)
End While
Catch ex As Exception
Console.WriteLine("Cannot retrieve data " & ex.Message)
Finally
rdr.Close()
End Try
conn.Close()
End Sub
End Module
This code creates a MySqlCommand object then uses it to create a MySqlDataReader. Using the reader, it retrieves one table name at a time and writes it to the console.
Source...