Solved

How to open connection for datareader

Posted on 2014-11-21
6
94 Views
Last Modified: 2014-11-21
ExecuteReader requires an open and available Connection. The connection's current state is closed.

protected void fill_ddlCodes()
        {
            using (SqlConnection connCode = new SqlConnection(str1964))
            {
                using (SqlCommand cmdCode = new SqlCommand("Select * from view_tbl_codes", connCode))
                {
                    SqlDataReader drCodes;
                    //Getting exception error here...
                    drCodes = cmdCode.ExecuteReader();
                    
                    List<string> lstCodes = new List<string>();
                    foreach(DataRow dr in drCodes)
                    {
                        lstCodes.Add(drCodes["Code"].ToString());
                    }
                    ddlCodes.DataSource = lstCodes;
                    ddlCodes.DataValueField = "Code";
                    ddlCodes.DataTextField = "Code";
                    ddlCodes.DataBind();
                }
            }            
        }

Open in new window

0
Comment
Question by:Scarlett72
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 40457764
Change this line:

drCodes = cmdCode.ExecuteReader();

...to this:

connCode.Open();
drCodes = cmdCode.ExecuteReader()

Open in new window

0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40457777
You should also have a close connection before leaving the code, something like this.

connCode.Close();
ddlCodes.DataSource = lstCodes;
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40457780
@Fernando

There's already a "close connection" in that code  ; )
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40457786
@Kaufmed missed the using statement, thanks for the update.
0
 

Author Comment

by:Scarlett72
ID: 40457822
Hi Fernando / Kauffmed, thank you both again for your assistance.  The 'open' method on the datareader worked, but I thought because I put my reader in a connection dispose method I didn't have to open or close the connections...
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 40457837
You don't have to close it--the using takes care of that--but you do have to open it. If you think about it, it kind of makes sense:  Once you dispose of the connection object, you really don't need it anymore, so you implicitly shouldn't need the underlying connection TCP connection either; but in terms of opening the connection, you may need to do some setup first, potentially a lengthy operation. From a code perspective, you're not always guaranteed at what exact point you will open a connection, but you should always know when you no longer need it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now