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 is giving error because when you changed something in datatable, its index get changed.
You won't be able to do this in a For Each loop because when you remove something, the collection has changed and you can't enumerate it anymore.
What you need is a reversed 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 still work even as you remove rows because the ones you're not touching aren't having their indexes changed and it doesn't use an enumeration.
InvoiceDT.Rows.RemoveAt(i) in the Else clause.Sai, it fails because you should not really delete the rows while looping in all table rows.
an example of what you could do, in C# is this one:
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);
}
so as you see you first select all rows to delete with LINQ then you loop only on the resulting rows and remove them from the source table.
sorry no time to write this in VB.NET but the idea should be clear I hope.
My problem was that I needed to loop a table multiple times, so after deleting rows in the first round it would throw an exception when coming to the index of a previously deleted row. I ended up cloning the table and copying into it the rows I wanted to keep.
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
To iterate through a DataTable and remove rows conditionally (eg: row having column_id as 16), you can use the following code:
Dim iterateIndex As Integer = 0
Dim newDataTable As DataTable = originalDataTable.Copy
For Each row As DataRow In newDataTable.Rows
If row("column_id") = 16 Then
originalDataTable.Rows.RemoveAt(iterateIndex)
Else
iterateIndex += 1
End If
Next
It's simple
For i = 0 To dt.Rows.Count - 1
'DO .... then delete....
dt.Rows(0).Delete()
Next
Can you not delete using a simple DELETE statement?
DELETE FROM xxx WHERE Amount = yyy
