locked
Connection problem RRS feed

  • 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

    Thursday, July 4, 2013 4:47 AM

Answers

  • 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();
            }
        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 4, 2013 5:00 AM

All replies

  • 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();
            }
        }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 4, 2013 5:00 AM
  • 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.

     

     

    Thursday, July 4, 2013 5:02 AM
  • User-2101450227 posted

    Thanks Problem is solve Smile

    Thursday, July 4, 2013 5:05 AM