none
deleting rows from Datatable causes error RRS feed

  • Question

  • I am trying to remove rows that are not needed from a DataTable. Basically, there may be several rows where the itemID is identical. I want to find the rows where the column "failEmail" = "fail", and using the itemID of those rows, remove all rows from the emails DataTable that have the same itemID.

    the issue is that only a couple of rows will initially have fail, I need to get the itemID of those ones, and then select all the rows in the emails datatable having that emailID, and delete them

    Here is what I have tried:

    System.Diagnostics.Debug.Print(emails.Rows.Count.ToString() + " emails!");
    
    // create a list of the email IDs for records that will be deleted
    List<DataRow> rows2Delete = new List<DataRow>();
    
    foreach (DataRow dr in emails.Rows) 
    {
       if (dr["failEmail"].ToString().ToLower() == "fail") 
       {
          rows2Delete.Add(dr);
       }
    }
    
    foreach (DataRow row in rows2Delete)
    {
       DataRow[] drRowsToCheck =emails.Select("itemID ='" + row["itemID"].ToString()  +"'");
       foreach (DataRow drCheck in drRowsToCheck)
       {
          emails.Rows.RemovedDrCheck);
          emails.AcceptChanges();
       }
    }

    Here is the error message I am getting on the second pass:

    This row has been removed from a table and does not have any data. BeginEdit() will allow creation of new data in this row

    How can I do what I need to without throwing errors like that? Is there a better way like using a LiNQ query?


    Friday, May 2, 2014 6:41 PM

Answers

  • thanks Bonnie, but I think you missed something.

    I need to find the itemid's of any records that have 'fail' in the field 'failEmail', then remove all the records that have those itemid's..

    Yes, you're right, I missed that. Sorry! However, I still think that you need to implement the rest of my suggestion: iterate *backwards* through the Rows collection and .Delete(),  not .Remove().


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, May 4, 2014 11:38 PM

All replies

  • The following code should remove all rows from the email DataTable that has an itemID which has failed at least once:

                IEnumerable<string> failedItemIds = emails.AsEnumerable().Where(r => r.Field<string>("failEmail").Equals("fail"))
                    .Select(r=>r.Field<string>("itemID"));
    
    
                for (int i = 0; i < emails.Rows.Count; ++i)
                {
                    if (failedItemIds.Contains(emails.Rows[i]["itemID"].ToString()))
                    {
                        emails.Rows.RemoveAt(i);
                        --i;
                    }
                }
    

    Friday, May 2, 2014 9:12 PM
  • Normally when deleting Rows, you have to do it "backwards", starting at the last Row and going to the first Row. And you should Delete, not Remove. And I'd do it all in one pass, like this:

    for (int i = emails.Rows.Count -1; i >= 0; i--)
    {
       if (emails.Rows[i]["failEmail"].ToString().ToLower() == "fail") 
       {
          emails.Rows[i].Delete();
       }
    }
    // The AcceptChanges() should be done *outside* the loop.
    // And, I'm assuming you don't need to update a database with those deleted rows.
    // If you do, don't do an AcceptChanges()
    emails.AcceptChanges(); 
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Proposed as answer by Cor Ligthert Sunday, May 4, 2014 3:46 PM
    Saturday, May 3, 2014 4:22 PM
  • This question has been cross-posted in two forums. The other thread is here:

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/3f02e21a-f189-4234-8f6a-91fd5cc3b761/deleting-rows-from-datatable-causes-error?forum=csharpgeneral

    Typically, these two threads would be merged, but I've typically had issues with trying to read and follow merged threads. Oftentimes they don't make sense. So I'm not going to do that. I think my reply above is a good suggestion.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, May 3, 2014 9:12 PM
  • thanks Bonnie, but I think you missed something.

    I need to find the itemid's of any records that have 'fail' in the field 'failEmail', then remove all the records that have those itemid's..

    So I will try the answer by Magnus, which might be what I am looking for.

    Sunday, May 4, 2014 11:15 PM
  • thanks Bonnie, but I think you missed something.

    I need to find the itemid's of any records that have 'fail' in the field 'failEmail', then remove all the records that have those itemid's..

    Yes, you're right, I missed that. Sorry! However, I still think that you need to implement the rest of my suggestion: iterate *backwards* through the Rows collection and .Delete(),  not .Remove().


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, May 4, 2014 11:38 PM
  • The following code should remove all rows from the email DataTable that has an itemID which has failed at least once:

                IEnumerable<string> failedItemIds = emails.AsEnumerable().Where(r => r.Field<string>("failEmail").Equals("fail"))
                    .Select(r=>r.Field<string>("itemID"));
    
    
                for (int i = 0; i < emails.Rows.Count; ++i)
                {
                    if (failedItemIds.Contains(emails.Rows[i]["itemID"].ToString()))
                    {
                        emails.Rows.RemoveAt(i);
                        --i;
                    }
                }

    I got a NullReference exception using this code.
    Tuesday, May 6, 2014 10:08 AM
  • I'll say it again ... you won't have that problem if you iterate through the Rows *backwards*!

    for (int i = emails.Rows.Count - 1; i >= 0; i--)
    {
        if (failedItemIds.Contains(emails.Rows[i]["itemID"].ToString()))
        {
            emails.Rows[i].Delete();
        }
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 6, 2014 3:10 PM
  • Bonnie: You could iterate forward and not increment the counter when you remove an item (--i) OR iterating backwards. Both will work.

    The thing here is that, if the failEmail column may be NULL, you need to check that the failEmail column is not NULL before comparing the value of it with the "fail" string. The same goes for the itemID column although I assume that it can never be NULL:

     IEnumerable<string> failedItemIds = emails.AsEnumerable()
                    .Where(r => !string.IsNullOrEmpty(r.Field<string>("failEmail")) && r.Field<string>("failEmail").Equals("fail"))
                    .Select(r=>r.Field<string>("itemID"));
    
    
                for (int i = 0; i < emails.Rows.Count; ++i)
                {
                    if (emails.Rows[i]["itemID"] != DBNull.Value && failedItemIds.Contains(emails.Rows[i]["itemID"].ToString()))
                    {
                        emails.Rows.RemoveAt(i);
                        --i;
                    }
                }
    

               

    Tuesday, May 6, 2014 4:37 PM
  • thanks Magnus, 

    But I think I got a NullReference on the line

     IEnumerable<string> failedItemIds = emails.AsEnumerable()
                    .Where(r => !string.IsNullOrEmpty(r.Field<string>("failEmail")) && r.Field<string>("failEmail").Equals("fail"))
                    .Select(r=>r.Field<string>("itemID"));

    but I'm grateful for the help.

    Tuesday, May 6, 2014 5:35 PM
  • I did not want to reply in this thread because the replies from Bonnie looked so obvious the answer. I don't know why you took the other reply which has more problems, which has only more complexity.

    However, you ask in fact for a complex programming way. It leads to the replies which answer the question but not the problem.

    Important information is for instance if you use the table to update because then the remove can not be used because that also removes update (delete) information.

    But your real problem can in my idea more easily be done with a dataview and a rowfilter and then the method DataView.ToTable

    Not tested

                Emails.DefaultView.RowFilter = "YourColumn = Whatever";
                DataTable temp = Emails.DefaultView.ToTable();
                Emails.Clear();
                Emails.Merge(temp);


    Success
    Cor

    Wednesday, May 7, 2014 7:44 AM