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

Question

I am getting an error:

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

I have used the con.Open();, but I'm getting the same error.
Please help me on this.

static string connectionString = ConfigurationManager.AppSettings["MessageDB"].ToString();
static SqlConnection _sqlconn = new SqlConnection(connectionString);

public static void Message(MessageLog messageLog)
{
    try
    {
        _sqlconn = new SqlConnection(connectionString);
        _sqlconn.Open();

        SqlCommand cmd = _sqlconn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "INSERT_MESSAGE";

        SqlParameter APPID = cmd.CreateParameter();
        APPID.ParameterName = @"@MessageID";
        APPID.DbType = DbType.Int16;
        APPID.Direction = ParameterDirection.Input;
        APPID.Value = messageLog.MessageID;
        cmd.Parameters.Add(MessageID);

        cmd.ExecuteNonQuery();

        _sqlconn.Close();
    }
    catch (Exception ex)
    {
        if (_sqlconn.State == ConnectionState.Open)
        {
            _sqlconn.Close();
        }

        ExceptionLogger.LogException(ex);
    }
}

This may help https://stackoverflow.com/questions/7201754/executenonquery-requires-an-open-and-available-connection-the-connections-curr — IronMan, Jul 25 '19 at 17:48
IMHO, wrap the `Command` and `SqlConnection` in `Using` statements; then your object will get disposed when done with them... — Trevor, Jul 25 '19 at 18:18

score 0 · Answer 1 · answered Jul 25 '19 at 19:23

IMHO Your problem is using static connection variable. static SqlConnection _sqlconn = new SqlConnection(connectionString); and then you assigned it again which is doesn't make any sense. I would change your code a bit:

static string connectionString = ConfigurationManager.AppSettings["MessageDB"].ToString();
public static void Message(MessageLog messageLog)
{
   using(var _sqlconn = new SqlConnection(connectionString))
   {
      using(var cmd = new SqlCommand())
      {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = "INSERT_MESSAGE";
          cmd.Connection = _sqlconn;

          cmd.Parameters.Add("@MessageID", DbType.Int16).Value = messageLog.MessageID;
          try
          {
              if(_sqlconn.State == ConnectionState.Closed) _sqlconn.Open()

              cmd.ExecuteNonQuery();

          }
          catch (Exception ex)
          {
               ExceptionLogger.LogException(ex);
          } 
          finally
          { 
               if (_sqlconn.State == ConnectionState.Open)
               {
                  _sqlconn.Close();
               }
          }
      }
   }
}

Benefit of that syntax?

All disposable will be disposed.
Connection will be always closed regardless of exceptions or normal execution flows.
No attempts to open already open connections.
Connection always available because it's taken from connection pool every time and not "freeze" for static variable.

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

1 Answers1