[RESOLVED] Why would SQLDataReader close itself? Interesting.

Hi all,
Iam baffled at this - so basically, I want to get a set of records based on a query and then populate a combobox (with a field form the query) and then set the entire query result as a datagridview's datasource which displays all of its fields. Its to give the users 2 different views of their search to make it easier for them. here's the code:

Code:

---

SqlCommand cmd = new SqlCommand("Select rtrim(ndm_ndcode) as ndm_ndcode, rtrim(ndm_name) as ndm_name, rtrim(ndm_addr1) as ndm_addr1, rtrim(ndm_addr2) as ndm_addr2, rtrim(ndm_addr3) as ndm_addr3, rtrim(ndm_addr4) as ndm_addr4, rtrim(ndm_addr5) as ndm_addr5, rtrim(ndm_postcode) as ndm_postcode from ndmas where ndm_ndcode LIKE '" + sCustomer + "'", m_cnnMax); // it select x, y,z based on a condition on connection m_cnnMAX
                SqlDataReader rdr = cmd.ExecuteReader();
                //set datagridview's datasource
                DataTable dt = new DataTable();
                dt.Load(rdr);
                dgvwCustomerInfo.DataSource = dt;
                dgvwCustomerInfo.Refresh();
                while (rdr.Read()) ////ERROR here - says reader closed!
                {
                    cmbAccountCodes.Items.Add(rdr["ndm_ndcode"].ToString());
                }   
                rdr.Close(); rdr.Dispose();
                cmd.Dispose();

---

So, when I set the SqlDataReader object to the gridview, it works fine. If I use it to read it , then it says its already closed, even though I have not. Can you find out where the error is or Iam missing something? Please help. Thank you.

Also, it would be very useful to you and me, if you can check out the site below. Thank you very much.

Try this:

Code:

---

using( var cmd = new SqlCommand("...", m_cnnMax ) )
{
  using( var rdr = cmd.ExecuteReader() )
  {
    while (rdr.Read())
    {
      cmbAccountCodes.Items.Add(rdr["ndm_ndcode"].ToString());
    }   
  }
}

---

Quote:

---

Originally Posted by CuteAssistant

Also, it would be very useful to you and me, if you can check out the site below. Thank you very much.

---

While it's okay to list your personal website, please don't plug it. Thanks.

How will Using make any difference here?? The problem is not reading from rdr, but using rdr to set gridview's datasource and reading it into a combo...It closes once its used ...

I'll try it and post the results here...

I was thinking that you could change the order and do the Read first and then the datasource.

However, I don't think it's possible because the SqlDataReader is a read-only forward-only reader. So once its to the end of the records it can't be reset.

Quote:

---

Originally Posted by Arjay

Try this:

Code:

---

using( var cmd = new SqlCommand("...", m_cnnMax ) )
{
  using( var rdr = cmd.ExecuteReader() )
  {
    while (rdr.Read())
    {
      cmbAccountCodes.Items.Add(rdr["ndm_ndcode"].ToString());
    }   
  }
}

---

While it's okay to list your personal website, please don't plug it. Thanks.

---

Technically, that did not make sense anyway. Iam not surprised that you are an MVP?? and a Mod?? Ok, that goes against the basic principles of any OO lang like C#. Local Objects do NOT go out of scope until the function exits, or in this case the try block. So, the using keyword does NOT make any difference here.

I believe the way SQLDataReader is implemented in C# that makes all the difference. Once its loaded onto a DataTable, it does NOT go out of scope but is set to NULL or closed by the .NET runtime. I need to check on that, and get back.

And yes, I wont post those website info again,thanks!

Quote:

---

Originally Posted by CuteAssistant

Technically, that did not make sense anyway. Iam not surprised that you are an MVP?? and a Mod?? !

Ok, that goes against the basic principles of any OO lang like C#. Local Objects do NOT go out of scope until the function exits, or in this case the try block. So, the using keyword does NOT make any difference here.

I believe the way SQLDataReader is implemented in C# that makes all the difference. Once its loaded onto a DataTable, it does NOT go out of scope but is set to NULL or closed by the .NET runtime. I need to check on that, and get back.

---

I take it you didn't read my followup post that since a SqlDataReader is forward only and read-only, it isn't going to do what you wanted to anyway. Btw, even MVP's and Mod's don't get everything correct the first time, but I guess you understand that since you're the one asking for help.

As far as the using block, sure it won't help the problem, but it sure cleans up the code. Using the using block just prevents you from having to call close and dispose (not that you need to call both anyway).

It really doesn't make any difference in your case since the SqlDataReader can't be reset to the beginning of the records.

Can you do combox.items.add(dt.columns[ChosenColNum].tostring())?

@CuteAssistant - I'm going to cut out all the rhetoric and see if I can lay it out simply. There's a number of factors at work here. When you use ExecuteReader to get a reader that is using a connection that hasn't been opened yet, it will open the connection automatically. Which is fine, and expected - and a bonus :D - when the reader is then done with it's work, it will AUTOMATICALLY close the connection for you. So, when you use the reader to fill your datatable, it is opening the connection, filling the datatable, then closing the connection. This is why you are getting the closed connection error. Even though you did not explicitly close it, because you let it automatically open, it closed on it's own. Think of it like the automatic doors at a store.

Now... to solve your problem, I see two solutions... 1) Re-execute the ExecuteReader... then attempt your loop through it. Unfortunately there is no rewind method, even if there was, the connection is closed, so it wouldn't matter. 2) Since you have the data in the datatable, loop through that to get your data. Option 1 means a second trip to the data base for data you already have. Option 2 saves you from that extra trip. In addition, you could probably use a LINQ query to get the data and then just simply pass that to the combobox.

-tg

viperbyre, techgnome thank you for your replies as well. Yes, Iam simply going to read through the cells from the gridview columns - I can take data any format it comes. :)

1. Yes, DataReader is a forward only , and so it reaches its end when its read once, and it points to NULL. Or its closed and cannot be used, in simple terms. I learnt a point or two by this.

Quote:

---

Originally Posted by Arjay

I take it you didn't read my followup post that since a SqlDataReader is forward only and read-only, it isn't going to do what you wanted to anyway. Btw, even MVP's and Mod's don't get everything correct the first time, but I guess you understand that since you're the one asking for help.

As far as the using block, sure it won't help the problem, but it sure cleans up the code. Using the using block just prevents you from having to call close and dispose (not that you need to call both anyway).

It really doesn't make any difference in your case since the SqlDataReader can't be reset to the beginning of the records.

---

Thank you and sorry for that.