asked on

How to Properly Open and Close Connection to SQL server database via ASP

I am having major problems with performance of my website using SQL server as the back-end db. Sometimes the website starts to time out and I have to call my ISP to restart it. The problems are due to the connection to the db not being cleaned up. The way I do it is connect to the db on each page using Set connObj = GetConnectionObj() and subsequently closing the connection at the end of the page CloseConnectionObj()

The functions are in the inc file below. What is wrong with this code?

I am using the code below to connect to the db:

Dim connObj

Function GetConnectionObj

Set connObj = Server.CreateObject("ADODB.Connection")
      connObj.Open "dsn=ay4441;Database=ay444;uid=ay444;pwd=test1"
Set Application("connObj") = connObj

Set GetConnectionObj = Application("connObj")

End Function

Sub CloseConnectionObj

If (IsObject(Application("connObj")) = True) Then
Dim connObj
Set connObj = Application("connObj")
connObj.Close()
      Set Application("connObj") = Nothing
Set connObj = Nothing
End If

End Sub

jrram

1) On pages that you have that perform any kind of redirection, make sure you are closing your connection before redirection.

And, instead of storing your connection in an application variable, can you just use it directly and see if that makes a difference.

=== include_db_open.asp ===

Set connObj = Server.CreateObject("ADODB.Connection")
connObj.Open "dsn=ay4441;Database=ay444;uid=ay444;pwd=test1"

=== include_db_close.asp ===
connObj.Close
Set connObj = Nothing

drdevin

Are you sure there are not any recordsets still open. I know that they should close when the connection is terminated but I have seen stranger problems. Make sure all open recordsets are also closed as soon as you are done with them.

ASKER CERTIFIED SOLUTION

rdivilbiss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial