-
Sep 24th, 2014, 04:39 AM
#1
There is already an open DataReader???
I have the following (this is stripped testcase):
vb Code:
Using Scon As New System.Data.SqlClient.SqlConnection("Data Source=MYPC\INSTANCE;Initial Catalog=DB;Integrated Security=True;MultipleActiveResultSets=True")
Scon.Open()
Using Scmd = Scon.CreateCommand()
Scmd.CommandText = "SELECT * FROM Test1"
Using r = Scmd.ExecuteReader()
Do While r.Read
Scmd.CommandText = "SELECT * FROM Test2"
Scmd.ExecuteScalar() '<<ERRORS HERE
Loop
End Using
End Using
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
-
Sep 24th, 2014, 06:28 AM
#2
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
-
Sep 24th, 2014, 06:57 AM
#3
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
-
Sep 24th, 2014, 09:13 AM
#4
Re: There is already an open DataReader???
Originally Posted by i00
I have the following (this is stripped testcase):
vb Code:
Using Scon As New System.Data.SqlClient.SqlConnection("Data Source=MYPC\INSTANCE;Initial Catalog=DB;Integrated Security=True;MultipleActiveResultSets=True")
Scon.Open()
Using Scmd = Scon.CreateCommand()
Scmd.CommandText = "SELECT * FROM Test1"
Using r = Scmd.ExecuteReader()
Do While r.Read
Scmd.CommandText = "SELECT * FROM Test2"
Scmd.ExecuteScalar() '<<ERRORS HERE
Loop
End Using
End Using
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.
-
Sep 24th, 2014, 09:29 AM
#5
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
-
Sep 24th, 2014, 09:47 AM
#6
Junior Member
Re: There is already an open DataReader???
If your done add this?
Code:
Scon.close
or
Scon.Dispose
-
Sep 24th, 2014, 09:48 AM
#7
Re: There is already an open DataReader???
Originally Posted by tantan05
If your done add this?
Code:
Scon.close
or
Scon.Dispose
the End Using takes care of that.
-tg
-tg
-
Sep 24th, 2014, 10:14 AM
#8
Re: There is already an open DataReader???
Originally Posted by techgnome
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
-
Sep 24th, 2014, 10:23 AM
#9
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.
-
Sep 24th, 2014, 10:27 AM
#10
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?)
-
Sep 24th, 2014, 11:51 AM
#11
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
-
Sep 25th, 2014, 01:41 AM
#12
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
-
Sep 25th, 2014, 05:47 AM
#13
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!
-
Sep 25th, 2014, 05:50 AM
#14
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
-
Sep 25th, 2014, 09:14 AM
#15
Re: There is already an open DataReader???
Hey sorry ... been busy ... I just ended up using a 2nd connection ...
Thanks,
Kris
-
Sep 25th, 2014, 03:48 PM
#16
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|