ExecuteReader requires an open and available Connection Asp.net

Question

I have an error in my website when it is on the server.

System.Data ExecuteReader requires an open and available Connection. The connection's current state is open

private static int OpenCon()
    {
        int degel = 0;
        try
        {
            ResetCon(true);
            if (con.State != ConnectionState.Open)
                con.Open();
            degel = -1;
        }
    catch (System.Data.SqlClient.SqlException ex)
    {
        CloseCon();
        if (ex.Errors[0].Number == 53)
        {

        }
    }
    return degel;
}

private static void CloseCon()
{
    if (con.State != ConnectionState.Closed)
        con.Close();
    if (cmd == null) 
        return; 
    else 
        cmd.Dispose();
}

private static SqlCommand CreateCmd(string sql, CommandType type)
{
    SqlCommand cmd = new SqlCommand();
    try
    {
        cmd.Connection = con;
        cmd.CommandType = type;
        cmd.CommandText = sql;
        cmd.CommandTimeout = 300;
    }
    catch
    {
        CloseCon();
    }
    return cmd;
}

 public static DataTable GetTable(string sql)
        {
            DataTable dt = new DataTable();
            try
            {
                OpenCon();
                SqlCommand cmd = CreateCmd(sql, CommandType.Text);
                adpt = new SqlDataAdapter();
                adpt.SelectCommand = cmd;
                adpt.Fill(dt);
                CloseCon();
            }
            catch (Exception ex)
            {
                CloseCon();
                Log.WriteLog("DataBase.GetTable()", ex, HttpContext.Current.Request);
            }
            return dt;
        }

Thanks, Hani

score 1 · Accepted Answer · edited May 23 '17 at 12:21

I assume this error is not reproducible on your development PC, is it?

You should not use static connections, still less in ASP.NET. Your DB-class is just a source of nasty errors like this. Instead use the using-statement for everything that implements IDisposable like the connection to ensure that it gets closed as soon as possible (even on error).

I don't know what i could add since this seems to be a duplicate of ...

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

ExecuteReader requires an open and available Connection Asp.net

1 Answers1