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

Submitted by: 
Visitors have accessed this post 572 times.

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


Comments

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

Thanks.

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.