Delete rows from datatable properly in .Net

Question

Basically, I have a MySQL DB I'm binding the data from one column of a table into a ListBox.. I want to allow users to add/edit/remove items and to be able to save/reject changes

For deletion, I want the user to be able to delete the selected item in the ListBox.. here's what I've tried so far

(1) dt.Rows(lst.SelectedIndex).Delete()

But this doesn't actually delete the row from the DataTable until updating it using DataAdapter.Update method, so the next index in the ListBox will refer to the deleted row in the DataTable.

(2): dt.Rows.RemoveAt(lst.SelectedIndex)

This works fine, but when updating the DataTable (after the user clicks save), the deleted rows aren't even deleted.

(3) Tried calling AcceptChanges:

    dt.Rows(lst.SelectedIndex).Delete()
    dt.AcceptChanges()

And I get the same result as the second one above.

Here's how I finally update the DataTable:

    Dim cmdBldr As New MySqlCommandBuilder(da)
    da.Update(dt)

So, is there a way to allow users to add/delete any number of rows to the DataTable BEFORE updating it?

If you call both ('.Delete()' and '.RemoveAt()') what happens? — Aimnox
I ended up using a BindingSource -thanks to jmcilhinney's answer-, but anyway what you're suggesting isn't a good idea because first of all it will delete two items from the listbox at once. — 41686d6564

jmcilhinney jmcilhinney · Accepted Answer · 2016-05-24T04:52:37

Bind the DataTable to a BindingSource (which you would add in the designer) and bind that to the ListBox. You would then delete the selected record by calling RemoveCurrent on the BindingSource. That will flag that row as Deleted and it will be excluded by the BindingSource.

DO NOT call AcceptChanges on anything. Doing so will indicate to the data adapter that there are no changes to save. Call Update on the data adapter to save the changes from the DataTable back to the database. That will call AcceptChanges internally after a successful save. When AcceptChanges is called, any rows marked Deleted are removed from the DataTable, while any rows marked Added or Modified are marked Unchanged.

Delete rows from datatable properly in .Net

1 Answers