Deleting specific rows from DataTable

Question

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?

score 201 · Accepted Answer · edited Oct 16 '18 at 05:26

201

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.

edited Oct 16 '18 at 05:26

Umashankar

694
7
21

answered Apr 13 '11 at 11:25

Widor

13,003
7
42
64

@Slugster beat me to it! (I changed your `[ii]` to `[i]`, however :-) – Widor Apr 13 '11 at 11:36
11

This is incorrect. You _can_ use a foreach to loop through a table while deleting rows. See [answer by Steve](http://stackoverflow.com/a/13752739/441684). – Alexander Garden Sep 12 '13 at 20:13
3

This answer should also include @bokkie's answer. If we use the `DataTable` later, its gonna throw an exception. The correct way would be to call `Remove()` on the source `DataTable` - `dtPerson.Rows.Remove(dr)`. – Code.me May 01 '15 at 20:12
If you are using the DataTable to update a table in a database server, @Steve has a better answer. You can mark rows as deleted, update rows, and add new rows all in a single loop. You can use a SqlAdapter to commit the changes to the Db table. Considering how often the problem arises, the whole process is a lot more convoluted than you would think it should be, but it does work. If I wasn't going to take advantage of the DataTable's transactional nature, I'd just use an object collection and the namco or Widor approach. – B H Jun 23 '16 at 15:16
Doesn't using `Delete()` require a call to `AcceptChanges()` in order for the deletion to take effect? – Broots Waymb Mar 17 '17 at 17:40
@AlexanderGarden I just tried to use a foreach loop and got this error: `An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code Additional information: Collection was modified; enumeration operation might not execute.` I don't think that you can do it with foreach loops anymore. – Daryl Bennett Jun 07 '17 at 16:10
Using a foreach should be possible: "The actual removal occurs when the application calls the AcceptChanges method. By using Delete, you can programmatically check which rows are marked for deletion before actually removing them. When a row is marked for deletion, its RowState property is set to Delete." see https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/datarow-deletion – needfulthing Oct 29 '19 at 16:58
It should be dr["name"].ToString()=="Joe" if comparing with string – raw_hitt Jun 01 '21 at 02:49
What is the point of reverse loop when you are not actually removing but just marking for deletion? – variable Jul 30 '21 at 18:40

score 144 · Answer 2 · edited Feb 14 '18 at 13:33

144

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:

C#

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()

edited Feb 14 '18 at 13:33

KyleMit

30,350
66
462
664

answered Dec 06 '12 at 21:17

Steve

1,779
1
11
12

for c# version just need to use { and } instead of () – DotNetDublin Apr 22 '14 at 15:18
2

also more helpful (I think) to change `object row_loopVariable in ds.Tables(0).Rows` to `DataRow row in ds.Tables(0).Rows` – DotNetDublin Apr 22 '14 at 15:24
2

Ffs, this has saved me during a nightmare weekend deployment. You deserve all the beers! – James Love Jan 31 '15 at 15:13
See docs at https://msdn.microsoft.com/de-de/library/system.data.datarowcollection.remove(v=vs.110).aspx – Andreas Krohn Mar 02 '17 at 15:13
Nice code. One thing, in C# the typical way to increment by one is `counter++` instead of `counter+= 1`. – MQuiggGeorgia Sep 04 '17 at 18:18
Good answer , but there could be times when one doesn't want to call .AcceptChanges() – faheem khan Oct 17 '17 at 08:11
@faheemkhan Although you are correct, if you aren't calling AcceptChanges(), I feel you are using the DataTable improperly. True, you could use the accepted answer and never need to call AcceptChanges(), but unless there was a huge performance penalty, I think Looping Backwards through datasets is the more convoluted solution to avoid this error. – Steve Aug 02 '18 at 15:00
Also, note that you can delete a row using row.Delete() or by calling Remove() on the rows collection. For this answer to work, you must use Delete() or you will still get the error. – Mike Cheel Jun 11 '21 at 11:46

bokkie · Answer 3 · 2015-05-05T09:43:36.743

19

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);

edited May 05 '15 at 09:43

answered Oct 24 '12 at 05:31

bokkie

1,477
4
21
40

score 19 · Answer 4 · edited Mar 16 '16 at 06:26

19

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();

