Scarlett72
asked on
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();
}
}
}ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Fernando
There's already a "close connection" in that code ; )
There's already a "close connection" in that code ; )
@Kaufmed missed the using statement, thanks for the update.
ASKER
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...
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.
connCode.Close();
ddlCodes.DataSource = lstCodes;