Not allowed to change the 'ConnectionString' property. The connection's current state is open

Question

First time user of stackoverflow, but I have followed its development over on Coding Horror.

I am having a massive headache with the above error. I have ELMAH installed and Google Analytics and as the site traffic has increased, so has the number of times I have seen this error.

I have done my best to follow the Microsoft principles: http://msdn.microsoft.com/en-us/library/ms971481.aspx throughout development and I've optimised my code as much as possible based on multiple sources of advice across the web.

I have my SqlConnection in a public class;

Public Class pitstop
Public Shared oConn As New System.Data.SqlClient.SqlConnection
    Public Shared Sub doConnect()
    If oConn.State = ConnectionState.Closed Then
        oConn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("pitstopConnectionString").ConnectionString
        oConn.Open()
    End If
End Sub
Public Shared Sub doGarbage()
    oConn.Dispose()
End Sub
' /// other code ///
End Class

And in my main application pages, I do much the same as this:

 Private Sub doPump()
    pitstop.doConnect()
    Dim cmd As New System.Data.SqlClient.SqlCommand("doGetCategory", pitstop.oConn)
    Dim dt As New DataTable
    Dim dr As SqlDataReader

    cmd.Parameters.Add("@cat", SqlDbType.Int)
    cmd.Parameters("@cat").Value = CType(Request.QueryString("id"), Integer)

    cmd.CommandType = CommandType.StoredProcedure

    dr = cmd.ExecuteReader()
    While dr.Read()
        If dr.HasRows = True Then
            litCategory.Text = dr("category")
            litCategoryDesc.Text = pitstop.doMakeReadyForHTML(dr("desc"))
        End If
    End While
    cmd = Nothing
    dr.Close()
    pitstop.doGarbage()
End Sub

I've used this method throughout and most of the time it works well, but now the site is getting horrifically busy, the dramas have begun! Does anyone have any ideas?

I would prefer not to have to re-write mountains of code, but I'm open to suggestions.

:)

Chris

Answer 1

Sharing your connections is the problem.

There is no need to share connections and creates problems like you are experiencing. .net's connection pooling handles the sharing of the real connections behind the scenes.

Just create a new connection in doPump()

Private Sub doPump()
    Using Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("pitstopConnectionString").ConnectionString)
        Using Dim cmd As New SqlCommand("doGetCategory", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@cat", CType(Request.QueryString("id"), Integer))
        conn.Open()
        Using Dim dr as SqlDataReader = cmd.ExecuteReader()
            While dr.Read()
                    litCategory.Text = dr("category")
                    litCategoryDesc.Text = pitstop.doMakeReadyForHTML(dr("desc"))
            End While
            dr.Close()
        End Using
    End Using
End Sub