Solved

How to open connection for datareader

Posted on 2014-11-21
6
97 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

815 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

12 Experts available now in Live!

Get 1:1 Help Now