Error adding connection in Server Explorer: "Unable to add data connection. ExecuteScalar requires an open and available connection."

I'm using Visual Studio 2008, and my database is SQL Server 2000.

I want to add a connection to the Server Explorer in VS. The Data source is Microsoft SQL Server (SqlClient). After entering in all my information and I click Test Connection, it is successful.

But when I click OK, I get the error:

Unable to add data connection. ExecuteScalar requires an open and available connection. The connection's current state is closed开发者_Go百科.

Restart Visual Studio. Then, restart your computer.

You can open Server Explorer (View -> Server Explorer) to re-connect the connection.

You may delete the current connection to open the same one again.

For those who are using a SQL Command and are getting the error "Unable to add data connection. ExecuteScalar requires an open and available connection. The connection's current state is closed." try this:

      using (SqlConnection conn = new SqlConnection(connString))
        {
            using (SqlCommand comm = new SqlCommand())
            {
                // query to select all the rows whose column name is the same as id
                comm.CommandText = "SELECT COUNT(*) from tableName where colName like @val1";
                comm.Connection = conn;
                conn.Open();  // <---- adding this line fixed the error for me
                comm.Parameters.AddWithValue("@val1", id);
                // retrieve how many rows are returned after executing the query
                count = (int)comm.ExecuteScalar();  // < --- where the error originally occurred
            }
        }