Configure MySQL connection in VB.NET with ADO.NET

Migrate your Microsoft Access Database to MySQL Database?

Visual Basic.Net provides variety of Data Provider to work with ADO.NET depends on the needs of the projects. For instance, to store a large amount of data to your database, it’s prefer to used MySQL, Oracle, and MSSQL because MS Access Database only provides limited features compare other Databases. Fortunately, All Data Providers works on the same way because if you know how to use OLEDB Provider also you can easily work with others.

For example.

OLEDB Class

Dim da As OleDbDataAdapter

Dim cmd As New OleDbCommand

Dim conn As New OleDbConnection

MySQL Class

Dim da As MySqlDataAdapter

Dim cmd As New MySqlCommand

Dim conn As New MySqlConnection

To use MySQL Class name. Right click anywhere in your Toolbox and Choose Items…

It will displays dialog window, the default tab is .Net Framework Components. Locate MySqlCommand, MySqlConnection, and MySqlDataAdapter.

Then, click OK.

Now, just above Public Class Form1 import class name Imports MySql.Data.MySqlClient.

To setup the connection string.

connString = "server=localhost;user id=root;password=123;database=dbinfo"

connString a variable name that holds your data provider connection, in server provider you can set localhost as number 127.0.0.1 because were both identical. If you don’t have password, let empty and lastly, your database name.

Your code looks like the following.

Imports MySql.Data.MySqlClient

Public Class Form1

    Dim connString As String

    Dim da As MySqlDataAdapter

    Dim ds As New DataSet

    Dim cmd As New MySqlCommand

    Dim sql As String

    Dim conn As New MySqlConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Public Sub myConnection()

        connString = "server=localhost;user id=root;password=123;database=dbinfo"

        conn.ConnectionString = connString

        conn.Open()

    End Sub

    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click

        Call myConnection()

        MessageBox.Show("Opened")

        conn.Close()

    End Sub

    Public Sub retrieveData()

        Call myConnection()

        sql = "SELECT * FROM tblInfo"

        da = New MySqlDataAdapter(sql, conn)

        da.Fill(ds, "dbinfo")

        DataGridView1.DataSource = ds.Tables("dbinfo")

        conn.Close()

    End Sub

    Private Sub btnFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFill.Click

        Call retrieveData()

    End Sub

End Class

The Windows Form Looks like the following.