VB.NET Insert, Update, Delete Data From DataGridView Using MySQL Database

VB.NET For beginners : how to add, edit, delete data from database in a datagridview vb.net? how to CRUD DataGridview using MySQL Database?

VB.NET for beginners - How to add, edit, delete data from MySQL database in a DataGridView Vb.net using Odbc Connections? in the previews tutorial we have discussed about how to create simple CRUD operations using MySQL database, SQL Server, and Ms Access Database. please read :

1. How to CRUD (Create, Read, Update, Delete) Operations using MySQL V1 ?
2. How to CRUD Operations using MySQL and Metro UI Theme? 
3. How to CRUD Operations using SQL Server?
4. How to Add, Update, Delete Operations with Ms Access

And now, we will create simple application while we can update, add, delete new data from a DataGridView, so it's very easy. You must have a database (MySQL) database before, and connect it using Odbc Class, if you don't know how to connect with our database, just search on this blog. you will be fine.

Create New Project (CRUD in DataGridView)

Open your Visual studio and create new project, rename it with "VBNETcrudDataGridView", and at the Form1.vb just design it look like this one :

After Done, we will create new Form where this from will be use for create new data.

Create Form New Data (Form2)

Just rename New form with "NewData.vb" and design it look like images below :

Well, for this step we have done. now we will create a new Module where this module will create a connections and Save or Update data into database.

Source Code ModuleConnection.VB

Create new module and rename it with "ModuleConnection.Vb" and write all source code below :

' import namespaces ODBC class
Imports System.Data.Odbc
Module ModuleConnections
    ' declaration our connection
    Public connections As OdbcConnection
    Public Sub conn()
        Try
            ' using our DSN to connect with mysql database
            connections = New OdbcConnection("DSN=java_db;MultipleActiveResultSets=True")
            If connections.State = ConnectionState.Closed Then
                connections.Open() ' open connection
            End If
        Catch ex As Exception
            MsgBox("Failde")
        End Try
    End Sub
    ' Functions to add, edit, delete data into database
    Public Sub RUNSql(ByVal sql As String)
        Dim cmd As New OdbcCommand
        conn() ' open our connection
        Try
            cmd.Connection = connections
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            connections.Close()
            MsgBox("Data Hasbeen Saved !")
        Catch ex As Exception
            MsgBox("Error when saving data")
        End Try
    End Sub
End Module

After done, Please back to the form1.vb, we will start to code for the button1 (New Button), Button2 (Save Button), Button3 (Delete Button) and Button4 (Exit Button). Before do it, just Import NameSpaces into header of form 1 class,

' import namespaces ODBC class
Imports System.Data.Odbc

Source Code New Button (Button1)

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        FormAddNew.ShowDialog() ' open and show the add new data form
    End Sub

Source Code Save Button (Button2)

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim row, id As Integer
        Dim nama, nis, kelas, alamat, UpdateData As String
        ' Declare the variable to get value event click on datagridview
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        nama = DataGridView1(1, row).Value
        nis = DataGridView1(2, row).Value
        kelas = DataGridView1(3, row).Value
        alamat = DataGridView1(4, row).Value

        ' query to Update data into biodata tables
        UpdateData = "UPDATE biodata SET nama='" & nama & "',nis='" & nis & "',kelas='" & kelas & "',alamat='" & alamat & "' where id='" & id & "'"
        ' call function to update data
        RUNSql(UpdateData)
        ' fill new data into datagridview1
        LoadBiodata()
    End Sub

Source Code Delete Button (Button3)

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim DeleteData As String
        Dim row, id As Integer

        ' Declare the variable to get value event click on datagridview
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value

        Dim message As String
        message = MsgBox("Are you Sure to delete this data? ", vbYesNo + vbInformation, "Warning")
        If message = vbNo Then
            Exit Sub
        End If
        ' query to delete data from biodata tables
        DeleteData = "DELETE from biodata where id='" & id & "'"
        ' call function to update data
        RUNSql(DeleteData)
        ' fill new data into datagridview1
        LoadBiodata()
    End Sub

Source Code Link Label1

    Private Sub LinkLabel1_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked, LinkLabel2.LinkClicked
        Process.Start("www.hc-kr.com")
    End Sub

Source Code Sub LoadBiodata Into DataGridView

    Sub LoadBiodata()
        ' fill data from biodata table into datagridview1
        conn() ' open connections
        Dim da As OdbcDataAdapter ' declaration da to dataadapter
        Dim dt As DataTable ' dt is datatable

        da = New OdbcDataAdapter("SELECT * FROM biodata", connections) ' query to our MySQL database
        dt = New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt ' bind data into datagridview
        connections.Close() ' close connection
        da.Dispose()
    End Sub

Source Code Event Load Data

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadBiodata() ' show data into load event
    End Sub

Well you have done at the Form1 (Biodata Form), then we will create a code to the next form (Form Add New data), go to the Add New Data Form and Write all source code below. Don't forget to add NameSpaces into Header of the form2,

' import namespaces ODBC class
Imports System.Data.Odbc

Source Code Save Button (Button1)

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim SaveBiodata As String ' declare for save data
        Dim Message As String ' declare for messafe
        Message = MsgBox("Are you sure to add new data into database?", vbYesNo + vbInformation, "Informations")
        If Message = vbNo Then
            Exit Sub
        End If
        ' Save data into Biodata Tables
        SaveBiodata = "INSERT INTO biodata(id,nama,nis,kelas,alamat) VALUES('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"
        RUNSql(SaveBiodata)
        With Form1
            ' using form1
            ' refresh the gridview
            ' and show new data where has updated
            .DataGridView1.Refresh()
            .LoadBiodata()
        End With
        ' then close add form window
        Me.Close()
    End Sub

Source Code Cancel Button (Button2)

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Close()
    End Sub

Congrat, you have successfully created simple CRUD operations in DataGridView using MySQL database, just try to Debugging your application and let me know what happen if you press "F5" key.

If you are still confused with This tutorials, just watch this video tutorial.

Video Tutorial CRUD Operations in DataGridView

Download Source Code CRUD in DataGridView

Please share and Subscribe if this tutorial help you.