locked
executeScalar method is giving error RRS feed

  • 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

    Sunday, May 30, 2021 10:44 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 31, 2021 6:03 PM

All replies

  • 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

    Monday, May 31, 2021 2:50 AM
  • 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

    Monday, May 31, 2021 3:53 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 31, 2021 6:03 PM