Deleting rows from a DataTable in a DataSet

I have a typed DataSet with a DataTable in it(created by using the "Generate DataSet" of the DataAdapter). Sometimes, I want to delete all of the records in the DataTable and I use the following snippet:

Dim dr As DataRow
For Each dr In tbTable.Rows
dr.Delete()
Next

I have found that if I don't make a call to the DataAdapter's .Update function before I call the above snippet, I get the following error message:

"Collection was modified; Enumeration operation may not execute"

I really should be able to delete these records independently of what's on the SQL server. Why will it work only after an Update?

Michael

Miha
Thu Jan 22 14:03:15 CST 2004

Hi Michael,

Instead of foreach do a inverse loop:
For i = tbTable.Rows.Count-1 to 0 step -1
dr.Delete()
Next

This is so, because Delete on Added row removes row from collectoin - which
is forbidden within for each loop.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Michael" <michael@hockstein.org> wrote in message
news:CE65974E-673E-4D4C-B6CC-2BFCE76D0EB8@microsoft.com...
> I have a typed DataSet with a DataTable in it(created by using the
"Generate DataSet" of the DataAdapter). Sometimes, I want to delete all of
the records in the DataTable and I use the following snippet:
>
> Dim dr As DataRow
> For Each dr In tbTable.Rows
> dr.Delete()
> Next
>
> I have found that if I don't make a call to the DataAdapter's .Update
function before I call the above snippet, I get the following error message:
>
> "Collection was modified; Enumeration operation may not execute"
>
> I really should be able to delete these records independently of what's on
the SQL server. Why will it work only after an Update?
>
> Michael

Miha
Thu Jan 22 14:49:48 CST 2004

Hi Michael,

This is a limitation of foreach loop - you can't change the iteration
source.
If the row is added and you call Delete then the row is actually removed
from Rows, while if the row has other status then it is just flaged for
Deletion (and not removed - thus the Rows collection doesn't change).

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Michael" <michael@hockstein.org> wrote in message
news:F0E38E34-B4CC-419C-8B3A-3BF0F48AE1A2@microsoft.com...
> OK, you figured it out twice for me today. The Delete line is actually
tbTable.Rows.Item(i).Delete().
>
> I'm unclear on the reason why though. If I understood correctly, from
within a For Each loop, you cannot Delete a row from a collection that has
been Added(but can you delete a row which was already there, as in the case
after an Update). However, from within a For statement, where you're not
directly working with the collection, you can Delete any row.
>
> If this is so, could you expand the explanation a bit more or point me in
the direction of the explanation.
>
> Thanks.
>
> Michael
>
>
> ----- Miha Markic wrote: -----
>
> Hi Michael,
>
> Instead of foreach do a inverse loop:
> For i = tbTable.Rows.Count-1 to 0 step -1
> dr.Delete()
> Next
>
> This is so, because Delete on Added row removes row from collectoin -
which
> is forbidden within for each loop.
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "Michael" <michael@hockstein.org> wrote in message
> news:CE65974E-673E-4D4C-B6CC-2BFCE76D0EB8@microsoft.com...
> > I have a typed DataSet with a DataTable in it(created by using the
> "Generate DataSet" of the DataAdapter). Sometimes, I want to delete
all of
> the records in the DataTable and I use the following snippet:
> >> Dim dr As DataRow
> > For Each dr In tbTable.Rows
> > dr.Delete()
> > Next
> >> I have found that if I don't make a call to the DataAdapter's
.Update
> function before I call the above snippet, I get the following error
message:
> >> "Collection was modified; Enumeration operation may not execute"
> >> I really should be able to delete these records independently of
what's on
> the SQL server. Why will it work only after an Update?
> >> Michael
>
>
>

michael
Thu Jan 22 15:01:18 CST 2004

Good explanation. Thanks

v-kevy
Thu Jan 22 19:43:48 CST 2004

Hi Mike,

Thank you for posting in the community! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to delete all the rows in a
DataTable in a For Each loop, but it throws an exception. If there's any
misunderstanding, please feel free to point me out.

Just as Miha said, the For Each loop cannot change the source collection.
This is by design. However, if you want to clear the contents, you can also
use DataTable.Clear() method to remove all the rows in it. For more
information about DataTable.Clear() please refer to the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatatableclasscleartopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."