Data Access and Transactions

Tip 3: Close Your Connections

Close your connections as soon as you are finished with them.

By proactively closing connections when they are no longer needed, you reduce demand on the database server and make resources available to other users. Connections are closed when they go out of scope, but you can use the Close method to close a connection at any time.

The following code creates a Command object, implicitly creates a new connection, and calls a stored procedure.

<%
   Set cmd = Server.CreateObject("ADODB.Command")
   cmd.ActiveConnection = "dsn=sqlsrvr"
   cmd.CommandText = "{call mysproc}"
   cmd.Execute
%>

The Command object opens a connection automatically and releases it when the Command object goes out of scope at the end of the page. To release it before that time, you can either set the Command object reference to Nothing, as in:

Set cmd = Nothing

or you could close the connection by closing the active connection:

cmd.ActiveConnection.Close

Remember that storing Connection objects in the ASP Session object is not a good idea, because active connections cause database resources to remain open and would defeat the purpose of the ODBC connection pool.