Update and Delete Rows in a DataTable


One guy asked me a question, he was having problems in updating and deleting rows when looping through a DataTable rows.

 

A typical scenario is, say you have to loop through a DataTable rows and do some calculations on the rows and delete some rows depending on the condition. For example, deleting rows with Name = Mahesh.

 

If you do like in the following code, you will get an error on AcceptChanges method and the reason is deleting rows inside the loop means affecting the indexing.

 

' Loop though the table

 For Each dr As DataRow In ds.Tables(0).Rows

      If (dr("Name") = "Mahesh" Then

            dr.Delete()

      End If

ds.Tables(0).AcceptChanges()

Next

       

However, if you mark the rows as delete using Delete method and accept changes on the DataTable outside the loop, you should be good to go. See the following code:

 

' Loop though the table

 For Each dr As DataRow In ds.Tables(0).Rows

      If (dr("Name") = "Mahesh" Then

            dr.Delete()

      End If

Next

ds.Tables(0).AcceptChanges()