executereader requires an open and available connection. the connection's current state is closed.

Question

_{Friday, August 9, 2019 10:22 AM}

Hi All,

I have the following code that calls a Stored Procedure, which keeps causing a " executereader requires an open and available connection. the connection's current state is closed." and "BeginExecuteNonQuery requires an open and available Connection. The connection's current state: Broken."

using (SqlCommand cmd = new SqlCommand("AddLog", instancesql.SqlConnection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (instancesql.SqlConnection.State == ConnectionState.Closed)
                        instancesql.SqlConnection.Open();

                    cmd.Parameters.Add("@Id", SqlDbType.UniqueIdentifier).Value = id;
                    cmd.Parameters.Add("@LogType", SqlDbType.VarChar).Value = logtype;

                    cmd.ExecuteNonQuery();
                }

However it has inserted 2k records out of 40k records, so it can't be the connection string. Also another thing to note, this only happens when I run against a SQLExpress, It didn't happen when I was using SQL Server.

Regards,

Loftty

All replies (2)

_{Friday, August 9, 2019 10:39 AM}

Hello,

Two things to try

• For the connection object use a fresh connection object with a using statement.
• Try changing the Packet Size part of the connection string.

There may also be setting for Express which I don't know about since it doesn't happen with SQL-Server edition.

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

_{Wednesday, September 11, 2019 1:41 PM}

I see a problem with your code: It says "if the connection state is Closed then Open the connection".

However, according to the error message, the state of the connection is "Broken" (not "Closed"). Therefore, it doesn't get opened, and the Execute cannot proceed.

You could change the "if" to: if (instancesql.SqlConnection.State != ConnectionState.Open)

But it's better to do as Karen said and use a fresh connection for each execution. This will not hurt performance, because the new connection is not actually created anew; instead it is internally managed by the Connection Pool and you simply get back an existing connection from the Pool, which is a very fast operation.