SQL connections not closing in sql server
Posted on 2004-10-12
I am having serious problems with a VB.NET application I am developing. I am using .net version 1.1.4 on a windows 2003 server and SQL Server 2000 with SP3.
My problem is that my connections to the database do not close when i use with a SQLDataReader. I am using <SQLDataReader>.close and <SQLConnection>.close when I am done with it.
I am using Microsoft DAAB v 2.0 for my database calls. I have also tried not using DAAB layer and done the normal way and had the same problem.
The main function is GetRelatedItemsDetail, which takes in the open connection and datareader BYREF, then executes reader and assigns it to the SqlDatareader passed in.
I open and close my connection using functions that take the connection in byref.
What am I missing here? I figure as long as I pass the connections and datareaders byref when i do a .close it will close the same one originally created.
Here is my code:
Dim myRelatedItemsDetail As SqlDataReader
Dim OpenSqlConnection As New SqlConnection
GetSqlConnection(OpenSqlConnection) 'open connection, in byref
GetRelatedItemsDetail(OpenSqlConnection, myRelatedItemsDetail, ItemID.ToString,1, 1)
Connection.CloseSqlConnection(OpenSqlConnection) 'close connection
Public Function GetRelatedItemsDetail(ByRef OpenSqlConnection As SqlConnection, ByRef myDataReader As SqlDataReader, ByVal ItemID As String, ByVal RelationTypeID As Integer, ByVal PublishStateID As Integer)
PublishStateID = GetPublishStateID(ItemID)
SQL = " sp_get_Stage_RelatedItemDetails '" & ItemID & "', " & RelationTypeID
myDataReader = SqlHelper.ExecuteReader(OpenSqlConnection, CommandType.Text, SQL)
Catch ex As Exception
Function GetSqlConnection(ByRef UnInitSqlConnection As SqlConnection)
'set and open connection
Function CloseSqlConnection(ByRef pConnection As SqlConnection)
pConnection = Nothing
Help! I hope I'm just being a retard am just missing something here! Either way 500 points to someone who helps me solve this problem!!