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