Use a single ADO connection to reduce ASP server load

If you need to execute multiple SQL statements on a single database in a single ASP page, it's often wise to open a single Connection and reuse it multiple times. Doing so reduces the number of times the server must open and close the same database, which can significantly increase the server's load. To use a single connection, create the database Connection at the beginning of the page, then destroy it at the end. When you Execute a SQL statement, whethe r it returns a Recordset or not, specify the active Connection. Here's an example.

<%

strDSN = "DSN=database;UID=username;PWD=password"

Set conn = Server.CreateObject("ADODB.Connection") 

conn.Open strDSN

strSQL = "select column from table"

Set rsResults=conn.Execute(strSQL, , 1)

if not rsResults.eof then

temp = rsResults("column")

else

temp = "No Results"

end if

'close the Recordset, but leave the Connection open

rsResults.Close

strSQL = "delete from table where column=123"

conn.Execute strSQL, , 1

'Done executing SQL statements at this point, so close the Connection.

conn.Close

set rsResults = Nothing

set conn = Nothing

%>

Active Server Index

Main Index