C# reader is closed while reading?

Question

I am running a C# windows application, and I get an error message when trying to use a datareader. The error message is:

"Invalid attempt to call CheckDataIsReady when reader is closed."

I used stop point and saw that the code works fine until it enters the "while" loop. Once inside, it gives the error message.

I have tried to do it without closing the previous reader, but then the message changed to something like "there is already an open reader" or some such.

Here's the code:

conn = new SqlConnection(DBConnectionString);
SqlCommand select_cmd = new SqlCommand("SELECT usrRealname, usrIsowner FROM tblUSERS WHERE usrNum = " + UserID, conn);
SqlCommand select_orders = new SqlCommand("SELECT orderNum, orderBy, orderShipadrs, orderDate, orderTotal FROM tblOrders WHERE orderDeliveryDate is NULL AND fkorderTakenbyusrnum = " + UserID, conn);
conn.Open();
SqlDataReader dr = select_cmd.ExecuteReader();
dr.Read();
CurrentUser User = new CurrentUser(Convert.ToString(dr[0]), UserID, Convert.ToBoolean(dr[1]));
DetailsLabel.Text = String.Format("Welcome {0}, ID number {1}. {2}", User.getname, UserID, User.getOwner);
dr.Close();
SqlDataReader orders = select_orders.ExecuteReader();
while (orders.Read())
{
UnfulfilledOrders CurrentOrder = new UnfulfilledOrders(Convert.ToInt32(dr[0]), Convert.ToString(dr[1]), Convert.ToString(dr[2]), Convert.ToString(dr[3]), Convert.ToInt32(dr[4]));
OrderList.Items.Add(CurrentOrder);
}

What I'd trying to do is add class (UnfulfilledOrders) type objects to a listbox (OrderList).

The thing that baffles me is that I used such a while loop in a previous form in the same app, and it worked fine there. I really have no idea what I'm doing wrong. I tried twiking the code, adding or removing certain parts, but nothing seems to work.

Clearly you are reading from closed reader `dr.Close(); .... dr[0]`,... what do you expect code to do? — Alexei Levenkov, Apr 27 '16 at 22:52

score 2 · Accepted Answer · answered Apr 27 '16 at 22:54

Your problem is that in your while loop you're using dr[0] instead of orders[0]. This is trying to get the value from the SqlDataReader dr.

A good way to avoid mix-ups like this would be to create the data reader in a using block.

using (var dr = select_cmd.ExecuteReader())
{
   //your code here
   dr.Close();
}

then

using (var orders = select_orders.ExecuteReader())
{
  // your code here
  orders.Close();
}

This would prevent you from accidently referencing the wrong reader because VS would give you an error saying it doesn't exist.

Wooooooooooooow that was so stupid of me... I'm not used to dealing with more than one datareader at a time. Usually we just use the default "dr" that our teacher gave us to work with. Anyway, that indeed solved the problem. Will mark as answer. Thanx a lot! — Nimrod Yanai, Apr 27 '16 at 23:02

score 1 · Answer 2 · answered Apr 27 '16 at 22:53

Your code:

dr.Close();  //<-- dr closed
SqlDataReader orders = select_orders.ExecuteReader(); // <-- Reader is "orders" here
while (orders.Read())
{
UnfulfilledOrders CurrentOrder = new UnfulfilledOrders(Convert.ToInt32(dr[0]), Convert.ToString(dr[1]),     Convert.ToString(dr[2]), Convert.ToString(dr[3]), Convert.ToInt32(dr[4]));
OrderList.Items.Add(CurrentOrder);
}

dr was closed previously, do you mean "orders" instead of "dr"?

C# reader is closed while reading?

2 Answers2