ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
What am I doing wrong here? I'm assuming you can reuse the connection?
Thanks for any help!
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString()))
{
cn.Open();
// If we are reverting to an old type
if (pageAction == "revert")
{
debug.Text = "FLAG 1";
// Get the revert ID
int revertingID = int.Parse(Request.QueryString["revID"]);
bool rowsReturned = false;
debug.Text = "FLAG 2 - " + revertingID.ToString();
// Set all to 0
using (SqlCommand cmd = new SqlCommand("SELECT ID FROM tblSiteSettings WHERE ID = " + revertingID, cn))
{
// If it exists
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (rdr.Read())
{
rowsReturned = true;
}
rdr.Close();
}
debug.Text = "FLAG 3 - " + rowsReturned.ToString();
// Set new active and reset others
if (rowsReturned == true)
{
using (SqlCommand cmd = new SqlCommand("UPDATE tblSiteSettings SET isActive = 1 WHERE ID = " + revertingID, cn))
{
cmd.ExecuteNonQuery();
}
using (SqlCommand cmd = new SqlCommand("UPDATE tblSiteSettings SET isActive = 0 WHERE ID <> " + revertingID, cn))
{
cmd.ExecuteNonQuery();
}
}
//debug.Text = "FLAG 4 - ";
}
Best Answer
Your problem is:
You should just call
cmd.ExecuteReader()'
if you want to use the connection again prior to "getting rid" of it. If you want to get an understanding for what theCommandBehaviour.CloseConnection
part does/means then the documentation for SqlCommand.ExecuteReader is a good bet. There's also documentation to tell you what all the possible values of the CommandBehaviour enumeration are. EssentiallyCommandBehaviour.CloseConnection
does the following:If you have no special need to specify a CommandBehaviour, then either specify
CommandBehaviour.Default
, or don't specify one at all. CommandBehaviour.Default is: