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

"Helene Day" <noSpam@noSpam> a écrit dans le message de
news:uL**************@TK2MSFTNGP09.phx.gbl...

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

[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
"Helene Day" <noSpam@noSpam> wrote in message
news:uL**************@TK2MSFTNGP09.phx.gbl...

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