Connection problem[Answered] RSS

• • Reply
  immad

  Member

  49 Points

  311 Posts

  Connection problem

  Jul 04, 2013 04:47 AM|immad|LINK

  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

• • Reply
  oned_gk

  All-Star

  52523 Points

  15672 Posts

  Re: Connection problem

  Jul 04, 2013 05:00 AM|oned_gk|LINK

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

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  susantamukhe...

  Member

  160 Points

  46 Posts

  Re: Connection problem

  Jul 04, 2013 05:02 AM|susantamukherjee|LINK

  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.

   

   

• • Reply
  immad

  Member

  49 Points

  311 Posts

  Re: Connection problem

  Jul 04, 2013 05:05 AM|immad|LINK

  Thanks Problem is solve