How to Create an Auto-Save and Auto-Update Based on DataGridView Using VB.Net and Access Database

Submitted by janobe on Tuesday, July 23, 2019 - 22:09.

In this tutorial, I will teach you how to auto-save and auto-update the data in the database using ms access database and vb.net. This method has the ability to add the data in the database automatically when you begin to write/input any value in the cell of the datagridview. It is also has a function that you can update the data in the database when you change the current value in the cell of the datagridview. Hope this method will help you with your current problem or in your project.

Creating an Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application in visual basic. ps1

Step 2

Do the form just like shown below. ps2

Step 3

Press F7 to open the code editor. In the code editor, add a namespace for OLeDB to access OLeDB libraries .

Imports System.Data.OleDb

Step 4

Create a connection between the access database and c#. After that, declare all the classes and variables that are needed.

 
    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
                                                     Application.StartupPath & "\peopledb.accdb")
 
    Dim cmd As OleDbCommand
    Dim da As OleDbDataAdapter
    Dim dt As DataTable
    Dim sql As String
    Dim maxrow As Integer

Step 5

Create an auto-save method.

    Private Sub Auto_Save(sql As String)
        Try
            con.Open()
            cmd = New OleDbCommand
            With cmd
                .Connection = con
                .CommandText = sql
                .ExecuteNonQuery()
            End With
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

Step 6

Create a method for retrieving data in the database that will be displayed in the datagridview.

 
    Private Sub loaddtg(ByVal sql As String, ByVal dtg As DataGridView)
        Try
            con.Open()
            cmd = New OleDbCommand
            da = New OleDbDataAdapter
            dt = New DataTable
 
            With cmd
                .Connection = con
                .CommandText = sql
            End With
 
            da.SelectCommand = cmd
            da.Fill(dt)
 
            dtg.DataSource = dt
 
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
 
    End Sub

Step 7

Create a function for getting the total number of rows in the database.

 
    Private Function numrows(sql)
        Try
            con.Open()
            cmd = New OleDbCommand
            da = New OleDbDataAdapter
            dt = New DataTable
 
            With cmd
                .Connection = con
                .CommandText = sql
            End With
 
            da.SelectCommand = cmd
            da.Fill(dt)
 
            maxrow = dt.Rows.Count
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
 
        Return maxrow
    End Function

Step 8

Double click the form and do the following code for displaying data in the datagridview.

        sql = "SELECT * FROM tblperson"
        loaddtg(sql, DataGridView1)

Step 9

Write the following code for saving the data automatically in the database.

 
    Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
 
        maxrow = DataGridView1.RowCount - 1
        DataGridView1.Rows(DataGridView1.RowCount - 1).Cells(0).Value = maxrow
 
        For i As Integer = 0 To DataGridView1.RowCount - 2
            With DataGridView1.Rows(i)
                sql = "SELECT * FROM tblperson WHERE ID=" & .Cells(0).Value
                maxrow = numrows(sql)
 
                If maxrow > 0 Then
                    sql = "UPDATE tblperson SET Fname='" & .Cells(1).Value &
                        "',Lname='" & .Cells(2).Value & "',Address='" & .Cells(2).Value &
                        "' WHERE ID=" & .Cells(0).FormattedValue
                    Auto_Save(sql)
                Else
                    sql = "INSERT INTO tblperson (ID,Fname,Lname,Address) VALUES ('" & .Cells(0).Value & "','" & .Cells(1).Value & "','" & .Cells(2).Value & "','" & .Cells(3).Value & "')"
                    Auto_Save(sql)
                End If
            End With
        Next
 
    End Sub

Download the complete source code and run it on your computer. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below

Comments

Request for Date wise search Project

Plz make a coding for searching the data between 2 dates from MSAccess data by VB.NET. Thanks.

Add new comment

Add new comment
2622 views