Removing specific rows from a DataTable

Question

I want to delete some rows from a DataTable, but it gives such an error,

The collection has been modified; enumeration operation may not be performed

I use to remove 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? What method do you recommend?

Answer 1

If you delete an item from a collection, this collection has been modified and you cannot continue enumerating on it.

Use a For loop instead, for example:

 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 iterate in reverse order to avoid skipping a row after deleting the current index.

Answer 2

Before everyone goes on to win “You cannot delete lines in an enumeration,” you first need to understand that DataTables are transactional , and technically do not flush the changes until you call AcceptChanges ()

If you see this exception when calling Delete , you are already in the data state of the pending changes. For example, if you just loaded from the database, calling Delete will throw an exception if you are inside the foreach loop.

BUT! BUT!

If you load rows from the database and call the AcceptChanges () function, you commit all these pending changes to the DataTable. Now you can iterate over the list of lines calling Delete () without paying attention to the world, because it just marks the line to be deleted, but is not fixed until you again call AcceptChanges ()

I understand that this answer is a bit outdated, but recently I had to face a similar problem, and I hope this will relieve some pain of the future developer working on 10-year code :)

---

Ps Here is a simple code example added by Jeff :

FROM#

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

Answer 3

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

Answer 4

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 intend to use datatable after deleting the row, you will receive an error message. So what you can do: replace dr.Delete(); on dtPerson.Rows.Remove(dr);

Answer 5

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

I hope this helps you

Answer 6

Or simply convert the list string to the DataTable string:

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

Answer 7

To remove an entire row from a DataTable

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

Answer 8

What is the problem: it is forbidden to remove elements from the collection inside the foreach loop.

Solution: either do this, as Vidor wrote, or use two loops. In the first pass over the DataTable, you save (in a temporary list) links to the rows you want to delete. Then in the second pass above your temporary list, you delete these lines.

Answer 9

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

Answer 10

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

The problem is that in my table, approx. 10,000 rows, so the DataTable loopback rows were very slow.

Finally, I found a much faster solution where I make a copy of the DataTable source with the desired results, clear the source text of the DataTable and merge from a temporary DataTable to the original one.

note : instead of searching for Joe , the DataRow is called name You need to search for all records that do not have the name Joe (slightly opposite search path)

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 

Hope this shorter solution helps someone.

There is c# code (not sure if this is correct, because I used the 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 , if there is no result (for example, if your dtPerson does not contain name Joe , it will throw an exception), so you do nothing with your table, it remains unchanged.

Answer 11

I have a dataset in my application and I went for the installation of changes (deleting a row), but ds.tabales["TableName"] read-only. Then I found this solution.

This is a wpf C# application,

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

Answer 12

You are trying this to get and remove an identifier column from a 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"); }