Error "there is already an open datareader associated with this command which must be closed first."

Question

1.00/5 (2 votes)

See more:

How do i overcome this error always occurs at the time of multiple hits on same process "There is already an open Data Reader associated with this Command which must be closed first."

Any Help will be appreciated.

Thanks in Advance

What I have tried:

This error occurs at multiple places in my project.

This is my Code:

VB

Shared Function GetTable(ByVal SqlString As String) As DataSet
Dim ds As New DataSet
If con.State = ConnectionState.Closed Then con.Open()
Using con
Dim da As New SqlClient.SqlDataAdapter(SqlString, con)
da.SelectCommand.CommandTimeout = 5000
da.Fill(ds)
End Using
Return ds

Posted 8-May-17 23:41pm

Anjani Rawat

Updated 9-May-17 0:43am

Ralf Meier

v2

Add a Solution

Comments

CPallini 9-May-17 5:54am

Close the darned DataReader, then.
(I can't give a better answer, because you gave me poor info).

Anjani Rawat 9-May-17 6:00am

I haven't use datareader in my project,i have used dataset but this error occurs.

Ralf Meier 9-May-17 6:03am

And which help do you want to get without any Information about your code-construction ?

Anjani Rawat 9-May-17 6:05am

This is my Code:

Shared Function GetTable(ByVal SqlString As String) As DataSet
Dim ds As New DataSet
If con.State = ConnectionState.Closed Then con.Open()
Using con
Dim da As New SqlClient.SqlDataAdapter(SqlString, con)
da.SelectCommand.CommandTimeout = 5000
da.Fill(ds)
End Using
Return ds

Ralf Meier 9-May-17 6:16am

So ... why don't you close your Connection before leaving the function ?

Anjani Rawat 9-May-17 6:44am

Please see the code i have already closed the connection as "End Using" and start the connection as "Using con"

Ralf Meier 9-May-17 7:31am

if 'End Using' closes the Connection - why did you open it explicit before 'Using con' ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-05-09T00:43:00

When you open a DataReader, it "blocks" the connection until the reader is closed.
Somewhere in your code you have created a DataReader from an SQL command, and started it reading using the same connection object - and it will remain active until the Reader ort the Connection object is closed, even if the method that uses it has finished executing.

Look at your code, find everywhere you use the connection, and make sure that you close and Dispose of everything you use: SqlCommand, SqlDataAdapter, SqlDataReader.

The best way to do this is to delete the global connection object, and create new connections each time you want to use it: SqlConnections are a scarce resource and you shouldn't hold on to them, particularly if they are open, as they may block other users from the DB.