Deleting specific rows from DataTable

I want to delete some rows from DataTable, but it gives an error like this,

Collection was modified; enumeration operation might not execute

I use for deleting this code,

foreach(DataRow dr in dtPerson.Rows){
    if(dr["name"].ToString()=="Joe")
        dr.Delete();
}

So, what is the problem and how to fix it? Which method do you advise?

Solution 1:

If you delete an item from a collection, that collection has been changed and you can't continue to enumerate through it.

Instead, use a For loop, such as:

for(int i = dtPerson.Rows.Count-1; i >= 0; i--)
{
    DataRow dr = dtPerson.Rows[i];
    if (dr["name"] == "Joe")
        dr.Delete();
}
dtPerson.AcceptChanges();

Note that you are iterating in reverse to avoid skipping a row after deleting the current index.

Solution 2:

Before everyone jumps on the 'You can't delete rows in an Enumeration' bandwagon, you need to first realize that DataTables are transactional, and do not technically purge changes until you call AcceptChanges()

If you are seeing this exception while calling Delete, you are already in a pending-changes data state. For instance, if you have just loaded from the database, calling Delete would throw an exception if you were inside a foreach loop.

BUT! BUT!

If you load rows from the database and call the function 'AcceptChanges()' you commit all of those pending changes to the DataTable. Now you can iterate through the list of rows calling Delete() without a care in the world, because it simply ear-marks the row for Deletion, but is not committed until you again call AcceptChanges()

I realize this response is a bit dated, but I had to deal with a similar issue recently and hopefully this saves some pain for a future developer working on 10-year-old code :)

P.s. Here is a simple code example added by Jeff:

YourDataTable.AcceptChanges(); 
foreach (DataRow row in YourDataTable.Rows) {
    // If this row is offensive then
    row.Delete();
} 
YourDataTable.AcceptChanges();

VB.Net

ds.Tables(0).AcceptChanges()
For Each row In ds.Tables(0).Rows
    ds.Tables(0).Rows(counter).Delete()
    counter += 1
Next
ds.Tables(0).AcceptChanges()

Solution 3:

with this solution:

for(int i = dtPerson.Rows.Count-1; i >= 0; i--) 
{ 
    DataRow dr = dtPerson.Rows[i]; 
    if (dr["name"] == "Joe")
        dr.Delete();
}

if you are going to use the datatable after deleting the row, you will get an error. So what you can do is: replace dr.Delete(); with dtPerson.Rows.Remove(dr);

Solution 4:

This works for me,

List<string> lstRemoveColumns = new List<string>() { "ColValue1", "ColVal2", "ColValue3", "ColValue4" };
List<DataRow> rowsToDelete = new List<DataRow>();

foreach (DataRow row in dt.Rows) {
    if (lstRemoveColumns.Contains(row["ColumnName"].ToString())) {
        rowsToDelete.Add(row);
    }
}

foreach (DataRow row in rowsToDelete) {
    dt.Rows.Remove(row);
}

dt.AcceptChanges();

Solution 5:

DataRow[] dtr=dtPerson.select("name=Joe");
foreach(var drow in dtr)
{
   drow.delete();
}
dtperson.AcceptChanges();

I hope it will help you