{ Berbagi, Menulis, Dan Mengajar } Ilmu…

In this article, I 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

As I’ve been saying, 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 1 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.

Private Sub Form1_Load(ByVal sender As Object, ByVal 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

Listing 1. Accessing a MySQL database

Note:

In this code, I have converted Northwind Access database to MySQL database. You make sure you replace your database name, table, user ID and password.