executeScalar method is giving error

Question

User34732094 posted

Hi All,

public string execCount(string query)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {

                SqlCommand cmd = new SqlCommand(query,con);
               cmd.Connection = con;
                string count = cmd.ExecuteScalar().ToString();
               

                return count;
            }
        }

It is giving me error saying that 

ExecuteScalar requires an open and available Connection. The connection's current state is closed. What is the error here?

Thanks

Answer 1

User-1330468790 posted

Hi entrylevel,

 

The error message has told you the exact error => The connection's current state is closed.

 

In your codes, you have to open the connection before you execute sql command.

Moreover, you don't need to set the property "Connection" of "cmd" to the connection variable "con" again as you have already passed "con" as a parameter of the constructor for SqlCommand.

public string execCount(string query)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
               // Open the connection
                con.Open();
                SqlCommand cmd = new SqlCommand(query,con); 
                string count = cmd.ExecuteScalar().ToString();               
                return count;
            }
        }

  

A further improvement of your codes would be adding exception handling try-catch since there are some exceptions that can be thrown from SqlCommand.ExecuteScalar Method. You can find them here: Exceptions of SqlCommand.ExecuteScalar Method

 

Hope helps.

Best regards,

Sean

Answer 2

User34732094 posted

Hi ,

 Thanks Sean. But what is the purpose of using block? Doesn't it take care of open and closing the connection?

Thanks

Answer 3

User1120430333 posted

The purpose of the 'using' block is to instance and object called 'con' as a SqlConnection instancing it as an object that lives in the computer's memory. It in no way opens the connection to the database, which is done by con.Open. The using block closes and disposes 'con' object when the code successfully executes or when the code throws an exception within the using block.

using statement - C# Reference | Microsoft Docs