edited Mar 16 '16 at 06:26

Thilina Sampath

3,615
6
39
65

answered Feb 09 '12 at 07:28

Balaji Birajdar

2,394
1
23
29

1

so easy to miss dt.AcceptChanges() – Matthew Lock Oct 08 '15 at 03:43
"You can also call the Delete method of the DataRow class to just mark a row for removal. Calling Remove is the same as calling Delete and then calling AcceptChanges. Remove should not be called in a foreach loop while iterating through a DataRowCollection object.Remove modifies the state of the collection." See https://msdn.microsoft.com/de-de/library/system.data.datarowcollection.remove(v=vs.110).aspx Cheers. – Andreas Krohn Mar 02 '17 at 15:06

score 10 · Answer 5 · edited Aug 14 '22 at 03:11

10

DataRow[] dtr = dtPerson.Select("name=Joe"); //name is the column in the data table
foreach(var drow in dtr)
{
   drow.Delete();
}
dtperson.AcceptChanges();

edited Aug 14 '22 at 03:11

SuperStormer

4,997
5
25
35

answered Jun 16 '16 at 12:50

Karthik

101
1
3

1

the command is `drow.Delete();` not `drow.delete();` methods are case sensitive in .net btw – MethodMan Dec 29 '16 at 17:30

score 6 · Answer 6 · answered Jul 29 '15 at 13:53

6

To remove entire row from DataTable , do like this

DataTable dt = new DataTable();  //User DataTable
DataRow[] rows;
rows = dt.Select("UserName = 'KarthiK'");  //'UserName' is ColumnName
foreach (DataRow row in rows)
     dt.Rows.Remove(row);

answered Jul 29 '15 at 13:53

Karthikeyan P

1,216
1
20
23

score 4 · Answer 7 · answered Mar 20 '13 at 16:50

4

Or just convert a DataTable Row collection to a list:

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

answered Mar 20 '13 at 16:50

Milos

578
6
6

Arun Prasad E S · Answer 8 · 2017-01-26T15:49:59.540

<asp:GridView ID="grd_item_list" runat="server" AutoGenerateColumns="false" Width="100%" CssClass="table table-bordered table-hover" OnRowCommand="grd_item_list_RowCommand">
    <Columns>
        <asp:TemplateField HeaderText="No">
            <ItemTemplate>
                <%# Container.DataItemIndex + 1 %>
            </ItemTemplate>
        </asp:TemplateField>            
        <asp:TemplateField HeaderText="Actions">
            <ItemTemplate>                    
                <asp:Button ID="remove_itemIndex" OnClientClick="if(confirm('Are You Sure to delete?')==true){ return true;} else{ return false;}" runat="server" class="btn btn-primary" Text="REMOVE" CommandName="REMOVE_ITEM" CommandArgument='<%# Container.DataItemIndex+1 %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

 **This is the row binding event**

protected void grd_item_list_RowCommand(object sender, GridViewCommandEventArgs e) {

    item_list_bind_structure();

    if (ViewState["item_list"] != null)
        dt = (DataTable)ViewState["item_list"];


    if (e.CommandName == "REMOVE_ITEM") {
        var RowNum = Convert.ToInt32(e.CommandArgument.ToString()) - 1;

        DataRow dr = dt.Rows[RowNum];
        dr.Delete();

    }

    grd_item_list.DataSource = dt;
    grd_item_list.DataBind();
}

nelek · Answer 9 · 2017-04-15T06:23:03.643

I know this is, very, old question, and I have similar situation few days ago.

Problem was, in my table are approx. 10000 rows, so looping trough DataTable rows was very slow.

Finally, I found much faster solution, where I make copy of source DataTable with desired results, clear source DataTable and merge results from temporary DataTable into source one.

note : instead search for Joe in DataRow called name You have to search for all records whose not have name Joe (little opposite way of searching)

There is example (vb.net) :

'Copy all rows into tmpTable whose not contain Joe in name DataRow
Dim tmpTable As DataTable = drPerson.Select("name<>'Joe'").CopyToTable
'Clear source DataTable, in Your case dtPerson
dtPerson.Clear()
'merge tmpTable into dtPerson (rows whose name not contain Joe)
dtPerson.Merge(tmpTable)
tmpTable = Nothing

