Data Reader Problem

Hi All,

I got an error while running this code.There is already an open DataReader associated with this Command which must be closed first

How can I resolve the error?

protected void Page_Load(object sender, EventArgs e)
{
string sql;

SqlConnection Connection = new SqlConnection("ConnectionString");
sql = "SELECT PO_SE_Line_ID FROM PO_STOCK_QUERY_LINE_DETAILS WHERE TRANS_NUM ='TR-A-00-01-93'";

SqlCommand command = new SqlCommand(sql, Connection);
SqlDataReader Dr;
Connection.Open();
Dr = command.ExecuteReader();


while (Dr.Read())
{

sql = "SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93' and PO_SE_Line_ID=" + Dr["PO_SE_Line_ID"].ToString();

SqlCommand command1 = new SqlCommand(sql, Connection);

SqlDataReader Dr1;

Dr1 = command1.ExecuteReader();

while(Dr1.Read())
{

Response.Write(Dr["SUPPLIER_ITEM_CODE"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_MAN_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_PAT_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ITEM_DESC"].ToString());
Response.Write("<br>");
Response.Write(Dr["SUPPLIER_ADDIT_DESC"].ToString());

}
Dr1.Close();

}
Dr.Close();

I tried to close the first data reader before opening the second data reader.still the error persists.

I suggest that you open a second data connection.

|||

Hi,

If I open a second connection it is showing the error there is already an open connection......

When I tried to close and open the connection Iam getting the error as Invalid attempt to read data when Datareader is closed.

|||

Hi

I guess that your facing problem because your using same sql string for both the command.

the first command already uses the string and you are trying to change that script while datareading, so obviously it gives error

so my suggestion is to use another string named like sql1, so your problem will be solved out.

I hope this will helpful to u...

|||

It's better create a stored procedure and return the results once. In your design, the performance could be a problem.

|||

Howdy!

I generally think nested queries within queries in the code is bad form (like running a query than gens a value to use in another recursion of queries).

I noticed that your first query runs against PO_STOCK_QUERY_LINE_DETAILS tables with a TRANS_NUM filter, then your 2nd query uses a field value from the first to run against the same table. I'm not sure what the design goal was, but it looks like you could simply run the query one time?

In other words, you're running a query againt a table to get a list of values to run against the same table, just run this query with a single data reader:

SELECT SUPPLIER_ITEM_CODE,SUPPLIER_MAN_DESC,SUPPLIER_PAT_DESC,SUPPLIER_ITEM_DESC,SUPPLIER_ADDIT_DESC,SUPPLIER_SUGG_RETAIL FROM PO_STOCK_QUERY_LINE_DETAILS where TRANS_NUM ='TR-A-00-01-93'

(If you wind up needing for example, a master/detail view, you'd be better off using a join or creating a relationship object in code after returning 2 tables via a dataset).

DT