How to Delete Selected Rows from DataGridView and MS-Access Database in VB.Net

Описание: Learn how to effectively delete selected rows from a DataGridView and ensure these changes reflect in your MS-Access database using VB.Net in your WinForms application.
---
This video is based on the question https://stackoverflow.com/q/62502653/ asked by the user 'johnny sterckx' ( https://stackoverflow.com/u/8751690/ ) and on the answer https://stackoverflow.com/a/62503658/ provided by the user 'GME' ( https://stackoverflow.com/u/4742975/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Delete selected row from DataGridView and MS-Access Database

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deleting Selected Rows from DataGridView and MS-Access Database: A Step-by-Step Guide

When building applications with VB.Net, one common requirement developers face is managing data, which often includes the need to delete selected rows from a DataGridView. If these rows are linked to an underlying database, like MS-Access, it's critical that these deletions are synchronized. This guide will walk you through the process of effectively deleting rows from both the DataGridView and the database, ensuring your application stays up to date.

The Problem

Imagine you have a DataGridView displaying user information, and you want to delete a selected user from both the DataGridView and the corresponding record in an MS-Access database. The challenge lies in managing the connection to the database, executing the deletion command while ensuring that your app doesn’t throw runtime errors, and updating the DataGridView accordingly.

Current Approach

Here's a brief description of the existing method you'd use to handle deletions:

Check Connection State: Ensure the database connection is open.

Gather Selected Rows: Get the selected row(s) from the DataGridView.

Execute Delete Command: Use SQL commands to delete the data from the database based on row selection.

Confirm Deletion: Prompt the user for confirmation before deleting.

Close Connection: Always remember to close the database connection.

While this approach works, it can be improved for better performance and error handling.

The Improved Solution

Let’s enhance your existing code to include proper resource management and clearer operations. Here’s how we can do it step-by-step:

1. Use of Using Statement for Better Resource Management

Instead of manually managing the connection state, we’ll utilize the Using statement. This ensures that your connection is automatically closed and disposed of correctly, even if an error occurs.

[[See Video to Reveal this Text or Code Snippet]]

2. Iterating Over Selected Rows

For each selected row, we’ll construct a command to delete that entry from the database. It’s important to use parameters to prevent SQL injection and ensure data integrity. Here's how to implement this:

[[See Video to Reveal this Text or Code Snippet]]

3. Clearing and Refreshing DataGridView

After the deletion process, clear the DataGridView’s data source to ensure it doesn’t display stale data. You can do this simply by setting the DataSource to Nothing:

[[See Video to Reveal this Text or Code Snippet]]

4. Refill the DataGridView

Once the DataGridView is cleared, re-fetch the data from the database to reflect the current state following the deletions. This could involve calling a method that retrieves and binds the updated data back to the DataGridView.

[[See Video to Reveal this Text or Code Snippet]]

Complete Code Example

Here’s the complete code snippet incorporating all the improvements discussed:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following the structured steps outlined in this guide, you can efficiently manage deletions from your DataGridView while ensuring your MS-Access database reflects those changes immediately. Using proper resource management with Using statements enhances the reliability of your application. Implement these strategies in your VB.Net projects to improve overall performance and user experience.