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

Submitted by: 
Visitors have accessed this post 612 times.

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.

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

Step 4

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

  1. Private Sub CheckboxColumn()
  2. 'set a variable as a checkbox column in the DataGridView
  3. Dim chkbox As New DataGridViewCheckBoxColumn
  4. 'set the width of the column in the DataGridView
  5. With chkbox
  6. .Width = 30
  7. End With
  8.  
  9. With DataGridView1
  10. 'Adding the checkbox column in the DataGridView
  11. .Columns.Add(chkbox)
  12. 'set the rows header to invisible
  13. .RowHeadersVisible = False
  14. End With
  15. End Sub

Step 5

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

  1. Private Sub RetrieveData()
  2. Try
  3. 'opening connection
  4. con.Open()
  5. sql = "Select * From tblperson"
  6. 'initialize the new instance of the OleDbCommand class
  7. cmd = New OleDb.OleDbCommand
  8. With cmd
  9. .Connection = con
  10. .CommandText = sql
  11. End With
  12. 'initialize the new instance of the OleDbDataAdapter class
  13. da = New OleDb.OleDbDataAdapter
  14. 'set a stored procedure use to select record in the data source
  15. da.SelectCommand = cmd
  16. 'initialize the new instance of the Datable class with no arguments
  17. dt = New DataTable
  18. 'fill the rows in a specified range in the dataset
  19. 'to match those in the data source the data Table name.
  20. da.Fill(dt)
  21.  
  22. 'adding a checkbox column in the datagridview
  23. CheckboxColumn()
  24. 'display the data from the database to the datagridview
  25. DataGridView1.DataSource = dt
  26.  
  27. Catch ex As Exception
  28. 'fetching errors
  29. MsgBox(ex.Message)
  30. Finally
  31. 'closing connection
  32. con.Close()
  33. 'release all resources use by the system model
  34. da.Dispose()
  35. End Try
  36. End Sub

Step 6

Create a function for deleting data in the database.

  1. Function DeletingData(ByVal sql As String)
  2. Try
  3. 'opening the connection
  4. con.Open()
  5. 'initialize the new instance of the OleDbCommand class
  6. cmd = New OleDb.OleDbCommand
  7. With cmd
  8. .Connection = con
  9. .CommandText = sql
  10. End With
  11. 'Execute the Data
  12. result = cmd.ExecuteNonQuery
  13.  
  14. If result = 0 Then
  15. result = 0
  16. Else
  17. result = 1
  18. End If
  19. Catch ex As Exception
  20. MsgBox(ex.Message)
  21. Finally
  22. 'Close the connection
  23. con.Close()
  24. End Try
  25. Return result
  26. 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.

  1. 'call the sub procedure to retrieve data in the first load of the form
  2. 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.

  1. For Each row As DataGridViewRow In DataGridView1.Rows
  2. 'Cells is to get the collection of cell that populate the row
  3. 'FormattedValue is to get the value of the cell as formtted for display
  4. 'the condition is, if the checkbox is checked then it delete the data in the database
  5. If row.Cells(0).FormattedValue = True Then
  6. 'DataGridViewRow represents a row in the DataGridView Control
  7. 'Rows is to get the collection of rows in the DataGridView control
  8. 'the syntax is , getting the rows one by one
  9. 'store your delete query to a variable(sql)
  10. sql = "DELETE * FROM tblperson WHERE ID = " & CStr(row.Cells(1).FormattedValue)
  11. DeletingData(sql)
  12. End If
  13. Next
  14. 'the condition is, if the result is equals to zero
  15. 'then the message will appear and says "No Deleted Record."
  16. 'and if not the message will appear and says "The Record(s) has been deleted."
  17. If result = 0 Then
  18. MsgBox("No Deleted Record.")
  19. Else
  20. MsgBox("The Record(s) has been deleted.")
  21. End If
  22. 'call the SubName for retrieving data to resfresh the list of Records in the DataGridView
  23. 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.


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.