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

Submitted by janobe on Sunday, October 28, 2018 - 20:58.

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.

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

Create a function for saving the data in the database.

Function SavaData(sql As String)
        Try
            con.Open()
            cmd = New OleDb.OleDbCommand()
            With cmd
                .Connection = con
                .CommandText = sql
                result = .ExecuteNonQuery()
            End With
            If result Then
                result = True
            Else
                result = False
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
        Return result
    End Function

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

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dtg As DataGridView = DataGridView1
        Dim i As Integer
 
        For i = 0 To dtg.RowCount - 2
            With dtg.Rows(i)
                sql = "INSERT INTO tblperson (Fname,Lname,Address) VALUES ('" & .Cells(0).FormattedValue & "','" & .Cells(1).FormattedValue & "','" & .Cells(2).FormattedValue & "')"
                result = SavaData(sql)
            End With
        Next
        If result = True Then
            MsgBox("Data has been saved in the database")
 
        Else
            MsgBox("error saving function. Unable to save", MsgBoxStyle.Exclamation)
        End If
 
    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

857 views