How to delete selected row from Datagridview and Access DB in .NET

Question

I am trying to delete selected row from datagridview and commit changes permanently in my access DB here is my code:

     cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accountcode.accdb;"
    cn.Open()

    Try
        For Each row As DataGridViewRow In DataGridView1.SelectedRows
            Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accountcode.accdb;")
                Using cm As New OleDb.OleDbCommand
                    cm.Connection = cn
                    cm.CommandText = "DELETE * FROM CheckDJ WHERE [ID]= @id"
                    cm.CommandType = CommandType.Text
                    cm.Parameters.AddWithValue("@ID", CType(row.DataBoundItem, DataRowView).Row("ID"))
                    cm.ExecuteNonQuery()
                End Using
            End Using
        Next
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    Try
        Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accountcode.accdb;")
            Using cm As New OleDb.OleDbCommand
                strsql = "SELECT * FROM CheckDJ"
                cm.Connection = cn
                cm.CommandText = strsql
                cm.CommandType = CommandType.Text

                Dim da As New OleDbDataAdapter(strsql, cn)
                Dim ds As New DataSet()
                da.Fill(ds, "CheckDJ")
                DataGridView1.DataSource = ds.Tables(0)
            End Using
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

But I'm having an error.
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index Any help is very much appreciated. Thank you in advance!

It's because you are removing rows while iterating them. Throw them in a collection first, then loop through each one and remove it from the datagridview and then remove it from the database. Currently your in a loop to remove records from datagridview but you are requiring the database and filling the datagridview again inside this loop, that is bad practice and asking for problems... Also use parameters and look into properly disposing connection objects, commands etc...Open connection do your work and the close and cleanup. — Trevor
– Trevor, Commented Jul 20, 2016 at 3:30
@Zaggler I would appreciate it very much if you can help me by showing me the correct code. You can modify my code. — Mai.V
– Mai.V, Commented Jul 20, 2016 at 3:38

F0r3v3r-A-N00b · Accepted Answer · 2016-07-20 05:53:50Z

1

Try
        For Each row As DataGridViewRow In DatagridView1.SelectedRows
            Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accountcode.accdb;")
                Using cm As New OleDb.OleDbCommand
                    cm.Connection = cn
                    cm.CommandText = "DELETE * FROM CheckDJ WHERE [ID]= @id"
                    cm.CommandType = CommandType.Text
                cm.Parameters.AddWithValue("@id", ctype(row.DataBoundItem,DataRowView).Row("id"))
                    cm.ExecuteNonQuery()
                End Using
            End Using
        Next
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    Try
        Using cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Accountcode.accdb;")
            Using cm As New OleDb.OleDbCommand
                    strsql = "SELECT * FROM CheckDJ"
                    cm.Connection = cn
                    cm.CommandText = strsql
                    cm.CommandType = CommandType.Text

        Dim da As New OleDbDataAdapter(strsql, cn)
        Dim ds As New DataSet()
        da.Fill(ds, "CheckDJ")
        DataGridView1.DataSource = ds.Tables(0)
           End Using
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

edited Jul 20, 2016 at 5:53

answered Jul 20, 2016 at 4:00

F0r3v3r-A-N00b

3,0234 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mai.V Over a year ago

@F0r3v3r-A-N00b : Object reference not set to an instance of an object. I got this error trying your code.

Mai.V Over a year ago

@F0r3v3r-A-N00b This line: cm.Parameters.AddWithValue("@id", ctype(row.DataBoundItem,DataRowView).Row("id"))

F0r3v3r-A-N00b Over a year ago

change the "id" in Row("id") to the actual name of your column

jmcilhinney · Accepted Answer · 2016-07-20 03:47:29Z

0

You're going about that all wrong. Firstly, use a data adapter to populate a DataTable and bind that to the grid. You would then get the bound DataRowView from each selected row in the grid, call its Delete method to mark it as Deleted (which will remove it from the grid) and then use the same data adapter to save the changes back to the database. E.g.

myDataAdapter.Fill(myDataTable)
myDataGridView.DataSource = myDataTable

'...

Dim rowsToDelete As New List(Of DataRowView)

For Each gridRow As DataGridViewRow In myDataGridView.SelectedRows
    rowsToDelete.Add(DirectCast(gridRow.DataBoundItem, DataRowView))
Next

For Each row In rowsToDelete
    row.Delete()
Next

myDataAdapter.Update(myDataTable)

answered Jul 20, 2016 at 3:47

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

1 Comment

jmcilhinney Over a year ago

I wondered about the DV myself. I do wish such people would leave a comment. If there's something that needs changing then we're not going to change it if we don't know what it is.

Collectives™ on Stack Overflow

How to delete selected row from Datagridview and Access DB in .NET

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related