Error in server 'executenonquery requires an open and available connection'

Question

I got this error on server not in local and when facing this error, then i re-upload that related class file. after doing this problem solved but not permanently.

Error:

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

Code:

int n;

try
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = DataConnection.Con;
        cmd.CommandText = "sp_InsertUpdateDeleteValidationDate";
        cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0;
        cmd.Parameters.AddWithValue("@Task", "CheckExist");
        cmd.Parameters.AddWithValue("@id", 0);
        cmd.Parameters.AddWithValue("@AdId", "");
        cmd.Parameters.AddWithValue("@Username", "");
        cmd.Parameters.AddWithValue("@DOE", DOE);
        cmd.Parameters.AddWithValue("@ExpieryDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@DOR", DateTime.Now);
        cmd.Parameters.Add("@flag", SqlDbType.Int).Direction = ParameterDirection.Output;

        if (cmd.Connection.State  == ConnectionState.Closed)
        {
            cmd.Connection.Open();
        }
        cmd.ExecuteNonQuery();
        n = Convert.ToInt32(cmd.Parameters["@flag"].Value);
        return n;
    }
}
catch (SqlException Ex)
{

    return 0;
}

this may help http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren — lockstock, Nov 10 '14 at 05:54
public class DataConnection { private static SqlConnection SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString); public static SqlConnection Con { get { return SqlCon; } } } — Boktiar, Nov 10 '14 at 05:54

lockstock · Accepted Answer · 2014-11-10T22:31:17.187

4

You only create one connection in your DataConnection class. You should create a new connection for each database call and let the driver's connection pooling take care of efficiently reusing them.

change your DataConnection class to this:

public class DataConnection
{
    public static SqlConnection Con
    {
        get 
        { 
            return new SqlConnection(ConfigurationManager
                .ConnectionStrings["conn"].ConnectionString); 
        }
    }
}

and use a using statement when you use the connection like in ekad's answer:

using (SqlConnection conn = DataConnection.Con)
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        //use the command here
    }
}

edited Nov 10 '14 at 22:31

answered Nov 10 '14 at 06:00

lockstock

2,359
3
23
39

1

Could you please explain this a bit further? I have a similar situation, however, the exception has only been reported once. I have even created a test application which deletes and reinsterts the same record 1000 times without closing the connection and not encountered the error. Is it that this would only occur occasionally - i.e. if multiple database calls are made using a single open connection - and if so, why? – komodosp Sep 04 '17 at 11:05

score 0 · Answer 2 · answered Nov 10 '14 at 05:54

Looks like your SqlConnection is never closed. Try to use using statement to make sure that the SqlConnection is closed after executing cmd.ExecuteNonQuery()

int n;

try
{
    using (SqlConnection conn = DataConnection.Con)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "sp_InsertUpdateDeleteValidationDate";
            cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 0;
            cmd.Parameters.AddWithValue("@Task", "CheckExist");
            cmd.Parameters.AddWithValue("@id", 0);
            cmd.Parameters.AddWithValue("@AdId", "");
            cmd.Parameters.AddWithValue("@Username", "");
            cmd.Parameters.AddWithValue("@DOE", DOE);
            cmd.Parameters.AddWithValue("@ExpieryDate", DateTime.Now);
            cmd.Parameters.AddWithValue("@DOR", DateTime.Now);
            cmd.Parameters.Add("@flag", SqlDbType.Int).Direction = ParameterDirection.Output;

            conn.Open();        
            cmd.ExecuteNonQuery();
            n = Convert.ToInt32(cmd.Parameters["@flag"].Value);
            return n;
        }
    }
}
catch (SqlException Ex)
{

    return 0;
}

Error in server 'executenonquery requires an open and available connection'

2 Answers2