Read and write Data from MySQL Database in visual basic (VB.NET)

how to retrieve data from MySQL dataTable and display it in a table format using VB.NET windows application.

Here, we will see how to use Data Grid View along with data from SQL Server Database using SQL Data Adapter and Data Table in VB.NET Application.

For doing this we have to do two main things that are

  • Creating a database table in MySQL workbench
  • Fetching the data from MySQL table into VB.NET application in a table format

Creating a database table in MySQL workbench

First, we have to create a table in the MySQL database for this we have to follow these simple steps.

Step 1:

first, we have to create a table in our MySQL workbench if you have the MySQL workbench and MySQL server installed in your system then it’s good otherwise first download and install them with all the required things then move to the next step.

Step 2:

Open your MySQL workbench > click on create schema >  Apply to SQL script page will open >give the name to your schema > click on apply > again click on apply now the SQL script page will show you the output that “your SQL script is successfully applied to the database”> close the SQL script.

Step 3:

Click on the schema/Database which you have named in the above step it will be showing on the left side of the screen > just right click on the schema/database > right-click on Tables >  click on create table > give the name to your table > double click under the white section of table name > add columns and give the name and data type for those columns > click on apply.

Step 4:

Now add data into your table > from the left sidebar click on the table name in which you have to add data > from given three options choose last option that has a table icon

>The table window will open > then type the data in given columns one by one adding the rows when you complete one row by entering the data in all fields just click on enter button so the cursor will move to next row. By doing this complete your table with the data you want to enter > click on apply button.

Step 5:

A new window of SQL script will open > click on apply > next click on finish. Our table is now successfully created with containing data.

Fetching data from MySQL table into VB.NET Application

For fetching the data from our MySQL database table into our VB Form we also have to follow some steps here these are-

Step 1:

Open visual studio > go to File menu > New project > in visual Basic > add New Windows Form > add DataGridView control from toolbox by dragging it into form.

Step 2:

In first step, we have to download and install the MySQL Connector after that it needs to be connected with the MySQL database.
  • Download the MySQL Connector > then install the MySQL Connector >  when installation is complete open your Windows Explorer > find the MySql installation in the Program Files folder of Windows drive > here we will see a folder for MySQL Connector > inside that we will find the > copy it inside the BIN folder of our project.

Step 3:

Now we have to import the following namespaces in our application.

Imports System.Data
Imports MySql.Data.MySqlClient

Step 4:

In this step, we have to Bind the DataGridView with our records from MySQL Database Table.
If you know the ADO.Net then using MySql will be simpler for you because the MySQL Connector classes have very similar names to ADO.Net classes.
For example, in ADO.Net we have SqlConnection class and in MySQL here is MySqlConnection class. On the Form Load event of the page, the DataGridView control is located it will hold the records from the MySql database using a DataTable.
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub BindGrid()
    Dim conString As String = "Data Source=localhost;port=3306;Initial Catalog=AjaxSamples;User Id=Nida;password=pass@123"
    Using con As New MySqlConnection(conString)
        Using cmd As New MySqlCommand("SELECT * FROM employee", con)
            cmd.CommandType = CommandType.Text
            Using sda As New MySqlDataAdapter(cmd)
                Using dt As New DataTable()
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub

Dim conString As String = "Data Source=localhost;port=3306;Initial Catalog=AjaxSamples;User Id=Nida;password=pass@123"

This is a connection string. It is used by the data provider to establish a connection with the database. We specify the database name, host, user name and password.

We have to enter here the same username and password which we have provided in between the process of installation of  MySQL workbench in our system and Databse name would be which we want to connect with our VB.NET Application.


Now you can see that our table data is retrieving or fetching into our VB window form and fitted into Data Grid View.

By doing these steps we can simply connect our MySQL table with our VB.NET Application, and as result the grid view will show the data of our MySQL table in VB.NET Application in a table manner.