Skip to main content

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


Body

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

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.