You are already familiar with the database operations in VB.Net tutorial series. As we have completed the insert, retrieve and update operation with the database records. For retrieving and updating of database records, we have used DataGridView control. The DataGridView is a GUI control in the Visual Studio IDE which provides the tabular form to manage and display the records. This is an easy approach to control the operation of the data which are coming from the database. In our last tutorial, we have seen that how to update database record using the DataGridView. Today, in this tutorial we will learn how to delete database record using the DataGridView. This is the fourth part of the Database operation in the Visual Basic tutorial. In this tutorial, we will continue to our last project which we have created to perform the search, and update operation.
Delete Database Record Using DataGridView
Here, we will open the existing project to delete database record which is already created in the previous tutorial. Apart from the project, we will use the same database that is created in MS Access. You can change or create the new one if you need.
Open the Existing Project
We can open the existing project in two ways –
- Navigate to the Computer User -> Documents -> Visual Studio Folder (whatever version you have).
- Open the Projects folder and choose the project file.
- In my case, the project name is DataGridViewExample.
- Inside this project folder, you will see the Microsoft Visual Studio Solution File. Just double-click on that.
- It will load up the entire project files in the Visual Studio.
- Open the Visual Studio whatever version you have installed. Go to the Menu Bar option and click on the File ->Open -> Project/Solution
- Now, navigate to the Visual Studio project folder and select the solution file type of the Visual Studio project.
- It will open the complete project.
- In another way, if you didn’t create any project after that project in the visual studio. Then you will see the recent project file there. You can open it from there too.
Open the Existing Windows Form
- After the successful opening of the project files, just go to the Solution Explorer window and open the windows form which you have created previously.
- After the opening of the windows form, you will see the form design.
- Just double-click on the form to view the code.
- Start debugging the project and you will see the output.
- So, here this is the output of my last project. Here, you can see the existing records are displaying in the DataGridView.
- Now, we have to perform the deletion of the record here that will select in the DataGridView.
Delete Database Record
- For deletion of the record, here we will use the same DataGridView which we have used to update the records.
- So, for this, the deletion operation will perform after selecting the index row of the record.
- Once a row has selected the index value of that row will consider. Here the Student ID is the primary key. So, I used the Student ID as the condition to delete the record.
- That means the value of the selected row will create a condition of the first record which is Student ID.
- This is the code for the delete button.
Delete Button Code
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
'checking for the selected item in the list
If Me.DataGridView1.Rows.Count > 0 Then
If DataGridView1.SelectedRows.Count > 0 Then
Dim intStID As Integer = Me.DataGridView1.SelectedRows(0).Cells("Student ID").Value
If Not con.State = ConnectionState.Open Then
'deleting data from table
Dim cmd As New OleDb.OleDbCommand
cmd.Connection = con
If MessageBox.Show("Do you want to delete?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
cmd.CommandText = "DELETE FROM student WHERE st_id=" & intStID
MessageBox.Show("Successfully deleted", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
MessageBox.Show("Deletion aborted", "Aborted", MessageBoxButtons.OK, MessageBoxIcon.Warning)
MessageBox.Show("No record selected for delete.", "Deletion Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
MessageBox.Show("No record to be delete.", "Deletion Denied", MessageBoxButtons.OK, MessageBoxIcon.Error)
Explanation of the Above Code
- In the line no. 3, I have checked If the database table has any record or not.
- The line no. 4 is checking if the user has selected the row or not for the deletion.
- I’ve declared a variable of integer type in the line no. 5, and assigned the first cell value (Student ID) of the selected row.
- Then in the next from line no. 8-10, I’ve checked for the connection is opened. If any existing connection has opened then It will not open here. If not, then it will open the connection.
- In the next from line 13-14, I have created an object cmd for the OleDbCommand.
- After creating the object, I have assigned the connection.
- From the line 16-19, I’ve used the SQL query to delete the record from the database.
- If the record deletion has completed successfully, the message will display.
- In the line no. 28, I have called the method for refreshing the data after successful deletion.
- At last, in the line no. 31, I have closed the connection which we have opened before the SQL query is initiated.
- Here is the method of refreshing the data.
Code for the Method DisplayData()
Private Sub DisplayData()
If Not con.State = ConnectionState.Open Then
con.Open() 'opening the connection
Dim da As New OleDb.OleDbDataAdapter("SELECT st_id as [Student ID],st_name as [Student Name],course as [Course],gender as [Gender],dob as [Date of Birth],category as [Category],address as [Address],email as [Email],contact as [Contact],state as [State],country as [Country] FROM student ORDER BY st_id", con)
Dim dt As New DataTable
DataGridView1.DataSource = dt
'closing the connection
Catch ex As Exception
MessageBox.Show(ex.Message, "Exception occurred", MessageBoxButtons.OK, MessageBoxIcon.Error)
- Here is the output of this project.
- In the below result, I have selected the row by clicking the index of the particular row.
- Then click on the Delete button. After clicking on the Delete button, a message box will appear. The message box will have the option to confirm the deletion.
- So just click on the Yes button in the message box.
- Once you confirmed the deletion, the record will be deleted.
- After successful deletion of the record, a message box will appear with the message of Successfully deleted.
- Here, after the successful deletion of the record, the record is refreshed. You can see the selected record is deleted from the DataGridView.
- In another case, If you want to cancel the deletion of the record just click on the No option which is showing in the message box.
- If you didn’t confirm the record deletion, the record will not delete from the database.
- A message box will appear with the Deletion aborted.
- Once, the deletion is aborted you can see here no data is deleted from the DataGridView.
Bingo, we have successfully completed the Delete Database Record tutorial using DataGridView. If you have any doubt or query regarding any tutorials in the series, your comments are always welcome here. So that’s all about the Delete database record tutorial, in the next post I will come with some advance. Till enjoy this tutorial and practice it.
Thank you. 🙂