none
There is already an open DataReader associated with this Command which must be closed first. RRS feed

  • Question

  • I have the same issue. I get an

    Code Snippet

    System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.


    exception and I understand why it happens. My code looks something like that.
    First I read some data in.


    Code Snippet

    SqlCommand command =
    new SqlCommand(sbSelect.ToString(), transaction.Connection, transaction);

    SqlDataReader objDR;
    objDR = command.ExecuteReader(CommandBehavior.CloseConnection);


    //...do some reading from objDR...


    objDR.Close();


    The Close() method now sets the Connection property of my transaction object to null which makes the transaction unusable. Calling


    Code Snippet

     

    SqlCommand command =
    new SqlCommand(sbUpdate.ToString(), transaction.Connection, transaction);


    int rowsAffected = command.ExecuteNonQuery(); //Exception is thrown here



    doesn't work now because I don't have a connection anymore.
    It must be possible to use the same connection for reading and writing without using SQL2005 and the 'MultipleActiveResultSets=True'.

    In the meantime I simply use two connections. The weird thing is though that the Close() Method of the DataReader doesn't destroy the connection object now. Only when I use it together with a transaction.

    Is there a reason for that?

    Cheers,
    Tobi

    Friday, August 29, 2008 12:56 AM

All replies

  • Hi,

     

    You just need to enable Multiple Active Result Sets (MARS), just add 'MultipleActiveResultSets=True' into your connection string.

    Wednesday, March 30, 2011 9:13 AM
  • Why do you have CommandBehavior.CloseConnection if you want to reuse your connection?

    You also have to either commit or rollback your transaction before you close a connection.

    Wednesday, March 30, 2011 9:23 AM