It is not allowed to modify the ConnectionString property. The state of the current connection is open
First user of stackoverflow, but I've been following its development on Coding Horror.
I'm having a massive headache with the above error. I have ELMAH and Google Analytics installed and as my site traffic increases the number of times I've seen this error.
I have done my best to follow the Microsoft guidelines: http://msdn.microsoft.com/en-us/library/ms971481.aspx throughout the development, and I have optimized my code as much as possible based on several online consultation sources.
I have a 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 the same as here:
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 everywhere and it works well most of the time, but now the site is getting terribly busy, the drama has begun! Does anyone have any ideas?
I'd rather not rewrite mountains of code, but I'm open to suggestions.
:)
Chris
a source to share
Problem with shared connections.
No need to swap connections and create problems like the ones you are experiencing..net integrates the exchange of 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
a source to share
There are so many problems with your code. I don't know where to start.
First, I am assuming that pitstop
is the actual name of the class, which is terrible. Second, pitstop.doMakeReadyForHTML
makes me think that this class contains all sorts of functionality that doesn't actually belong.
Third, and most terrible, is your SqlConnection
object Shared
. And this is in a web application that is multithreaded by its very nature.
Fourth, your database access logic is on your code page, which is bad.
Fifth, you are not managing your resources properly. What if the exception occurs before the call pitstop.doGarbage()
(terrible name, by the way)? The connection will either never close or it will hang and cause all kinds of errors.
I suggest the following. Understand design patterns and software architecture in general.Then you yourself will see all your problems. Then move on to Enterprise Application Architecture Patterns : this will give you a solid foundation to build.
Regarding your current code: un Shared
your connection, move your data access logic to a separate class, manage resources properly (think Usage ).
a source to share
I added the following code by removing the "shared" connection from the pitstop class. Pitstop is called "pitstop" because it is the name of the project. Doesn't he fit? What conventions are you using?
I broke the doGarbage () function and now I call the connection and the one-off 'inline' like:
Private Sub doPump()
Dim oConn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("pitstopConnectionString").ConnectionString)
Dim cmd As New System.Data.SqlClient.SqlCommand("doGetCategory", oConn)
oConn.Open()
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()
oConn.Dispose()
End Sub
Is it correct?
Also, Anton mentioned that the functionality inside the class is wrong - I will read the related docs, but is there a quick answer as to why this is wrong?
Thanks a lot to all the answers - you were all very helpful.
Chris
a source to share