ADO/ADO.NET - Error while executing EXECUTESCALAR sql command

Asked By Rajendra Reddy on 22-Apr-14 09:38 AM
I get following error when I execute following piece of code.

Error : "executescalar requires the command to have a transaction when the connection assigned to the command is in a pending local transaction"

Code: public int GetStudentId(int studid)
         {
            try
            {
                int studentId= 0;
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.StoredProcedure;
                    if (connection.State != ConnectionState.Open) { connection.Open(); }

                    command.CommandText = "TestStoredProcedure";
                    command.Parameters.Add(new SqlParameter("@StudentId", studid));
                    Object temp = new Object();
                    temp = command.ExecuteScalar();
                    if (temp != null)
                    {
                        studentId= (int)command.ExecuteScalar();
                    }
                    else
                    {
                        studentId= 0;
                    }
                }
                return studentId;
            }
            finally
            {
                if (connection.State == ConnectionState.Open) { connection.Close(); }
            }
        }
I get error only in few cases, sometime it works fine. I am not getting how to track the exact issue? Can any one help me to identify the exact issue?? Is there is any issue with code??

Thanks in advance 

Hemanth Kumar replied to Rajendra Reddy on 28-Apr-14 05:43 AM
You have to assign the Transaction object to the SqlCommand Object. Please check this example http://stackoverflow.com/questions/10648718/executenonquery-requires-the-command-to-have-a-transaction-error-in-my-code
Rajendra Reddy replied to Hemanth Kumar on 28-Apr-14 07:56 AM
Hello Hemanth,

Thanks for your reply.
Reason for the issue was SQL connection with begin transaction was not committed & closed in other method, as result when execution flow come to my method same earlier SQL connection (Of previous method) was getting used.
Hence I was getting error.

Regards,
Rajendra