Deleting rows from a DataTable in a DataSet

[Guest]

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 Markic]

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

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?

 

[Miha Markic]

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

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.

 

[Guest]

Good explanation. Thanks

 

[Kevin Yu [MSFT]]

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."