How to delete row when u loop on datatable

Question

For Each Dr As DataRow In InvoiceDT.Rows Dim DrResult As Array = PaymentInvoiceDT.Select("Amount='" & Dr("Amount").ToString() & "'") If DrResult.Length > 0 Then ''some code Else InvoiceDT.Rows.remove(Dr) End If Next 

It gives an error because when you change something in the datatable, its index has changed.

Answer 1

You cannot do this in a For Each loop, because when you delete something, the collection has changed, and you can no longer list it.

You need a reverse For loop.

 For i as Integer = Invoice.Rows.Count -1 to 0 Step -1 Dim DrResult As Array = PaymentInvoiceDT.Select("Amount='" & Invoice.Rows(i).("Amount").ToString() & "'") If DrResult.Length > 0 Then 'some code Else InvoiceDT.Rows.remove(InvoiceDT.Rows(i)) End If Next 

This will work even when deleting rows, because those that you do not touch do not change their indexes or use an enumeration.

Answer 2

Sai, it fails because you do not have to delete rows during the loop in all rows of the table.

An example of what you could do in C # is:

  DataTable dt = CreateDataSource(); DataRow[] rows = (from t in dt.AsEnumerable().Cast<DataRow>() where t.Field<int>("ID") == 1 select t).ToArray(); foreach (DataRow row in rows) { dt.Rows.Remove(row); } 

since you see that first you select all the rows to delete using LINQ, then you only loop through the resulting rows and delete them from the original table.

Sorry, there is no time to write this in VB.NET, but the idea should be clear, I hope.

Answer 3

My problem was that I needed to loop the table several times, so after deleting rows in the first round, it throws an exception when moving to the index of a previously deleted row. I ended up cloning the table and copying the rows that I wanted to save into it.

 Dim NewInvoiceDT as DataTable = InvoiceDT.clone For Each Dr As DataRow In InvoiceDT.Rows If 'some statement Dim NewDr as DataRow = NewInvoiceDT.Rows.Add NewDr.ItemArray = Dr.ItemArray End if Next 

Answer 4

Can't be deleted using a simple DELETE ?

DELETE FROM xxx WHERE Amount = yyy