Connection problem

Question

User-2101450227 posted

i am using 3 tier archit i am having error

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

this is my coding

public bool Save()
{
try
{
mCommand = new System.Data.SqlClient.SqlCommand();
mCommand.CommandText = "abc";
mCommand.CommandType = CommandType.StoredProcedure;
mCommand.Connection = mConnection;

mCommand.Parameters.AddWithValue("@cid", cid);
mCommand.Parameters.AddWithValue("@pincode", pincode);
mCommand.Parameters.AddWithValue("@checktime", checktime);
mCommand.Parameters.AddWithValue("@status", status);
mCommand.Parameters.AddWithValue("@br_code", br_code);
mCommand.Parameters.AddWithValue("@ebid", ebid);

if (mConnection.State == ConnectionState.Closed)
{
OpenConnection();
}
RowsAffected = (int)mCommand.ExecuteNonQuery();

CloseConnection();

if (RowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
catch (SqlException Error)
{
CloseConnection();
throw Error;
}
}

the line which is under line showing error

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

please help me out i am new in this profession

thank you

Answer 1

User-1716253493 posted

    public bool Save()
    {
        try
        {
            mCommand = new System.Data.SqlClient.SqlCommand();
            mCommand.CommandText = "abc";
            mCommand.CommandType = CommandType.StoredProcedure;
            mCommand.Connection = mConnection;

            mCommand.Parameters.AddWithValue("@cid", cid);
            mCommand.Parameters.AddWithValue("@pincode", pincode);
            mCommand.Parameters.AddWithValue("@checktime", checktime);
            mCommand.Parameters.AddWithValue("@status", status);
            mCommand.Parameters.AddWithValue("@br_code", br_code);
            mCommand.Parameters.AddWithValue("@ebid", ebid);
            OpenConnection();
            RowsAffected = (int)mCommand.ExecuteNonQuery();
            if (RowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (SqlException Error)
        {           
            throw Error;
        }
        finally
        {
            CloseConnection();
        }
    }

Answer 2

User351203563 posted

For trial and error : Instead of checking for close can you try doing as below

if (mConnection.State != ConnectionState.Open) {

try{

OpenConnection();

}

catch{}

}

Also check a few other things like if database is up and running, connection string is correct etc.

 

 

Answer 3

User-2101450227 posted

Thanks Problem is solve