One would think this article wouldn't be necessary, but I have "inherited" code from several web sites where the previous developer has manually opened and closed database connections every time they are needed.

So in this article, I am going to show you how to create a data function to call on your pages when you wish to open and close a data connection. Then all you will need is to use:

<% Call OpenDB() Call CloseDB() %>

as necessary.

The first step is to create your DataFunc.asp file. Well, goodness, just copy mine!

In the below example, I create OpenDB() which opens the database connection using Application variables defined in the global.asa file.

I automatically use "rs" throughout my projects when I create a record set from a sql string, so I go ahead and Dim it in the DataFunc.asp file.

Set rs = dbConn.Execute(sql)

This saves me from having to release it in my code. As you can see below, I set rs = Nothing in the CloseDB() function below.

<SCRIPT LANGUAGE="VBScript" RUNAT="Server"> ' functions to open and close the database connections ' library of Nannette Thacker, http://www.shiningstar.net Dim rs, dbConn Function OpenDB() Set dbConn = Server.CreateObject("ADODB.Connection") dbConn.ConnectionTimeout = Application("dbConn_ConnectionTimeout") dbConn.CommandTimeout = Application("dbConn_CommandTimeout") dbConn.Open Application("dbConn_ConnectionString") End Function Function CloseDB() ' close cursor and database connection Set rs = Nothing ' NOTICE! I used to have this as OBJECT, but ' found out later that it is now being returned as CONNECTION ' so update your code! if ucase(TypeName(dbConn)) = "CONNECTION" then dbConn.Close Set dbConn = Nothing end if End Function Function OpenRecordSet(recset, tablename) Call OpenDB() Set recset = Server.CreateObject("ADODB.Recordset") recset.Open tablename, dbConn, 0, 1 End Function Function CloseRecordSet(recset) Set recset = Nothing Call CloseDB() End Function </SCRIPT>

Now, what's going on here, you might ask?

if ucase(TypeName(dbConn)) = "CONNECTION" then

Well, if you open a database connection more than once on the same page, nothing happens. No big deal. But if you try to close it more than once and you've already closed it or never opened it, then it will generate an error. Bummer. So the above line of code checks to see if the connection has been defined and opened before it actually closes it.

Hint: If you use page templates, in your pagebottom.asp page, you can always call the function to close the database connection, whether you've ever opened it or not. That way you will always have safe pages and clean code!

I've included the two OpenRecordSet and CloseRecordSet functions with this, but frankly, I don't like using them and prefer working with SQL query strings. If you don't use them either, just delete them out of this function.

But what if you need to have a query within a query on a page? No big deal, use the "rs" for the first one, then use rs1, rs2, or whatever for each additonal query. Just remember to set them to nothing when done:

Set rs1 = Nothing Set rs2 = Nothing

Okay, here's how to use it:

Hopefully, you will be using a page template for the top and bottom of all your pages. For details on how to do that, see Using Templates. If you are using a pagetop.asp template at the top of the page, call the include file within the pagetop.asp page, rather than each actual file.

So, put the below include file in your pagetop.asp page or, if you don't use include templates on every page, call it directly on your page. Of course, use your path, not mine.

<!--#include VIRTUAL ="/articles/include/datafunc.asp" -->

The include file has now included the function on the page. It has not yet opened the database connection. So now, when you're ready to work with your database, open it:

<% Call OpenDB() sql = "select from mytable where this = 'that'" Set rs = dbConn.Execute(sql) if not rs.EOF then   ' do your stuff! end if Call CloseDB() %>

And, remember, use Call CloseDB() in the pagefooter.asp include page that you include at the bottom of all your pages so that you won't accidentally leave any open connections.