Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts

    Multiple Inserts - "already an open DataReader associated with this Command"

    the full error is:
    System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.
    i am trying to loop through a SqlDataReader and then on each pass use the data i got from it to SqlCommand.ExecuteNonQuery().

    This works:
    Code:
    SqlCommand objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, 'Hello')", objConn);
    objInsertCommand.ExecuteNonQuery();
    objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, 'Hello')", objConn);
    objInsertCommand.ExecuteNonQuery();
    but when i stick it in a loop like this it doesnt:
    Code:
    objCommand = new SqlCommand("SELECT field1, field2 FROM sourcetable", objConn);
    objDataReader = objCommand.ExecuteReader();
    
    while (objDataReader.Read())
    {
        objInsertCommand = new SqlCommand("INSERT INTO tablename (field1, field2) VALUES (3, '" + objDataReader[0] + "')", objConn);
        objInsertCommand.ExecuteNonQuery();
    }
    
    objDataReader.Close();
    (i am not just copying data, this is a simplified version of my code) if there is a better way of getting the data and closing the datareader before the loop (eg recordset.GetRows() in asp3) or a way of batching all my inserts into one and running it after closing this datareader, i am open to that

    EDIT: Currently i am just concatenating all my insert statements with a semicolon and appending to a StringBuilder then running that at the end, dont know if this is the best way of doing it though.
    Last edited by ghell; 11-25-2005 at 03:43 PM.

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    I'm far too new at ASP.NET to help you with the specific code, but perhaps using the DataAdapter class would work for you. Here is some sample code that hopefully is what you need.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    theres a msdn2 for .net2.0 btw now if u use it, it even works in browsers other than ie!

    however, i think dataadapter is used to select records, manipulate them and then update the changes.. if im wrong please tell me

  • #4
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    It is my understanding from the documentation in that link I posted that DataAdapter will do inserts, updates and deletes.

    The Update method of the DataAdapter is called to resolve changes from a DataSet back to the data source. The Update method, like the Fill method, takes as arguments an instance of a DataSet, and an optional DataTable object or DataTable name. The DataSet instance is the DataSet that contains the changes that have been made, and the DataTable identifies the table from which to retrieve the changes.

    When you call the Update method, the DataAdapter analyzes the changes that have been made and executes the appropriate command (INSERT, UPDATE, or DELETE).
    Maybe this will help you and maybe it won't but it's worth looking at the documentation, I think.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #5
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I think loading your query into a dataset or datatable and then looping through each of the rows in the datatable would be a good way to handle it. Also, I think making a few stored procedures for you inserts would work out well. You could have your stored procedure names in an array, and then each time through your datatable you could grab your stored procedure from the array and pass in the values.

    As for the error, your sql connection is already being used by your first query, and still is when you're looping through each record, so you'd most probably have to use another connection object in order to do it the way you currently are. On a side note, I don't know what version of SQL Server you're using but in SQL Server 2005 you can use MARS - Multiple Active Result Sets, which would probably eliminate this problem.

    Good luck;
    Last edited by Brandoe85; 11-25-2005 at 11:15 PM.

  • #6
    New to the CF scene
    Join Date
    Aug 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Create one more connection object, and use new con object for datareader.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •