Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Conflicting SqlCommand Objects...

Back to Forum: Tech Off
  • User profile image
    jsampsonPC

    I'm suffering from what seems to be a rather simple problem. Here's what I'm trying to do

    Create new SQLCommand
    Create new DataReader

    Open and Read From dataReader

           Print Some stuff from the reader
       
           Create a NEW SQLCommand
           Create a New DataReader

                 Read From the new datareader

           Close New datareader


    Close First DataReader


    I cannot do this though, it keeps telling me that an Open dataReader is already associated with "this command".

    is it talking about the SqlCommand object? Because I have two distinct instances.

    Why won't this work?

  • User profile image
    phreaks

    What, you don't like using the DataAccess Application Block that comes with the Enterprise Library.

    As for your specific issue, sorry, I don't know. I don't design layers like that. That psuedocode looks very procedural though.


    Remember, sub-classsing is your friend.

  • User profile image
    jsampsonPC

    I got it working - my particular flow allowed for me to run the sub-set command after the parent commands, instead of within them.

    Create new SQLCommand
    Create new DataReader

    Open and Read From dataReader

           Print Some stuff from the reader

    Close First DataReader

           Create a NEW SQLCommand
           Create a New DataReader

                 Read From the new datareader

           Close New datareader

    I am wondering though why it wasn't working. If anybody knows, please fill me in.

  • User profile image
    Sven Groot

    It wasn't working for the reason it told you: you can have only one DataReader at the same time on a given connection. What the technical reason for this limitation is I don't know.

  • User profile image
    jsampsonPC

    Sven Groot wrote:
    It wasn't working for the reason it told you: you can have only one DataReader at the same time on a given connection. What the technical reason for this limitation is I don't know.


    That isn't what it told me Smiley It said I already had an open reader associated with that Command, not Connection. So I thought it was the sqlCommand object - I created a new instance, and still had the error.

    This stinks Smiley

  • User profile image
    SimonJ

    In VS 2005 you can enable MARS (multiple active results sets) to get more than one reader working at once.

    SimonJ

  • User profile image
    Minh

    Hmmm... I never knew that was a limitation. I guess readers are pretty expensive & why they told us to avoid them like the plague. Or at least close them up pronto fast after you're done.

  • User profile image
    JChung2006

    jsampsonPC wrote:
    
    Sven Groot wrote: It wasn't working for the reason it told you: you can have only one DataReader at the same time on a given connection. What the technical reason for this limitation is I don't know.


    That isn't what it told me Smiley It said I already had an open reader associated with that Command, not Connection. So I thought it was the sqlCommand object - I created a new instance, and still had the error.

    This stinks Smiley

    Sven's right.

    My guess is that you had both SqlCommands on the same SqlConnection and tried to open a second SqlDataReader while the first was still open off the same connection.

    He may have been wrong about the verbiage of the message, but what he told you was correct.
    While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
    http://windowssdk.msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.