How to Delete Multiple Data Using VB.Net and MS Access Database

Submitted by janobe on Wednesday, October 31, 2018 - 19:29.

In this tutorial, I will teach you how to delete multiple data using vb.net and MS Access database. This method will give you convenience in deleting data and you can select what data to be deleted by using a checkbox. This will surely help you especially if you have multiple data to delete. Follow the procedure below to see how it works.

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application. multidelete123

Step 2

Do the form just like shown below. multidelete321

Step 3

Go to the code view and write this code for initializing classes, declaring variable and the connection between access database and VB.net.

   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 da As OleDb.OleDbDataAdapter
    Dim dt As DataTable
    Dim sql As String
    Dim result As Boolean

Step 4

Create a sub procedure for adding the checkbox in the datagridview.

    Private Sub CheckboxColumn()
        'set a variable as a checkbox column in the DataGridView
        Dim chkbox As New DataGridViewCheckBoxColumn
        'set the width of the column in the DataGridView
        With chkbox
            .Width = 30
        End With
 
        With DataGridView1
            'Adding the checkbox column in the DataGridView
            .Columns.Add(chkbox)
            'set the rows header to invisible
            .RowHeadersVisible = False
        End With
    End Sub

Step 5

Create a sub procedure for retrieving data in the database to display in the datagridview.

    Private Sub RetrieveData()
        Try
            'opening connection
            con.Open()
            sql = "Select * From tblperson"
            'initialize the new instance of the OleDbCommand class
            cmd = New OleDb.OleDbCommand
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            'initialize the new instance of the OleDbDataAdapter class
            da = New OleDb.OleDbDataAdapter
            'set a stored procedure use to select record in the data source
            da.SelectCommand = cmd
            'initialize the new instance of the Datable class with no arguments
            dt = New DataTable
            'fill the rows in a specified range in the dataset 
            'to match those in the data source the data Table name.
            da.Fill(dt)
 
            'adding a checkbox column in the datagridview
            CheckboxColumn()
            'display the data from the database to the datagridview 
            DataGridView1.DataSource = dt
 
        Catch ex As Exception
            'fetching errors
            MsgBox(ex.Message)
        Finally
            'closing connection
            con.Close()
            'release all resources use by the system model
            da.Dispose()
        End Try
    End Sub

Step 6

Create a function for deleting data in the database.

    Function DeletingData(ByVal sql As String)
        Try
            'opening the connection
            con.Open()
            'initialize the new instance of the OleDbCommand class
            cmd = New OleDb.OleDbCommand
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            'Execute the Data
            result = cmd.ExecuteNonQuery
 
            If result = 0 Then
                result = 0
            Else
                result = 1
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            'Close the connection
            con.Close()
        End Try
        Return result
    End Function

Step 7

Go back to the design view and double click the form. After that, do the following codes for displaying data in the datagridview.

 'call the sub procedure to retrieve data in the first load of the form
  RetrieveData()

Step 8

Double click the button to fire the click event handler of it and do the following code for deleting data when the button is clicked.

        For Each row As DataGridViewRow In DataGridView1.Rows
            'Cells is to get the collection of cell that populate the row
            'FormattedValue is to get the value of the cell as formtted for display
            'the condition is, if the checkbox is checked then it delete the data in the database
            If row.Cells(0).FormattedValue = True Then
                'DataGridViewRow represents a row in the DataGridView Control
                'Rows is to get the collection of rows in the DataGridView control
                'the syntax is , getting the rows one by one
                'store your delete query to a variable(sql)
                sql = "DELETE * FROM tblperson WHERE ID = " & CStr(row.Cells(1).FormattedValue)
                DeletingData(sql)
            End If
        Next
        'the condition is, if the result is equals to zero
        'then the message will appear and says "No Deleted Record."
        'and if not the message will appear and says "The Record(s) has been deleted."
        If result = 0 Then
            MsgBox("No Deleted Record.")
        Else
            MsgBox("The Record(s) has been deleted.")
        End If
        'call the SubName for retrieving data to resfresh the list of Records in the DataGridView
        RetrieveData()

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.