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

Imports System.Data
Imports MySql.Data.MySqlClient

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Me.BindGrid()
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()
                    sda.Fill(dt)
                    dataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Using
End Sub