none
need to delete a row in a dataset during loop RRS feed

  • Question

  • Hello,

    I have two data sets that i'm looping through.  The first is relatively small while the second can potentially get very big (200,000 records or so).  In order to speed things up, I'm trying to delete a record in the big set after I'm done with it.  Problem is that I keep getting the "deleted row information cannot be accessed through the row" error.  I've tried a "for" loop and a "foreach" loop for my INNER loop, but I've listed the
    "for" code below.  My question is: how can I get this row deleted?  Any help would be appreciated.

    foreach (DataRow dataRow in dsCustomers.Tables["myTable"].Rows)
            {
                string extractRow = "";
                extractRow += dataRow["customer_number"];
              
                for (int i = 0; i < dsAdmissions.Tables["myTable"].Rows.Count; i++)
                {
                    if (dataRow["order_number"].ToString() == dsAdmissions.Tables["myTable"].RowsIdea["order_number"].ToString())
                    {
                        extractRow += "," + dsAdmissions.Tables["myTable"].RowsIdea["seatloc_seat"];
                        dsAdmissions.Tables["myTable"].RowsIdea.Delete();
                    }
                    else
                    {
                        break;
                    }
                   
                }
               
                extractRow += "\n";
            
                Console.Write(extractRow);
            }
    Tuesday, March 25, 2008 11:40 PM

All replies

  •  seisner wrote:
    Hello,

    I have two data sets that i'm looping through.  The first is relatively small while the second can potentially get very big (200,000 records or so).  In order to speed things up, I'm trying to delete a record in the big set after I'm done with it.  Problem is that I keep getting the "deleted row information cannot be accessed through the row" error.  I've tried a "for" loop and a "foreach" loop for my INNER loop, but I've listed the
    "for" code below.  My question is: how can I get this row deleted?  Any help would be appreciated.

    foreach (DataRow dataRow in dsCustomers.Tables["myTable"].Rows)
            {
                        dsAdmissions.Tables["myTable"].Rows.Delete();
                                   
                }
                
           
                Console.Write(extractRow);
            }

     

    You ABSOLUTELY, categorically CANNOT do it. You cannot delete anything in the rows collection. Once you've deleted a row the collection loses all meaning. Forget it.

     

    If you want to delete your rows use TableAdapter. You can set them up in the designer and connect to your database again in the designer. You will have to find the database/table thru a connection string or just even navigating a File dialog box. it is elementary.

     

    There will be commands offered once you set it up whereby you can delete any rows you want and the result will stick, I mean the rows will also be deleted in the table.

    Wednesday, March 26, 2008 2:24 AM
  • Hi,

     

    You cannot delete a datarow from the collection while looping through the DataTable. There is a workaround for this

     

    After looping through your datatable, you can use "Select" command to retrieve the datarow collection. and you can loop through the collection and delete it.

     

    DataRow[] drRemoveItems = DtItems.Select("SomeFlag='value");

    if (drRemoveItems.Length > 0)

    {

    foreach (DataRow drRemove in drRemoveItems )

    {

    DtItems.Rows.Remove(drRemove);

    }

    }

     

    Hope this helps.

     

    Regards

    Ramakrishnan S

    Wednesday, March 26, 2008 4:47 AM
  • The workaround Rama Suggested won't work because it will only delete rows in your program footprint. It won't be reflected in the Datatable itself. It is just a RAM deletion.

     

    If you want to delete things you have a number of options. The best for you is to write a Stored Procedure in Sql Server. It is the best since you have a very large table. With DataAdapter it will take a lot of time just to open it up.

     

    TableAdapter is a sure thing but it is slow.

     

    You can delete by using TRANS SQL DELETE command from you code and ExecuteNonQuery ()

     

    However, the absolutely best thing will be an SQL stored procedure or CLR stored procedure.

    Wednesday, March 26, 2008 12:59 PM
  • Hey guys,

     

    Thanks for all the answers.  Actually... deleting things in memory is all I want to do.  I don't actually want to delete anything in the database.  So - I'm going to try Rama's workaround.

     

    I'll post what I glean!

     

    Wednesday, March 26, 2008 5:29 PM
  •  

    Scratch that... I need to delete the records on the fly.  That being said the code seems to be performing significantly well, so it's possible that I may not actually need to do that.  Will keep you posted!
    Wednesday, March 26, 2008 7:35 PM