Object Disposal Problem

[robulator]

In my code I have several objects that need a connection to SQL Server. So I have a Connection object that creates a shared connection when instantiated. Each page creates one of these objects which opens the connection. When the other objects create one of these connection objects, the connection is shared (rather than creating a new connection). It works fine on the first page, but going to the next page generates an error. If I implement the IDisposable interface to dispose of the connection when I'm done with it, I get the following error: "Object reference not set to an instance of an object". If I don't dispose of the connection object, I get the following error: "Not allowed to change the 'ConnectionString' property while the connection (state=Open)".

Any ideas on how to share the connection without generating errors?

Thanks!

Rob

 

[link9]

When you refer to a "shared" connection, exactly what method are you employing to "share" the connection?

Some relevant code might be in order, also.

The answer to getting answered -- faq855-2992

 

[robulator]

Here is part of the code below. Note that the connection in the class clsConnection is shared:

Class clsConnection

Implements IDisposable

Private ConnectionString As String
Public Shared connOCD As New SqlConnection()

Sub New(ByVal Test As String)

ConnectionString = "Data Source=TestSQL; Initial Catalog=TestDB; User ID=ABC; Password=xyz"

Try
connOCD.Open()
Catch
HandleDBError()
End Try
End Sub

Sub New()

'Do nothing

End Sub

Sub Dispose() Implements IDisposable.Dispose
connOCDOnline.Close()
connOCDOnline = Nothing
End Sub



End Class


Class clsUser

Implements IDisposable

Private connOCDOnline As SqlConnection
Private objConnection As New clsConnection()

Sub New()

connOCDOnline = objConnection.connOCD
cmdUser.Connection = connOCDOnline

End Sub

Sub Dispose() Implements IDisposable.Dispose
connOCDOnline.Close()
connOCDOnline = Nothing
End Sub

End Class


On an ASP page where I want to create a User object, I do the following:

Dim objConnection As New clsConnection(Session("Test&quot)
Dim objUser As New clsUser()


And when I'm done with the User object, I do the following:

objUser.Dispose()
objUser = Nothing
objConnection = Nothing

As described in the first message, I get an error whether or not I dispose of the Connection or the User object. I think there is something fundamental I am missing.

Any help appreciated!!

Rob