Deleting Multiple Rows From a DataView

[Daryll Shatz]

What is the proper way to delete multiple rows of data in a dataview without
using the table's primary key?

This does not seem to work:

Dim dv = New DataView(DataSet11.table)
dv.RowFilter = "Column=" & searchvalue

For Each drv In dv
drv.Delete()
Next

dataadapter.Update(DataSet11.table)

I get an "no record at position X" error on the last row deletion.

Thanks for any suggestions.

 

[IbrahimMalluf]

Why not just run a delete query against the DB then fill the dataset with
the current DB

 

[Jay B. Harlow [MVP - Outlook]]

Daryll,

For Each drv In dv
drv.Delete()
Next

The above will not work as you expect, as you are iterating over a
collection that you are modifying, each drv.Delete is going to reduce by 1
the number of items in your DataView. Specifically you are modify the
RowState for each row from Current (which is the default for the DataView)
to Deleted. Using For Each over a collection that you are implicitly or
explicitly modifying never really works as you expect. In fact a number of
collections actually throw an exception right away.

I would suggest you use either the DataTable.Select method to get a 'fixed'
collection of rows that you then delete each row, or you use a While loop &
DataView, to delete each row.

Something like:

Dim dv = New DataView(DataSet11.table)
dv.RowFilter = "Column=" & searchvalue

Do While dv.Count > 0
dv(0).Delete() ' delete one row
Loop

or

For Each row As DataRow In DataSet11.table.Select("Column=" &
searchvalue)
row.Delete()
Next

Hope this helps
Jay

 

[Tom Gross]

Hi Jay,

I'm having much the same problem, but I'm not sure if either of your
solutions would work for me in my case.

I have a for loop iterating through a dataview:

for (int i = 0; i < EventCount; i++)
{
row = EventsDataView;
EventID = row["EventID"];
if (eventisGood(EventID))
markEventForDeletion(EventID);
}

void markEventForDeletion(int EventID)
{
DataRow row = TagEventsDataSet.Tables["TagEvents"].Rows.Find(EventID);

row.Delete();
}

what I find is that my program crashes half-way through the dataview
(if there are 70 rows in the dataview it will crash at 35) so I can
see that I am actually deleting data underneath myself.

