Search code examples
sql-serverasp-classicvbscript

Properly closing a database connection - VBScript + MS SQL

I have a classic ASP (vbscript) web app that connect to an SQL server. On certain pages, I open multiple DB connections to pull data.

My question is:

Is it better to add a close connection function at the bottom of each page or to explicitly close the connection right after using it? Keep in mind, on these certain pages, I reopen a DB connection everytime I get data; I do not reuse the connection. IE:

Set DBread = Server.CreateObject("ADODB.Connection")
DBread.Mode = adModeRead
DBread.Open (SQL_DB_CONN)

When I close the connection, I use:

DBread.Close
Set DBread = Nothing

So, should I constantly open then close the connection OR constantly open connections, then close them once at the end of a page?

Solution

  • You should open one connection and make all queries at the top of your page then close the connection as soon as the last query has executed

    Example:

    Dim DBread
Set DBread = Server.CreateObject("ADODB.Connection")
DBread.Mode = adModeRead
DBread.Open (SQL_DB_CONN)

'Make SQL Calls Here and Save rows of data by using the getrows function

DBread.Close
Set DBread = Nothing

'Process rows of data here