Insert, delete, update datagridview on VB.Net

DataGridView is a simple way to display the data in table form. The data can be from remote database such as MS Access, SQL Server or any other sources. Its also easy to update the back end database directly from DataGridView. Any changes made to the datagridview can be updated to the data source. I will show you how.

Please refers to my previous post on how to load MS Access data table to DataGridView.

The form design

The Save and Update button will update the origin database with the new data from datagridview. For example if user add new record via the data grid view, or delete a record, it will also delete or add record to the origin database.

The flow

First, we need to fill the data grid view with the data from our source, such as MS Access database. This code will be executed during the form load.

Public strSQL As String
    Public MyAdapter As OleDb.OleDbDataAdapter
    Public Dt As New DataTable
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            MyModuleConnect.Connect()
            strSQL = "Select * from Tbl_DHCollar"
            MyAdapter = New OleDb.OleDbDataAdapter(strSQL, MyModuleConnect.MyConn)
            
            Dt = New DataTable()
            MyAdapter.Fill(Dt)
            Me.DataGridView1.DataSource = Dt
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

The code above will load the table Tbl_DHCollar to DataGridView 1 during the form load. And make a note that I was created a module called MyModuleConnect to define the OleDb connection details. So I don’t have to redefine the OleDb connection again here.

Output:

OK now data is loaded to DataGridView. Now we can add code to the Save and Update button. The following code will perform update to the database on every changes made by user to the table above.

Private Sub btn_Save_Click(sender As Object, e As EventArgs) Handles btn_Save.Click
        'Save changes to datagridview
        Try
            MyAdapter.UpdateCommand = New OleDb.OleDbCommandBuilder(MyAdapter).GetUpdateCommand()
            MyAdapter.Update(Dt)
            MsgBox("Database updated", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

When the Save and Update button is clicked, the database will be updated.