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
– Aimnox, Commented May 24, 2016 at 7:02
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
– 41686d6564, Commented May 24, 2016 at 8:34

jmcilhinney · Accepted Answer · 2016-05-24 04:52:37Z

3

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.

answered May 24, 2016 at 4:52

jmcilhinney

54.9k5 gold badges30 silver badges51 bronze badges

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

3 Comments

41686d6564 Over a year ago

Thank you, the BindingSource solved the issue.. but I'm wondering is there a way to do this without using an extra BindingSource (by binding the DataTable directly to the ListBox)?

jmcilhinney Over a year ago

Yes, but why would you want to? The BindingSource exists for a reason.

41686d6564 Over a year ago

It was just because I'm binding 6-8 tables, but I already used a binding source for each table. Thank you again :)

Collectives™ on Stack Overflow

Delete rows from datatable properly in .Net

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related