Results 1 to 16 of 16

Thread: There is already an open DataReader???

  1. #1

    Thread Starter
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    There is already an open DataReader???

    I have the following (this is stripped testcase):

    vb Code:
    1. Using Scon As New System.Data.SqlClient.SqlConnection("Data Source=MYPC\INSTANCE;Initial Catalog=DB;Integrated Security=True;MultipleActiveResultSets=True")
    2.             Scon.Open()
    3.             Using Scmd = Scon.CreateCommand()
    4.                 Scmd.CommandText = "SELECT * FROM Test1"
    5.                 Using r = Scmd.ExecuteReader()
    6.                     Do While r.Read
    7.                         Scmd.CommandText = "SELECT * FROM Test2"
    8.                         Scmd.ExecuteScalar() '<<ERRORS HERE
    9.                     Loop
    10.                 End Using
    11.             End Using
    12. End Using

    But i get the error:
    There is already an open DataReader associated with this Command which must be closed first.
    (SQL 2012)

    ... Why is this the case, I have specified MultipleActiveResultSets in the connection string?

    Thanks in advance,
    Kris

  2. #2
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: There is already an open DataReader???

    Hi,

    Here's a similar post with suggestions from SH and TG.
    VBForums There-is-already-an-open-DataReader

    KGC
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: There is already an open DataReader???

    Yup... readers obtain an EXCLUSIVE lock on the connection... all the MultipleActiveResultSets setting means is that you can execute a command that will return multiple results... but you can't access all of them all at the same time... you have to iterate through the first, then move to the second, then to the third etc...

    so.. when you ExecuteReader, that connection is locked, it can't be used for anything else. If you then need to execute more commands inside your loop, you will need a SECOND connection.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: There is already an open DataReader???

    Quote Originally Posted by i00 View Post
    I have the following (this is stripped testcase):

    vb Code:
    1. Using Scon As New System.Data.SqlClient.SqlConnection("Data Source=MYPC\INSTANCE;Initial Catalog=DB;Integrated Security=True;MultipleActiveResultSets=True")
    2.             Scon.Open()
    3.             Using Scmd = Scon.CreateCommand()
    4.                 Scmd.CommandText = "SELECT * FROM Test1"
    5.                 Using r = Scmd.ExecuteReader()
    6.                     Do While r.Read
    7.                         Scmd.CommandText = "SELECT * FROM Test2"
    8.                         Scmd.ExecuteScalar() '<<ERRORS HERE
    9.                     Loop
    10.                 End Using
    11.             End Using
    12. End Using

    But i get the error:
    There is already an open DataReader associated with this Command which must be closed first.
    (SQL 2012)

    ... Why is this the case, I have specified MultipleActiveResultSets in the connection string?

    Thanks in advance,
    Kris

    From: Using Multiple Active Result Sets (MARS)
    SQL Server 2005 introduced support for multiple active result sets (MARS) in applications accessing the Database Engine. In earlier versions of SQL Server, database applications could not maintain multiple active statements on a connection. When using SQL Server default result sets, the application had to process or cancel all result sets from one batch before it could execute any other batch on that connection. SQL Server 2005 introduced a new connection attribute that allows applications to have more than one pending request per connection, and in particular, to have more than one active default result set per connection.

    MARS simplifies application design with the following new capabilities:

    Applications can have multiple default result sets open and can interleave reading from them.

    Applications can execute other statements (for example, INSERT, UPDATE, DELETE, and stored procedure calls) while default result sets are open.
    See also: Default Result Set Processing and Multiple Active Result Sets

    Executing the SqlCommand creates the result set. You can execute multiple SqlCommands using the same SqlConnection simultaneously with MultipleActiveResultSets = True, but you can not redefine the SqlCommand mid-stream as you have and the problem does not involve MultipleActiveResultSets.

    SqlCommand Class
    You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.
    Solution - create a second SqlCommand.

    Note that even-though the issue in this case is related to the improper re-use of the SqlCommand, that you will get the same error message if you Set MultipleActiveResultSets =False and use isolated SqlCommand instantances.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: There is already an open DataReader???

    You can execute multiple SqlCommands using the same SqlConnection simultaneously with MultipleActiveResultSets = True, but you can not redefine the SqlCommand mid-stream as you have and the problem does not involve MultipleActiveResultSets.
    Even if he could (which I didn't catch initially)... the problem of the use of a Reader in the first place still remains... even if the second command had been a completely new command object, it still won't execute using the existing connection because it's locked up by the reader. A second connection would be needed to run further commands inside the loop. Or a design change to not use a reader, but to get the results back in a datatable or some kind of List and iterate through that.

    I see other issues, but I'm hoping that the code is just for illustrative purposes and not truly representative of the real code, so I'll keep mum on that (for now).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Junior Member
    Join Date
    Aug 2014
    Posts
    30

    Re: There is already an open DataReader???

    If your done add this?
    Code:
    Scon.close
    or
    Scon.Dispose

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: There is already an open DataReader???

    Quote Originally Posted by tantan05 View Post
    If your done add this?
    Code:
    Scon.close
    or
    Scon.Dispose
    the End Using takes care of that.

    -tg

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: There is already an open DataReader???

    Quote Originally Posted by techgnome View Post
    Even if he could (which I didn't catch initially)... the problem of the use of a Reader in the first place still remains... even if the second command had been a completely new command object, it still won't execute using the existing connection because it's locked up by the reader. A second connection would be needed to run further commands inside the loop. Or a design change to not use a reader, but to get the results back in a datatable or some kind of List and iterate through that.
    Tg, what you are writing contradicts both my interpretation of the documentation and what I observe as possible in code. In fact if what you say is true, it would really make the existence of MultipleActiveResultSets pointless as the behavior would be the same as if one were not using it.

    Could use please point me to some documentation of this blocking nature of the DataReader when implementing MultipleActiveResultSets?
    Last edited by TnTinMN; Sep 25th, 2014 at 07:25 AM. Reason: typo

  9. #9
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: There is already an open DataReader???

    Tg, what you are writing contradicts both my interpretation of the documentation and what I observe as possible in code.
    same here. i remember doing this without problem. using the same command object as the initially posted code does of course can't work.

  10. #10
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: There is already an open DataReader???

    maybe what tg describes came from the fact that the records accessed by the first command are locked on database level and issuing a second command accessing the same records may cause a deadlock? if this is the case then [with nolock] could work around this although its a bad idea and i wont suggest it especially as querying the same records reveals a bad design idea (why query the same two times?)

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: There is already an open DataReader???

    I figured it out... I was partially right, partially misguided.

    So like a good developer, I did some searchnig... found this:
    http://blogs.msdn.com/b/sqlprogramma...oduction1.aspx

    with this example: (yes, I know it's in C#)
    Code:
                // create two commands that are in the same connection conn1
                SqlCommand cmd1 = new SqlCommand(“SELECT * FROM DB1.dbo.t1”, conn1, …);
                SqlCommand cmd2 = new SqlCommand(“SELECT * FROM DB2.dbo.t1”, conn1, …);
     
                // Send the two requests to server
                SqlDataReader* reader1 = cmd1.ExecuteReader();
                SqlDataReader* reader2 = cmd2.ExecuteReader();
    When you read the explanation, you see why the example works, but not the OP's code:
    After you sent cmd1.ExecuteReader(), server starts to run the command SELECT * FROM DB1.dbo.t1, and it sends the result to client, since client is not reading the result yet, server thread is blocked in writing packets, so it gives up the connection resource to let other request in the same connection to run. Now cmd2.ExecuteReader() picks up the connection resource, run the SELECT, and sends result to client. Since now client tries to read result from first command, the server thread that processes second SELECT is again blocked, so it gives up the connection resource to let first SELECT continue to run.
    So you CAN use multiple readers against a single connection UP UNTIL you start to read one of them. As soon as you read from (I'm guessing) either reader, the other will be blocked. That is until the read reader runs through its results.

    So I'm not completely nutters ... just partially so.

    So... I'll stand by my now revised statement that the error is happening because you've started to read from the datareader, once you start that, the connection is blocked from being able to be used for any other results. You will either need to get both readers first, or not use a reader for the outer loop, or use a secondary connecting on the inside.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: There is already an open DataReader???

    So... I'll stand by my now revised statement that the error is happening because you've started to read from the datareader, once you start that, the connection is blocked from being able to be used for any other results. You will either need to get both readers first, or not use a reader for the outer loop, or use a secondary connecting on the inside.
    Not confirmed. I just tested it and the following code runs fine. both with executescalar as well as a second reader:
    Code:
            Using cn As New System.Data.SqlClient.SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Timeout=5;MultipleActiveResultSets=True;Initial Catalog=****;Data Source=****")
                cn.Open()
                Using cmd1 = cn.CreateCommand()
                    cmd1.CommandText = "SELECT TOP 100 * FROM Table"
                    Using r = cmd1.ExecuteReader()
                        Do While r.Read
                            Dim cmd2 As New Data.SqlClient.SqlCommand("SELECT TOP 10 LogId FROM Table", cn)
                            'Debug.Print(cmd2.ExecuteScalar())
                            Using r2 = cmd2.ExecuteReader
                                Do While r2.Read
                                    Debug.Print(r2(0).ToString)
                                Loop
                            End Using
                        Loop
                    End Using
                End Using
            End Using
    That does not necessarily mean you are "completely nutters", i have never and would never suggest that

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: There is already an open DataReader???

    This is really the same thing digitalShaman did. I didn't see his\her solution until after I did it but since it is accessing AdventureWorks, which is available to everyone from SQL 2005 on, I thought I'd post it.

    Code:
    Using Scon As New System.Data.SqlClient.SqlConnection("Server=devsql\common1;Database=AdventureWorks;Trusted_Connection=Yes;MultipleActiveResultSets=True")
                Scon.Open()
                Using Scmd = Scon.CreateCommand()
                    Scmd.CommandText = "SELECT top 5 * FROM Person.Contact"
                    Using r = Scmd.ExecuteReader()
                        Do While r.Read
                            Using Scmd2 = Scon.CreateCommand()
                                Scmd2.CommandText = "SELECT top 5 * FROM Person.Contact"
                                Using r2 = Scmd2.ExecuteReader()
                                    Do While r2.Read
    
                                    Loop
                                End Using
                            End Using
                        Loop
                    End Using
                End Using
            End Using
    Please remember next time...elections matter!

  14. #14
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: There is already an open DataReader???

    well then what the hell do I know? Meanwhile we haven't heard from the OP in a while... so who knows? OH heck, we haven't heard fro the OP since the first post.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  15. #15

    Thread Starter
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: There is already an open DataReader???

    Hey sorry ... been busy ... I just ended up using a 2nd connection ...

    Thanks,
    Kris

  16. #16
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,712

    Re: There is already an open DataReader???

    When I get into that situation, I just make 2 loops and dump the results from the first loop to a list to use in the second...

    Code:
    While reader.Read
       MyList.Add(reader.Item("Blah").ToString)
    End While
    reader.Close
    
    For Each blah As String In MyList
      command2.Parameters("@BLAH", blah)
      reader.ExecuteReader
      While reader.Read
        '...
      End While
      reader.Close
    Next
    Chances are though, you should be using a JOIN in your SQL anyways, then you only have one table to loop through.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

Posting Permissions

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



Click Here to Expand Forum to Full Width