ExecuteReader requires an open and available Connection. The connection's current sta

[vuyiswamb]

Good Day All

i have the Following Function

private static void OMEGA_calcActvEqv()
    {
        SqlConnection conn1 = CommonFunctions.getSQLConnectionForThread(THREAD_DATA[0].ToString());
        string SQL = "SELECT MAX(D.OFFS + 1) * MAX(R.OFFS + 1) FROM TBL_ROWS R, TBL_CLMN D; SELECT MAX(OFFS + 1) FROM TBL_ROWS;";
        SqlCommand cmd = new SqlCommand(SQL, conn1);
        SqlDataReader rdr = cmd.ExecuteReader();

        int size = 0;
        if (rdr.Read())
        {
            size = Convert.ToInt32(rdr[0]);
        }

        int maxPeriods = 0;
        if (rdr.NextResult())
        {
            if (rdr.Read())
            {
                maxPeriods = Convert.ToInt32(rdr[0]);
            }
        }
        rdr.Close();

        SQL = "SELECT MEA.EQV [EQV], MEA.ACTV [ACTV] FROM MTM_EQV_ACTV MEA ORDER BY MEA.EQV, MEA.ACTV ";

        if ((THREAD_DATA.Length > 3) && (THREAD_DATA[3] != null))
        {
            string EQV = CommonFunctions.ExecuteScalarInThread("SELECT MEA.EQV FROM MTM_EQV_ACTV MEA WHERE ACTV=" + THREAD_DATA[3], conn1).ToString();
            SQL = "SELECT MEA.EQV [EQV], MEA.ACTV [ACTV] FROM MTM_EQV_ACTV MEA WHERE EQV='" + EQV + "' ORDER BY MEA.EQV, MEA.ACTV ";
        }

        cmd = new SqlCommand(SQL, conn1);
       <b> rdr = cmd.ExecuteReader();</b>

        string eqvSet = string.Empty;
        List&lt;int&gt; actvs = new List&lt;int&gt;();

        while (rdr.Read())
        {
            if (eqvSet != rdr["EQV"].ToString())
            {
                if (actvs.Count &gt; 1)
                {
                    OMEGA_intersectDomns(actvs, size, maxPeriods);
                }

                actvs.Clear();
                eqvSet = rdr["EQV"].ToString();
                if (!DBNull.Value.Equals(rdr["ACTV"]))
                {
                    actvs.Add(Convert.ToInt32(rdr["ACTV"]));
                }
            }
            else
            {
                if (!DBNull.Value.Equals(rdr["ACTV"]))
                {
                    actvs.Add(Convert.ToInt32(rdr["ACTV"]));
                }
            }
        }

        rdr.Close();

        if (actvs.Count > 1)
        {
            OMEGA_intersectDomns(actvs, size, maxPeriods);
            actvs.Clear();
        }

        conn1.Close();
        conn1.Dispose();
    }

and the Definition of function ExecuteScalarInThread

is

public static object ExecuteScalarInThread(string sql, SqlConnection conn1)
    {
        
        SqlCommand sqlcommand = new SqlCommand(sql, conn1);
        //You need to apply the the connection to the command after connection open
        sqlcommand.CommandType = CommandType.Text;

        object ret = new object();

        if (conn1.State != ConnectionState.Open)
        {
            conn1.Open();
        }
        try
        {
            ret = sqlcommand.ExecuteScalar();
        }
        catch (Exception e)
        {
            ret = e.Message;
        }

        conn1.Close();
        return ret;
    }

i get an Error in the bolded line of the Function OMEGA_calcActvEqv() on this line

rdr = cmd.ExecuteReader();

that says

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

Thanks

[dickersonka]

you need to have an open connection, just as it says, right before you call execute reader call this

if (conn1.State != ConnectionState.Open)        
{            
conn1.Open();        
}

also it would be a good idea to create a separate class or method for calling all sql functions, that way you don't have to worry about closing and opening except in a single place

[mikiurban]

To add to what dickersonka said, I would create a ExecuteReaderInThread() function, similar to your ExecuteScalarInThread() function, that returns a data reader. If you put the check in there (just like you have one in ExecuteScalarInThread()), you should be fine.

[vuyiswamb]

Thank yo very much guys. The Problem was that i was Closing the connection in the function ExecuteScalarInThread

Thanks