DataReader and database Connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
can you send us the code for the procedure?

jimbo said:
Inside a function, i call a stored procedure with a datareader. The
Click to expand...
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
 
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.
 
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 said:
Okay, I just read you can't nest DataReaders. Okay fine, so how do you
Click to expand...
nest stored procedure calls then?
----- jimbo wrote: -----

Inside a function, i call a stored procedure with a datareader. The
Click to expand...
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
 
As you can see GetCategoryScore is nested/inside GetCategory List. I
read that you can't next DataReaders. How do you nest stored procedure
calls then? (Where the child sp receives the outputed value of the
parent sp)

Thank you, I really appreciate your response.
Jim

public static String GetCategoryList(SqlConnection conn, string
TypeID, string Office, string Year)
{
SqlCommand cmd = new SqlCommand("Trust_Select_CategoryList_sp",
conn);
cmd.CommandType = CommandType.StoredProcedure;
if (TypeID != "NULL")
{
SqlParameter cmdTypeID = new SqlParameter("@TypeID",
SqlDbType.VarChar);
cmdTypeID.Direction = ParameterDirection.Input;
cmdTypeID.Value = TypeID;
cmd.Parameters.Add(cmdTypeID);
}

SqlDataReader dr;
dr = cmd.ExecuteReader();
String ScoreGrid = "";
while (dr.Read())
{
ScoreGrid = ScoreGrid + "<tr><td>" + dr["Desc"] + "</td>";
String CategoryScore = DataAccess.GetCategoryScore(conn, Office,
Year, dr["CategoryID"].ToString());
ScoreGrid = ScoreGrid + "<td>" + CategoryScore + "%</td></tr>";

}
dr.Close();
conn.Close();
return ScoreGrid;

}

public static String GetCategoryScore(SqlConnection connCategory,
string OfficeID, string Year, string CategoryID)
{
SqlCommand cmd = new SqlCommand("Trust_Select_CategoryScore_sp",
connCategory);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter cmdOfficeID = new SqlParameter("@OfficeID",
SqlDbType.VarChar);
cmdOfficeID.Direction = ParameterDirection.Input;
SqlParameter cmdYear = new SqlParameter("@Year", SqlDbType.VarChar);
cmdYear.Direction = ParameterDirection.Input;
SqlParameter cmdCategoryID = new SqlParameter("@CategoryID",
SqlDbType.VarChar);
cmdCategoryID.Direction = ParameterDirection.Input;


cmdOfficeID.Value = OfficeID;
cmd.Parameters.Add(cmdOfficeID);
cmdYear.Value = Year;
cmd.Parameters.Add(cmdYear);
cmdCategoryID.Value = CategoryID;
cmd.Parameters.Add(cmdCategoryID);

SqlDataReader drCat;
drCat = cmd.ExecuteReader();
String CategoryScore = "";
while (drCat.Read())
{
CategoryScore = drCat["Category_Score"].ToString();
}
drCat.Close();
connCategory.Close();
connCategory.Dispose();

return CategoryScore;

}
 
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.
 
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."
 
Back
Top