Order of executions or ExecuteScalar issue

Question

First im inserting a new member into the members table. Then im querying the table to get back the Member id. I get the data into the table, but it does not apear there quick enough to do the query in the following lines.

I get this exception "ExecuteScalar requires an open and available Connection. The connection's current state is closed." I cant figure out whats wrong here.

 //This code works fine
 //Insert new members data
 InsertMembers insert = new InsertMembers();
 int age = Int32.Parse(txtAge.Text);
 insert.InsertNewMember(txtEmail.Text, Myguid, txtName.Text, txtCity.Text, txtState.Text, txtDescription.Text, age, gender);

 //This is the block thats failing
 //Get Member Id to Insert into Pictures table
 GetMemberInfo GetID = new GetMemberInfo();
 int UMemberId = GetID.GetMemberId(Myguid);
 Displayme.Text = UMemberId.ToString();



 public int GetMemberID(string guid)
   {
       string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
       string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";

       int memberId;
       using (var connection = new SqlConnection(strConectionString))
       using (var command = new SqlCommand(StrSql, connection))
       {
           command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
           memberId = (int)command.ExecuteScalar();
       }
       //returns 0 when it should be member id number
       return memberId; 

   }

score 1 · Answer 1 · answered Dec 03 '11 at 04:57

You should call connection.Open(), before executing the command:

public int GetMemberID(string guid)
{
    string strConectionString = ConfigurationManager.AppSettings["DataBaseConnection"];
    string StrSql = "SELECT MemberID FROM MEMBERS WHERE (Guid = @GuidID)";

    int memberId;
    using (var connection = new SqlConnection(strConectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(StrSql, connection))
        {
            command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
            memberId = (int)command.ExecuteScalar();
        }
    }

    //returns 0 when it should be member id number
    return memberId; 
}

score 0 · Answer 2 · answered Dec 03 '11 at 04:54

Read the error message very carefully. It has nothing to do with ExecuteScalar being too quick, nor does it have to do with order of operations, except there is specifically an operation missing. You have not opened the connection.

Toss in a connection.Open(); within the scope of the using blocks prior to the ExecuteScalar invocation and you should experience a different outcome.

score 0 · Answer 3 · answered Dec 03 '11 at 05:07

Replace your these lines of code

  using (var connection = new SqlConnection(strConectionString))
       using (var command = new SqlCommand(StrSql, connection))
       {
           command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid; 
           memberId = (int)command.ExecuteScalar();
       }

with these

   using (SqlConnection connection = new SqlConnection(
               strConectionString))
    {
        SqlCommand command = new SqlCommand(StrSql, connection);
         command.Parameters.Add("@GuidID", SqlDbType.VarChar).Value = guid;
        command.Connection.Open();
        memberId = (int)command.ExecuteScalar();
    }

using statement is used to dispose the connection automatically and i don't think here is need to apply using with sql command when you have already applied it on SqlConnection. And you have missed to open the connection before executing the command.

There are views both for and against, but as a general rule of thumb you might say "if it implements IDisposable *(and you created it)* call Dispose" - so the original code is correct. — Black Light, Dec 07 '11 at 15:41

Order of executions or ExecuteScalar issue

3 Answers3