How to open connection for datareader

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

Scarlett72Asked:
Who is Participating?
 
käµfm³d 👽Commented:
Change this line:

drCodes = cmdCode.ExecuteReader();

...to this:

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

Open in new window

0
 
Fernando SotoRetiredCommented:
You should also have a close connection before leaving the code, something like this.

connCode.Close();
ddlCodes.DataSource = lstCodes;
0
 
käµfm³d 👽Commented:
@Fernando

There's already a "close connection" in that code  ; )
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Fernando SotoRetiredCommented:
@Kaufmed missed the using statement, thanks for the update.
0
 
Scarlett72Author Commented:
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
 
käµfm³d 👽Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.