ExecuteNonQuery requires an open and available Connection. The connection's current state is closed

Question

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 - ";
    }

For anyone googling the German error message, it e. g. reads: "**'ExecuteNonQuery erfordert eine geöffnete und verfügbare Connection. Der aktuelle Status der Verbindung ist 'Geschlossen'.**'". — Uwe Keim, May 02 '18 at 13:44

score 20 · Accepted Answer · edited Jun 20 '20 at 09:12

20

Your problem is:

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

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 the CommandBehaviour.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. Essentially CommandBehaviour.CloseConnection does the following:

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

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:

The query may return multiple result sets. Execution of the query may affect the database state. Default sets no CommandBehavior flags, so calling ExecuteReader(CommandBehavior.Default) is functionally equivalent to calling ExecuteReader().

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 23 '10 at 13:41

Rob

45,296
24
122
150

Does the Using() statement his commands are wrapped in close the connection as well when it disposes the SqlCommand? – Tommy Aug 23 '10 at 13:52
@Tommy, not as far as I'm aware - the documentation for SqlCommand.Dispose doesn't call it out, so I'd assume not (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.dispose.aspx) – Rob Aug 23 '10 at 13:58
Good call, if the connection definition was wrapped in a Using block, then it would. http://stackoverflow.com/questions/410222/does-connection-close-when-command-is-disposed-and-the-connection-is-defined-dire – Tommy Aug 23 '10 at 14:06
1

Thank you so very much. I had several instances, but they were not using this, except ONE, only ONE, and that darn thing had been driving me nuts for the last few hours. Thank you so very much. – Wayne Barron Jan 09 '23 at 07:37

score 5 · Answer 2 · answered Aug 23 '10 at 13:39

5

You're closing the connection rdr.Close(); and never re-opening it before calling ExecuteNonQuery().

You don't actually need to close it at all if it's wrapped in a using as the call to Dispose() will automatically close the connection for you.

answered Aug 23 '10 at 13:39

Dave D

8,472
4
33
45

Tommy · Answer 3 · 2010-08-23T13:53:21.923

It appears you are doing a read before doing your ExecuteNonQuery. In your first call to SqlCommand (for the SELECT), you are closing the connection after the read is complete.

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

Remove the command behavior, you should be good to go, or re-open the connection in your next if statement.

This

SqlDataReader rdr = cmd.ExecuteReader();

Or this

if (rowsReturned == true){
   cn.open();

score 3 · Answer 4 · answered Feb 15 '16 at 10:56

3

Just add cn.Open before, or do not close it.

answered Feb 15 '16 at 10:56

Vladyslav Furdak

1,765
2
22
46

score 1 · Answer 5 · edited Apr 26 '12 at 08:20

Right here, your SqlDataReader will close the connection when it completes:

// 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(); 
}

Later, the "Set new active and reset others" section will fail, because the connection is closed.

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed

5 Answers5

Linked

Related