Properly closing a database connection - VBScript + MS SQL

Question

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?

D'Arcy Rittich · Accepted Answer · 2012-09-21 18:47:23Z

3

Keep your connections open for as short a time as possible. Connection pooling will worry about efficiency for you.

But, if you have sequential db operations, they can share the same connection. This will also allow you to use transactions.

answered Sep 21, 2012 at 18:47

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MeSo2 · Accepted Answer · 2022-01-17 18:26:56Z

2

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

edited Jan 17, 2022 at 18:26

MeSo2

4641 gold badge7 silver badges20 bronze badges

answered Sep 21, 2012 at 18:47

DiverseAndRemote.com

20k10 gold badges65 silver badges75 bronze badges

5 Comments

xweb Over a year ago

Thanks, but since I am calling many external functions for queries, I try to open connections in the functions for use on other pages.

DiverseAndRemote.com Over a year ago

You should definitely use global functions for opening and closing the connection but it might be best if you rethought your code as opening and closing unnecessarily generates more load on your db server as well as network traffic

DiverseAndRemote.com Over a year ago

Maybe your functions could check to see if the connection is already open and use that instead. You could just make DBread a global variable and leave it unchanged if DBread is not nothing

Rafael Over a year ago

@xweb if you find any answer useful mark it as a correct answer

xweb Over a year ago

Rafael, I will. How can I check to see if the connection is already open? I tried If DBread.State = adStateOpen but that fails if the connection is closed.

Collectives™ on Stack Overflow

Properly closing a database connection - VBScript + MS SQL

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related