DB connection is not released.

[Helene Day]

Hi everyone,
I am using this code to access the database.... and after the method is
called... the database connection is not released. The connection is only
released when the application terminates. I used to only close the
connection, now I even called the dispose method of both the SQLCommand and
the SQLConnection objects. what else is missing ???



Private Function CreateCall(ByVal SQL As String) As Double

Dim objCon As SqlConnection

Dim cmd As SqlCommand

Dim ConStr As String

Dim dr As SqlDataReader



ConStr = GetConnectionString()

objCon = New SqlConnection(ConStr)

Try

objCon.Open()

Catch ex As Exception

LogError("CreateCall::Error : " & ex.ToString)

Return -1

End Try

Try

cmd = New SqlCommand(SQL, objCon)

dr = cmd.ExecuteReader

If dr.HasRows Then

dr.Read()

Return dr.GetValue(0)

Else

Return -1

End If

Catch ex As Exception

LogError("CreateCall::Error : " & ex.ToString)

Return -1

Finally

Try

If Not cmd Is Nothing Then

cmd.Dispose()

End If

cmd = Nothing



If Not objCon Is Nothing Then

objCon.Close()

objCon.Dispose()

End If

objCon = Nothing

Catch ex As Exception

End Try



End Try

End Function



Thanks,



Helene

 

[Ghislain Tanguay]

Try to close your Datareader
dr.close

 

[Greg Burns]

Don't use a DataReader to return a single value.

Dim o As Object
o = cmd.ExecuteScalar
If o Is Nothing Then
Return = -1
Else
Return = CInt(o)
End If

Maybe somebody else can come up with a more elegant way of testing for an
empty result set.

HTH,
Greg