CODEWITHSUNDEEP
c#sqlexecutescalar
CsharpBeginner
CsharpBeginner

Reputation: 1773

Order of executions or ExecuteScalar issue

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; 

   }

Upvotes: 0

Views: 571

Answers (3)

Syeda
Syeda

Reputation: 1205

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.

Upvotes: 0

Damir Arh
Damir Arh

Reputation: 17855

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

Upvotes: 1

Anthony Pegram
Anthony Pegram

Reputation: 126864

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.

Upvotes: 0

Related Questions