CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Posts
    82

    [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.
    Last edited by CuteAssistant; September 27th, 2010 at 02:12 PM. Reason: Code tag format corrected. Got it.

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Why would SQLDataReader close itself? Interesting.

    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 View Post
    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.

  3. #3
    Join Date
    Jul 2010
    Posts
    82

    Re: Why would SQLDataReader close itself? Interesting.

    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 ...

  4. #4
    Join Date
    Jul 2010
    Posts
    82

    Re: Why would SQLDataReader close itself? Interesting.

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

  5. #5
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Why would SQLDataReader close itself? Interesting.

    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.

  6. #6
    Join Date
    Jul 2010
    Posts
    82

    Re: Why would SQLDataReader close itself? Interesting.

    Quote Originally Posted by Arjay View Post
    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!

  7. #7
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: Why would SQLDataReader close itself? Interesting.

    Quote Originally Posted by CuteAssistant View Post
    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.
    Last edited by Arjay; September 28th, 2010 at 02:34 PM.

  8. #8
    Join Date
    Dec 2009
    Posts
    596

    Re: Why would SQLDataReader close itself? Interesting.

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

  9. #9
    Join Date
    Dec 2007
    Posts
    234

    Re: Why would SQLDataReader close itself? Interesting.

    @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 - 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
    * I don't respond to private requests for help. It's not conducive to the general learning of others.-I also subscribe to all threads I participate, so there's no need to pm when there's an update.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help - how to remove eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to???
    * On Error Resume Next is error ignoring, not error handling(tm). * Use Offensive Programming, not Defensive Programming.
    "There is a major problem with your code, and VB wants to tell you what it is.. but you have decided to put your fingers in your ears and shout 'I'm not listening!'" - si_the_geek on using OERN
    MVP '06-'10

  10. #10
    Join Date
    Jul 2010
    Posts
    82

    Re: Why would SQLDataReader close itself? Interesting.

    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.

  11. #11
    Join Date
    Jul 2010
    Posts
    82

    Re: Why would SQLDataReader close itself? Interesting.

    Quote Originally Posted by Arjay View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured