How to Save Multiple Data Using VB.Net and Access Database

Submitted by: 
Visitors have accessed this post 540 times.

In this tutorial, I will teach you how to save multiple data in the database by using vb.net and MS Access database. I create this tutorial because of the request of my followers. This method is very helpful for saving multiple data in just a click. Hope you will find this tutorial useful too.

Creating Application

Open Microsoft Visual Studio 2015 and create a new windows form application.
multisave231
Go to toolbox and add a Button and a Datagridview. Do the form as follows.
multisave12324
Open the code view and initialize the connection between MS Access and VB.Net. After that, declare all the classes and variables that are needed.

  1. Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\persondb.accdb;Persist Security Info=True")
  2. Dim cmd As OleDb.OleDbCommand
  3. Dim sql As String
  4. Dim result As Boolean

Create a function for saving the data in the database.

  1. Function SavaData(sql As String)
  2. Try
  3. con.Open()
  4. cmd = New OleDb.OleDbCommand()
  5. With cmd
  6. .Connection = con
  7. .CommandText = sql
  8. result = .ExecuteNonQuery()
  9. End With
  10. If result Then
  11. result = True
  12. Else
  13. result = False
  14. End If
  15. Catch ex As Exception
  16. MsgBox(ex.Message)
  17. Finally
  18. con.Close()
  19. End Try
  20. Return result
  21. End Function

Write this code to save multiple data in the database when the button is clicked.

  1. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  2. Dim dtg As DataGridView = DataGridView1
  3. Dim i As Integer
  4.  
  5. For i = 0 To dtg.RowCount - 2
  6. With dtg.Rows(i)
  7. sql = "INSERT INTO tblperson (Fname,Lname,Address) VALUES ('" & .Cells(0).FormattedValue & "','" & .Cells(1).FormattedValue & "','" & .Cells(2).FormattedValue & "')"
  8. result = SavaData(sql)
  9. End With
  10. Next
  11. If result = True Then
  12. MsgBox("Data has been saved in the database")
  13.  
  14. Else
  15. MsgBox("error saving function. Unable to save", MsgBoxStyle.Exclamation)
  16. End If
  17.  
  18. End Sub

The complete sourcecode is included. You can download it 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.


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.