How to Delete Selected Row in DatagridView and Ms Access

[Jommaria03]

I have here a code and could someone fix it?

I have here the code of the delete button.
How to delete the selected rows in datagridview and also be deleted in Ms access. The code here only deletes the record one by one starting from the first row not when selecting a row.
I don't know what code to add. please help...

Private Sub DeleteData()
    Dim conn As New OleDbConnection
    Dim sConnString As String
    Dim cmd As New OleDbCommand
    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable

    Try
        If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
        Else
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
        End If

        conn = New OleDbConnection(sConnString)
        conn.Open()
        cmd.Connection = conn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select id, image_description as description from tbl_image"
        da.SelectCommand = cmd

        da.Fill(dt)

        Me.DataGridView1.DataSource = dt

        dt.Rows(0).BeginEdit()
        dt.Rows(0).Delete()
        dt.Rows(0).EndEdit()

        Dim cb As New OleDbCommandBuilder(da)

        da.Update(dt)
        Me.DataGridView1.DataSource = dt.DefaultView
    Catch ex As Exception
        MsgBox(ErrorToString)
    Finally
        conn.Close()
    End Try
End Sub

 

[jmcilhinney]

Firstly, let's address some general issues with your code and then get to your specific request.

This:

If Microsoft.VisualBasic.Right(Application.StartupPat h, 1) = "\" Then
    sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "SampleDB.accdb;Persist Security Info=False;"
Else
    sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\SampleDB.accdb;Persist Security Info=False;"
End If

is silly code. Application.StartupPath is not going to include a slash in some cases and not in others. It will be the same every time. You don't need an If statement to be executed at run time. You can simply check whether it includes a slash or not and write the one appropriate line of code required. Even if it did change though, that still wouldn't matter because you could use Path.Combine to join two partial paths and it will take care of the slash for you, ensuring that there is one in the output whether one, neither or both inputs have slashes. That's still irrelevant though, because you shouldn't be using Application.StartupPath at all. This is what you should be using:

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\SampleDB.accdb;Persist Security Info=False;"

That will be resolved at run time to the program folder for a Windows app by default.

More to follow...

 

[jmcilhinney]

Next, what's up with this:

        Me.DataGridView1.DataSource = dt
 
        dt.Rows(0).BeginEdit()
        dt.Rows(0).Delete()
        dt.Rows(0).EndEdit()
 
        Dim cb As New OleDbCommandBuilder(da)
 
        da.Update(dt)
        Me.DataGridView1.DataSource = dt.DefaultView

You bind the grid, then make some changes and save them, then bind the grid again but to something different. What exactly are you trying to achieve there? Firstly, when you bind a DataTable, the data displayed actually comes from its DefaultView anyway, so there's never any point binding the DefaultView. You've already bound the grid to that DataTable though, so there's no point rebinding at all.

 

[jmcilhinney]

Getting closer to your issue now, the code you have is destined not to work because you're retrieving and saving in the one method. If the user has to make a selection then you have to have a break in between in order for them to do so, i.e. you must retrieve the data in one method and then save the changes in another. After the first method executes, the user can do whatever they need to do in the UI, then initiate a save, at which point you execute the second method. For an example of that, check out this thread of mine on data access:

Retrieving and Saving Data in Databases

 

[jmcilhinney]

Now, to address your request specifically, we first need to clarify whether you want to delete the current row (singular) or the selected rows (plural). The title and first post are at odds on that. Note that the current row is the row that contains the current cell, i.e. the cell that contains the caret. A selected row is one that is highlighted. There can be multiple selected rows but only ever one current row and the current row may or may not be selected.

Regardless of that, I would suggest that you bind your data via a BindingSource, which you can add in the designer:

myBindingSource.DataSource = myDataTable
myDataGridView.DataSource = myBindingSource

If you want to delete the current row then you will actually use the BindingSource to do so. Specifically, you would call its RemoveCurrent method. If you want to delete all the selected rows, however many that may be, then you would do so like this:

For Each gridRow As DataGridViewRow In myDataGridView.SelectedRows
    Dim tableRow = DirectCast(gridRow.DataBoundItem, DataRowView)

    tableRow.Delete()
Next

That will mark each underlying DataRow as Deleted and therefore remove them from the grid. You'll still need to commit those changes to the database by calling Update on a data adapter.