I hope so this shorter solution will help someone.

There is c# code (not sure is it correct because I used online converter :( ):

//Copy all rows into tmpTable whose not contain Joe in name DataRow
DataTable tmpTable = drPerson.Select("name<>'Joe'").CopyToTable;
//Clear source DataTable, in Your case dtPerson
dtPerson.Clear();
//merge tmpTable into dtPerson (rows whose name not contain Joe)
dtPerson.Merge(tmpTable);
tmpTable = null;

Of course, I used Try/Catch in case if there is no result (for example, if Your dtPerson don't contain name Joe it will throw exception), so You do nothing with Your table, it stays unchanged.

score 1 · Answer 10 · edited Apr 19 '19 at 08:06

1

You try this for getting and removing id column from data table

if (dt1.Columns.Contains("ID"))
{
    for (int i = dt1.Rows.Count - 1; i >= 0; i--)
    {
        DataRow dr = dt1.Rows[i];

        if (dr["ID"].ToString() != "" && dr["ID"].ToString() != null)
        {
            dr.Delete();
        }
    }

    dt1.Columns.Remove("ID");
}

edited Apr 19 '19 at 08:06

marc_s

732,580
175
1,330
1,459

answered Apr 06 '19 at 10:43

shubham

21
1

score 1 · Answer 11 · answered Apr 22 '19 at 13:46

I'm seeing various bits and pieces of the right answer here, but let me bring it all together and explain a couple of things.

First of all, AcceptChanges should only be used to mark the entire transaction on a table as being validated and committed. Which means if you are using the DataTable as a DataSource for binding to, for example, an SQL server, then calling AcceptChanges manually will guarantee that that the changes never get saved to the SQL server.

What makes this issue more confusing is that there are actually two cases in which the exception is thrown and we have to prevent both of them.

1. Modifying an IEnumerable's Collection

We can't add or remove an index to the collection being enumerated because doing so may affect the enumerator's internal indexing. There are two ways to get around this: either do your own indexing in a for loop, or use a separate collection (that is not modified) for the enumeration.

2. Attempting to Read a Deleted Entry

Since DataTables are transactional collections, entries can be marked for deletion but still appear in the enumeration. Which means that if you ask a deleted entry for the column "name" then it will throw an exception. Which means we must check to see whether dr.RowState != DataRowState.Deleted before querying a column.

Putting it all together

We could get messy and do all of that manually, or we can let the DataTable do all the work for us and make the statement look and at more like an SQL call by doing the following:

string name = "Joe";
foreach(DataRow dr in dtPerson.Select($"name='{name}'"))
    dr.Delete();

By calling DataTable's Select function, our query automatically avoids already deleted entries in the DataTable. And since the Select function returns an array of matches, the collection we are enumerating over is not modified when we call dr.Delete(). I've also spiced up the Select expression with string interpolation to allow for variable selection without making the code noisy.

score 1 · Answer 12 · answered Apr 13 '11 at 11:28

Where is the problem: It is forbidden to delete items from collection inside a foreach loop.

Solution: Either do it like Widor wrote, or use two loops. In the first pass over DataTable you only store (in a temporary list) the references to rows you want to delete. Then in the second pass over your temporary list you delete those rows.

score 0 · Answer 13 · edited Mar 16 '16 at 05:55

I have a dataset in my app and I went to set changes (deleting a row) to it but ds.tabales["TableName"] is read only. Then I found this solution.

It's a wpf C# app,

try {
    var results = from row in ds.Tables["TableName"].AsEnumerable() where row.Field<string>("Personalid") == "47" select row;                
    foreach (DataRow row in results) {
        ds.Tables["TableName"].Rows.Remove(row);                 
    }           
}

score -2 · Answer 14 · answered Oct 08 '20 at 17:19

-2

the easy way use this in button :

 var table = $('#example1').DataTable();
 table.row($(`#yesmediasec-${id}`).closest('tr')).remove( ).draw();

example1 = id table . yesmediasec = id of the button in the row

use it and every thing will be ok

answered Oct 08 '20 at 17:19

Salim Fh

75
6

1

The question is about C# not jQuery Plugin – Ian Dec 22 '21 at 09:11

Deleting specific rows from DataTable

14 Answers14

Linked