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 .
  1. 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.
  1.    
  2.     Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
  3.                                                      Application.StartupPath & "\peopledb.accdb")
  4.  
  5.     Dim cmd As OleDbCommand
  6.     Dim da As OleDbDataAdapter
  7.     Dim dt As DataTable
  8.     Dim sql As String
  9.     Dim maxrow As Integer

Step 5

Create an auto-save method.
  1.     Private Sub Auto_Save(sql As String)
  2.         Try
  3.             con.Open()
  4.             cmd = New OleDbCommand
  5.             With cmd
  6.                 .Connection = con
  7.                 .CommandText = sql
  8.                 .ExecuteNonQuery()
  9.             End With
  10.  
  11.         Catch ex As Exception
  12.             MsgBox(ex.Message)
  13.         Finally
  14.             con.Close()
  15.         End Try
  16.     End Sub    

Step 6

Create a method for retrieving data in the database that will be displayed in the datagridview.
  1.    
  2.     Private Sub loaddtg(ByVal sql As String, ByVal dtg As DataGridView)
  3.         Try
  4.             con.Open()
  5.             cmd = New OleDbCommand
  6.             da = New OleDbDataAdapter
  7.             dt = New DataTable
  8.  
  9.             With cmd
  10.                 .Connection = con
  11.                 .CommandText = sql
  12.             End With
  13.  
  14.             da.SelectCommand = cmd
  15.             da.Fill(dt)
  16.  
  17.             dtg.DataSource = dt
  18.  
  19.         Catch ex As Exception
  20.             MsgBox(ex.Message)
  21.         Finally
  22.             con.Close()
  23.             da.Dispose()
  24.         End Try
  25.  
  26.     End Sub

Step 7

Create a function for getting the total number of rows in the database.
  1.    
  2.     Private Function numrows(sql)
  3.         Try
  4.             con.Open()
  5.             cmd = New OleDbCommand
  6.             da = New OleDbDataAdapter
  7.             dt = New DataTable
  8.  
  9.             With cmd
  10.                 .Connection = con
  11.                 .CommandText = sql
  12.             End With
  13.  
  14.             da.SelectCommand = cmd
  15.             da.Fill(dt)
  16.  
  17.             maxrow = dt.Rows.Count
  18.         Catch ex As Exception
  19.             MsgBox(ex.Message)
  20.         Finally
  21.             con.Close()
  22.             da.Dispose()
  23.         End Try
  24.  
  25.         Return maxrow
  26.     End Function  

Step 8

Double click the form and do the following code for displaying data in the datagridview.
  1.         sql = "SELECT * FROM tblperson"
  2.         loaddtg(sql, DataGridView1)

Step 9

Write the following code for saving the data automatically in the database.
  1.  
  2.     Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit
  3.  
  4.         maxrow = DataGridView1.RowCount - 1
  5.         DataGridView1.Rows(DataGridView1.RowCount - 1).Cells(0).Value = maxrow
  6.  
  7.         For i As Integer = 0 To DataGridView1.RowCount - 2
  8.             With DataGridView1.Rows(i)
  9.                 sql = "SELECT * FROM tblperson WHERE ID=" & .Cells(0).Value
  10.                 maxrow = numrows(sql)
  11.  
  12.                 If maxrow > 0 Then
  13.                     sql = "UPDATE tblperson SET Fname='" & .Cells(1).Value &
  14.                         "',Lname='" & .Cells(2).Value & "',Address='" & .Cells(2).Value &
  15.                         "' WHERE ID=" & .Cells(0).FormattedValue
  16.                     Auto_Save(sql)
  17.                 Else
  18.                     sql = "INSERT INTO tblperson (ID,Fname,Lname,Address) VALUES ('" & .Cells(0).Value & "','" & .Cells(1).Value & "','" & .Cells(2).Value & "','" & .Cells(3).Value & "')"
  19.                     Auto_Save(sql)
  20.                 End If
  21.             End With
  22.         Next
  23.  
  24.     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
Tags
Auto-Save in VB.Net
Auto-Update in VB.net
vb.net
Access Database

Comments

Add new comment

About text formats