Help with connection pooling - connections get closed but remain active

[caroh]

Can anyone see whats wrong with this code:

The connections are remaining live in the database: and causing
"Timeout expired. The timeout period elapsed prior to obtaining a
connection from the pool" errors.

Try
'open the connection
myConnection.Open()
'run the query
myCommand.ExecuteNonQuery()
Catch ex As SqlException

Finally
'close connection
myConnection.Close()

End Try

'Check to see if the service is restricted
If
myCommand.Parameters("@AccessRestrictionsSupported").Value.Equals(1)
Then
Return True 'service is restricted
'myConnection.Close()
Else
Return False 'service not restricted
'myConnection.Close()
End If

myConnection.Close()

Any help/suggestions very much appreciated

Thanks!!

 

[Gabriel Lozano-Morán]

close the connection immediatly after the ExecuteNonQuery() method call. You
don't need the myConnection.Close() at the bottom since the finally block
will always be executed. When you close a connection it returns to the
connection pool where it will be destroyed after an idle period of 4 to 7.5
minutes.

Where do you exactly get the time-outs?

Gabriel Lozano-Morán

 

[Caro]

Timeouts when users logon to system.

Checked in enterprise manager - process info and there are lots of
active connections to the stored procedure that this piece of code
executes.

Thanks for your help

Much appreciated