This article will show you how to access MySQL server database using ODBC data provider. There are two ways to access MySQL Server database using ODBC data providers.
First, using ODBC data source name and second by passing connection string direct in the OdbcConnection object.
Using MySQL Database
Working with different data sources is only a matter of changing the connection string. You can access a MySQL database either using a DSN or using the direct database name in the connection string. You can use a database name directly as shown in the following code:
Dim connectionString as string = "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;"
Or you can use an ODBC DSN, as you can see from the following code that I’ve used TestDSN DSN to connect to the data source:
Dim conn As New OdbcConnection("DSN=TestDSN")
To test this code, create a Windows application and add a DataGrid control to the form and write code listed in Listing below (on the form load).
As you can see in Listing this code is similar to the code you saw earlier. It creates a connection, a data adapter, fills the dataset from the data adapter, and sets the dataset’s DefaultViewManager as the DataGrid control’s DataSource property.
'Listing: Accessing a MySQL database
Private Sub Form1_Load(sender As Object, e As System.EventArgs)
Dim connectionString As String = "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;"'
Dim conn As New OdbcConnection(connectionString)
conn.Open()
Dim da As New OdbcDataAdapter("SELECT CustomerID, ContactName, ContactTitle FROM Customers", conn)
Dim ds As New DataSet("Cust")
da.Fill(ds, "Customers")
dataGrid1.DataSource = ds.DefaultViewManager
conn.Close()
End Sub 'Form1_Load
Note:
For sample database you can convert Nortwind Access database to MYSQL database, and make sure you replace your database name, table, user ID and password.(Phillip N)
