GridView CRUD Operations in VB.NET using ADO.NET and MySQL Database

ADO.NET tutorial : how to create connection using ADO.NET and CRUD (create,read,update,delete) data using ADO.NET and MySQL Database?

VB.NET SQLite Tutorials : CRUD (add,update,delete) Operations ADO.NET

VB.NET Tutorials Create Connections to SQLite Connection String

ADO.NET Tutorials - Using ADO.NET as a data provider we will create simple CRUD operations in VB.NET and MySQL Database. This application will make a connection in a Module Connection using ADO.NET as as a data provider. If connection is successfully, we will load all data from one table in MySQL Database and bind it to the DataGridView, so we can update the data from a GridView.

Please read :

1. GridView CRUD operations using ODBC connection + Mysql Database
2. CRUD Operations with SQL Server

 On this project, will complete with search function. You can search or fill a GridView from a TextBox and show the result. Read tutorial here How to Fill Data Into DataGridView.

Create ADO.NET CRUD examples

Open your visual studio and create new project "ADOnetCRUD" and at the form1.vb just design look like this picture :

 After done, we will create new form, this form for add new data into database, just create form2.vb and design them look like this picture :

Ok, will looking nice. Now leave all our form, just create new Module COnnection (ModuleConnections.vb)

Source Code Module Connection

Because we will create connection using ADO.Net, so you must download and install MySQL .NET Driver for ado.net here http://dev.mysql.com/downloads/connector/net/

After MySQL .NET driver installed on your PC, add it into our Project References, then write all source code below :

' we will import MySQL Data Namespaces
Imports MySql.Data.MySqlClient
Module ModuleConnections
    Public conn As MySqlConnection ' declare our connection
    Sub openConnection()
        Dim server As String = "localhost" ' our server name
        Dim userID As String = "root" ' our user id
        Dim password As String = "" ' our server password
        Dim database As String = "crudjava" ' configure with your MySQL database
        Try
            Dim query As String = "Server=" & server & "; user id=" & userID & ";password=" & password & "; database=" & database & ";Convert Zero Datetime=True"
            'sorry
            conn = New MySqlConnection(query)
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
        Catch ex As Exception
            MsgBox("Connection Filed" + ex.ToString)
        End Try
    End Sub
End Module

Then back to Form1.vb

Source Code View, Save, Delete (Form1.Vb)

Imports MySql.Data.MySqlClient
Public Class Form1
    Dim cmd As MySqlCommand
    Dim da As MySqlDataAdapter
    Dim ds As DataSet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        showData()
        GridSetting()
    End Sub
    Sub showData()
        ' we will show data from MySQL database into a DataGridView
        openConnection() ' open connection
        cmd = New MySqlCommand("SELECT * FROM biodata", conn)
        da = New MySqlDataAdapter(cmd)
        ds = New DataSet
        da.Fill(ds, "biodata")
        ' set datagridview datasource from dataset
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "biodata"

        cmd.Dispose()
        da.Dispose()
        conn.Close()
    End Sub
    Sub GridSetting()
        ' setting the Header Column width and name
        DataGridView1.Columns(0).HeaderText = "ID" ' configure with your database
        DataGridView1.Columns(1).HeaderText = "Name"
        DataGridView1.Columns(2).HeaderText = "Nis"
        DataGridView1.Columns(3).HeaderText = "Class"
        DataGridView1.Columns(4).HeaderText = "Address"

        DataGridView1.Columns(0).Width = 80
        DataGridView1.Columns(1).Width = 200
        DataGridView1.Columns(2).Width = 100
        DataGridView1.Columns(3).Width = 100
        DataGridView1.Columns(4).Width = 200
    End Sub
    Private Sub UpdateData(ByVal sql As String)
        Dim objCMD As New MySqlCommand
        Try
            openConnection() ' open our connection
            objCMD.Connection = conn
            objCMD.CommandType = CommandType.Text
            objCMD.CommandText = sql
            objCMD.ExecuteNonQuery()
            objCMD.Dispose()
            MsgBox("Data Hasbeen Updated", vbInformation)
        Catch ex As Exception
            MsgBox("Cann't update data into server" & ex.Message)
        End Try
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim row, id As Integer
        Dim name, nis, klass, address As String
        row = DataGridView1.CurrentRow.Index
        id = DataGridView1(0, row).Value
        name = DataGridView1(1, row).Value
        nis = DataGridView1(2, row).Value
        klass = DataGridView1(3, row).Value
        address = DataGridView1(4, row).Value

        ' create query to update data into MySQL database
        Dim queryUpdateData As String = "UPDATE biodata set nama='" & name & "', nis='" & nis & "',kelas='" & klass & "',alamat='" & address & "' where id=" & id & ""
        ' call function to update the data
        UpdateData(queryUpdateData)
        ' fill all new data into a gridview
        showData()
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim deletedata As String
        Dim id, row As Integer
        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
        ' create query to delete data from database
        deletedata = "DELETE from biodata where id=" & id & ""
        ' call function to update data
        UpdateData(deletedata)
        showData()
    End Sub

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

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        AddNewData.Show() ' open the new form
    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
        ' we will fillter the datagridview from text
        openConnection()
        da = New MySqlDataAdapter("select * from biodata where nama like '" & TextBox1.Text & "%'", conn)
        Dim dt As New DataTable
        da.Fill(dt)
        DataGridView1.DataSource = dt
        da.Dispose()
        conn.Close()
    End Sub
End Class

Source Code Add New Data (Form2.Vb)

Imports MySql.Data.MySqlClient
Public Class AddNewData
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim saveBiodata As String
        Dim message As String
        message = MsgBox("Are you sure to add new data into database? ", vbYesNo + vbInformation, "Warning")
        If message = vbNo Then
            Exit Sub
        End If
        ' create query to add new data into a database
        saveBiodata = "INSERT INTO biodata(id,nama,nis,kelas,alamat)values(" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "')"
        ' create function to update data
        ' you can add the update data function into our module
        ' so, just call it from here

        UpdateData(saveBiodata)
        With Form1
            .DataGridView1.Refresh()
            .showData()
        End With
    End Sub
    Private Sub UpdateData(ByVal sql As String)
        Dim objCMD As New MySqlCommand
        Try
            openConnection() ' open our connection
            objCMD.Connection = conn
            objCMD.CommandType = CommandType.Text
            objCMD.CommandText = sql
            objCMD.ExecuteNonQuery()
            objCMD.Dispose()
            MsgBox("Data Hasbeen Updated", vbInformation)

        Catch ex As Exception
            MsgBox("Cann't update data into server" & ex.Message)
        End Try
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Me.Close()
    End Sub
End Class

After all done, yes you have finished CRUD operations using ADO.NET Project. if you are still confused, just watch our video tutorial below.

Video Tutorial CRUD Operation using ADO.NET and MySQL Database

Download Full Source code CRUD Operations Using ADO.NET
Download Example Databases