DataReader and database Connection

Inside a function, i call a stored procedure with a datareader. The function is called repeatedly (like 8 times) feeding a different parameter into the function. Then i get the "There is already an open DataReader associated with this Connection which must be closed first." error. I close both the datareader and connection object at the end of the function. I thought i found the issue on MS support but it seems like a framework 1.0 issue i'm running 1.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q31934

Any ideas? I would really appreciate it if someone could shed some light

anonymous
Fri Feb 20 14:11:05 CST 2004

Can you please post your code so we can help you

Tu-Thac
www.ongtech.co

----- jimbo wrote: ----

Inside a function, i call a stored procedure with a datareader. The function is called repeatedly (like 8 times) feeding a different parameter into the function. Then i get the "There is already an open DataReader associated with this Connection which must be closed first." error. I close both the datareader and connection object at the end of the function. I thought i found the issue on MS support but it seems like a framework 1.0 issue i'm running 1.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q31934

Any ideas? I would really appreciate it if someone could shed some light

jim
Fri Feb 20 14:21:05 CST 2004

Okay, I just read you can't nest DataReaders. Okay fine, so how do you nest stored procedure calls then?

----- jimbo wrote: -----

Inside a function, i call a stored procedure with a datareader. The function is called repeatedly (like 8 times) feeding a different parameter into the function. Then i get the "There is already an open DataReader associated with this Connection which must be closed first." error. I close both the datareader and connection object at the end of the function. I thought i found the issue on MS support but it seems like a framework 1.0 issue i'm running 1.1

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319345

Any ideas? I would really appreciate it if someone could shed some light.

Kirk
Fri Feb 20 14:18:24 CST 2004

can you send us the code for the procedure?

"jimbo" <jim.hong@staubach.com> wrote in message
news:C97C9942-5B7B-40D4-94BC-DCDC233569DE@microsoft.com...
> Inside a function, i call a stored procedure with a datareader. The
function is called repeatedly (like 8 times) feeding a different parameter
into the function. Then i get the "There is already an open DataReader
associated with this Connection which must be closed first." error. I close
both the datareader and connection object at the end of the function. I
thought i found the issue on MS support but it seems like a framework 1.0
issue i'm running 1.1
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319345
>
> Any ideas? I would really appreciate it if someone could shed some light.
>

Kirk
Fri Feb 20 14:37:54 CST 2004

I ran into the same dilema on a project I did. the solution I finally
implemented was to load the data into a datatable instead of using the
DataReader. Then loop through the DataTable to make the next set of calls.
this way the connection is not kept busy by the DataReader.
The only other option (which is MUCH worse) is to keep opening new
connections. Not something I was willing to do.

OR, there is one other option.... (also not a great one by the way) , you
could use an ADO Recordset. ADO Recordsets do not lock the connection the
way an ADO.Net datareader does.

Kirk Graves
KRGIT Software

"jimbo" <jim.hong@staubach.com> wrote in message
news:8BAE931D-B0A2-4EDA-83EC-3CF566EE1C40@microsoft.com...
> Okay, I just read you can't nest DataReaders. Okay fine, so how do you
nest stored procedure calls then?
>
> ----- jimbo wrote: -----
>
> Inside a function, i call a stored procedure with a datareader. The
function is called repeatedly (like 8 times) feeding a different parameter
into the function. Then i get the "There is already an open DataReader
associated with this Connection which must be closed first." error. I close
both the datareader and connection object at the end of the function. I
thought i found the issue on MS support but it seems like a framework 1.0
issue i'm running 1.1
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319345
>
> Any ideas? I would really appreciate it if someone could shed some
light.
>

(
Sat Feb 21 02:23:11 CST 2004


Hi jim,

We have reviewed your issue, and will do some research on it. We will reply
to you ASAP.

Thanks for your understanding.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

v-kevy
Sun Feb 22 23:18:01 CST 2004

Hi Jim,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to open a SqlDataReader from a
SqlConnection in a function. However, the SqlDataReader doesn't seem to be
closed properly at the end of that function. If there is any
misunderstanding, please feel free to let me know.

I have tried to reproduce it with the following code. I called read()
method from another function in a loop for 8 times. However, I cannot
reproduce it. Would you try there codes on your machine? If the problem
still persists, would you please paste your code here?

private void read()
{
this.sqlConnection1.Open();
SqlCommand com = new SqlCommand("SELECT * FROM Table22",
this.sqlConnection1);
SqlDataReader r = com.ExecuteReader();
r.Read();
r.Close();
this.sqlConnection1.Close();
}

Thank you for your cooperation. If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."