Incidentally, this isn't at all how I would expect ADO.NET to work,
based on what I have read (for example, page 241 of David Sceppa's
book Microsoft ADO.NET). The way I read it, *removing* a datarow
would actually remove it from the table, but *deleting* just flags it
as a pending deletion (if things worked the way I would expect, the
rows would not be deleted until the AcceptChanges method was called,
so now I'm wondering what AcceptChanges is for).

Again, I don't want to delete every row in my datatable, only some of
them. How am I supposed to iterate through the rows?

 

[Jay B. Harlow [MVP - Outlook]]

Tom
You are having the same problem, I would recommend you code the loop the
same way as I demonstrated.

Remember if you delete row 1, row 2 is now row 1, as row 1 was removed from
the collection. However you incremented "i" so you are looking at what was
row 3 (the new row 2).

The other method to use is to start at the end of the collection and work
forward. (decrement "i" instead of incrementing it).

Hope this helps
Jay

 

[Mark Johnson]

For a DataView this should be explaned al little bit differntly:

If you change a DataRow in a way that no longer fullfills the condition used
for creating the DataView and use .AcceptChanges() or .EndEdit() the
DataView will react immediately.

this will not work :
for (int i=0;i<dview_UserMarkiert.Count;i++) // = 5
{// Delete DataRow in Database
dview_UserMarkiert.Row.Delete();
dview_UserMarkiert.Table.AcceptChanges();
// dview_UserMarkiert.Count = is now 4, 3,boom
// Delete DataRow in Database
} // for (int i=0;i<dview_UserMarkiert.Count;i++)
// dview_UserMarkiert.Count = 0

this will work :

for (int i=0;i<dview_UserMarkiert.Count;i++) // = 5
{// Delete DataRow in Database
dview_UserMarkiert.Row.Delete();
// Delete DataRow in Database
} // for (int i=0;i<dview_UserMarkiert.Count;i++)
// dview_UserMarkiert.Count = 5
dview_UserMarkiert.Table.AcceptChanges();
// dview_UserMarkiert.Count = 0

The same thing happens when you Update a field that no longer fullfills the
condition of the View.

I ran into this today and spent a lot time wondering what was goining on.

It is nice that created View is allwys updated to changed DataSet/Table
condition, but was unexpected.

A warnig in the Documentation would be usefull.

Hope this helps

Mark Johnson, Berlin Germnay

(e-mail address removed)

Tom
You are having the same problem, I would recommend you code the loop the
same way as I demonstrated.

Remember if you delete row 1, row 2 is now row 1, as row 1 was removed from
the collection. However you incremented "i" so you are looking at what was
row 3 (the new row 2).

The other method to use is to start at the end of the collection and work
forward. (decrement "i" instead of incrementing it).

Hope this helps
Jay

Hi Jay,

I'm having much the same problem, but I'm not sure if either of your
solutions would work for me in my case.

I have a for loop iterating through a dataview:

for (int i = 0; i < EventCount; i++)
{
row = EventsDataView;
EventID = row["EventID"];
if (eventisGood(EventID))
markEventForDeletion(EventID);
}

void markEventForDeletion(int EventID)
{
DataRow row = TagEventsDataSet.Tables["TagEvents"].Rows.Find(EventID);

row.Delete();
}

what I find is that my program crashes half-way through the dataview
(if there are 70 rows in the dataview it will crash at 35) so I can
see that I am actually deleting data underneath myself.

Incidentally, this isn't at all how I would expect ADO.NET to work,
based on what I have read (for example, page 241 of David Sceppa's
book Microsoft ADO.NET). The way I read it, *removing* a datarow
would actually remove it from the table, but *deleting* just flags it
as a pending deletion (if things worked the way I would expect, the
rows would not be deleted until the AcceptChanges method was called,
so now I'm wondering what AcceptChanges is for).

Again, I don't want to delete every row in my datatable, only some of
them. How am I supposed to iterate through the rows?

 

[Jay B. Harlow [MVP - Outlook]]

Mark,
I do not believe AcceptChanges have anything to do with it per se, you are
correct it has to do with "DataRow ... fullfills the condition used for
creating the view".

The DataView.RowStateFilter clause is one of the "conditions used for
creating the view", which will determine if your second example works or
not. The default for RowStateFilter is CurrentRows, which excludes Deleted
rows.

Which means your second example should not work either, as the Delete itself
changes the condition used for creating the view in most cases. The default
condition excludes deleted rows, which means as soon as you execute
Row.Delete, that row will be excluded which means the DataView changed.

Hence its better to use the loop I originally gave, of course changing the
RowStateFilter is an alternative method.

Hope this helps
Jay

For a DataView this should be explaned al little bit differntly:

If you change a DataRow in a way that no longer fullfills the condition used
for creating the DataView and use .AcceptChanges() or .EndEdit() the
DataView will react immediately.

this will not work :
for (int i=0;i<dview_UserMarkiert.Count;i++) // = 5
{// Delete DataRow in Database
dview_UserMarkiert.Row.Delete();
dview_UserMarkiert.Table.AcceptChanges();
// dview_UserMarkiert.Count = is now 4, 3,boom
// Delete DataRow in Database
} // for (int i=0;i<dview_UserMarkiert.Count;i++)
// dview_UserMarkiert.Count = 0

this will work :

for (int i=0;i<dview_UserMarkiert.Count;i++) // = 5
{// Delete DataRow in Database
dview_UserMarkiert.Row.Delete();
// Delete DataRow in Database
} // for (int i=0;i<dview_UserMarkiert.Count;i++)
// dview_UserMarkiert.Count = 5
dview_UserMarkiert.Table.AcceptChanges();
// dview_UserMarkiert.Count = 0

The same thing happens when you Update a field that no longer fullfills the
condition of the View.

I ran into this today and spent a lot time wondering what was goining on.

It is nice that created View is allwys updated to changed DataSet/Table
condition, but was unexpected.

A warnig in the Documentation would be usefull.

Hope this helps

Mark Johnson, Berlin Germnay

(e-mail address removed)

Tom
You are having the same problem, I would recommend you code the loop the
same way as I demonstrated.

Remember if you delete row 1, row 2 is now row 1, as row 1 was removed from
the collection. However you incremented "i" so you are looking at what was
row 3 (the new row 2).

The other method to use is to start at the end of the collection and work
forward. (decrement "i" instead of incrementing it).

Hope this helps
Jay

Hi Jay,

I'm having much the same problem, but I'm not sure if either of your
solutions would work for me in my case.

I have a for loop iterating through a dataview:

for (int i = 0; i < EventCount; i++)
{
row = EventsDataView;
EventID = row["EventID"];
if (eventisGood(EventID))
markEventForDeletion(EventID);
}

void markEventForDeletion(int EventID)
{
DataRow row = TagEventsDataSet.Tables["TagEvents"].Rows.Find(EventID);

row.Delete();
}

what I find is that my program crashes half-way through the dataview
(if there are 70 rows in the dataview it will crash at 35) so I can
see that I am actually deleting data underneath myself.

Incidentally, this isn't at all how I would expect ADO.NET to work,
based on what I have read (for example, page 241 of David Sceppa's
book Microsoft ADO.NET). The way I read it, *removing* a datarow
would actually remove it from the table, but *deleting* just flags it
as a pending deletion (if things worked the way I would expect, the
rows would not be deleted until the AcceptChanges method was called,
so now I'm wondering what AcceptChanges is for).

Again, I don't want to delete every row in my datatable, only some of
them. How am I supposed to iterate through the rows?

 

[Tom Gross]

thanks Jay, I understand exactly what's going on... I don't think I
mentioned that I am sorting the events in the view by time, but thanks
for the suggestion to decrement through the view - I think that would
work if I sorted events by time descending in the view. as it is I
implemented a separate array of flags for each event and then deleted
events from the eventtable itself based on the flag. that works, but
I like your suggestion better.

 

[Terry Hornsby]

As another solution, wouldn't it be possible to step backward through the
collection, as you could possibly do in vba?

Ie,
Dim drv = New DataView(DataSet11.table)
drv.RowFilter = "Column=" & searchvalue

for i = drv.count to 1 step -1
drv(i).Delete()
next i

Terry.

___________________

 

[Jay B. Harlow [MVP - Outlook]]

Terry,
Yes you can step backwards, however remember that in .NET collections are
zero based, not 1 based.

Hope this helps
Jay

 

[Terry Hornsby]

Some vba collections are also zero based. It was an oversight on my part
(mostly because my recent code has relied on declaring Option Base 1
statements).

Many thanks for confirming this is possible.

Terry.