UPDATE! I'm moving this site to: www.emirplicanic.com.

BeWebmaster

ASP Open and Close Database Connection

Ad

The most basic purpose of ASP is to allow a website to connect to a database and show "Live data". It is called live data because ideally the database administrator will be updating the database routinely which will therefore automatically update the website.

So how do you do it? Well, it's actually pretty simple. First, you need to understand that there are two ways to connect to a database. You can use a DSN or DSN-less connection, both accomplish the same thing. A DSN is a Data Source Name that is setup on the server. You can think of it as a shortcut to your database because it contains the driver and database path information to your database. If you have your website hosted by an outside company like most people do, you will need to contact them directly and ask them to setup the DSN for you. You will have to tell them where your database is located within your website and you will have to give the DSN a name.

Here is an example of a DSN connection:

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=myCONNECTION.dsn"
objConn.Open
%>

For those of you not familiar, we will run through the lingo. In the first section, the DIM line declares the variable objConn. The "Set objConn..." sets the connection object. "objConn.ConnectionString..." sets the connection string and the DSN and the last line "objConn.Open" opens the connection.

Personally, we prefer to use DSN-less connections to our databases. The reason is that for maintenance and updating purposes, it is easier to make changes to database connections on your own rather than having to call or email your hosting company and wait for them to update your DSN.

There is a little more code involved with DSN-less connections, but it is worth it. Here is an example of a DSN-less connection:

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("/mydatabase.mdb") & ";"
objConn.Open
%>

The only difference between this example and the DSN example above is in the "objConn.ConnectionString = ..." line. Instead of using "DSN = myCONNECTION.dsn" you actually write out the appropriate driver and the respective path to the database. Ideally, the connection string should all be written on one line, but for display purposes we put it on two. When you paste this to your code, just remove the _ at the end of the line and put the Server.MapPath on the same line.

TIP: For maintenance purposes, we recommend that you place your database connection in a separate file like /includes/connection.asp. Then, simply use an include statement like this:
<!--#INCLUDE VIRTUAL="/includes/connection.asp"-->
to include your connection string in your pages. Then, if your database connection should ever change, you only have to edit it one time in your connection.asp file and it updates your connection throughout your website.

Closing Database Connection

A common problem among hosting companies is that ASP websites do not close the database connections after they are opened. This is a basic step that you should consider to be part of mandatory code. If you do not close your database connections, many problems can occur like web pages hanging, slow page loads, and more.

Think of it as going through a door to your house. Maybe the door will shut by itself, but maybe it won't. If it doesn't shut, who knows what will happen. If you live in the country, a bear could walk in. If you live in the city, a mugger could walk in. Alright, well maybe leaving a database connection open won't lead to anything that bad, but it will lead to a lot of unnecessary headaches for both you and your hosting company.

So how do I close my database connection?

<%
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=myCONNECTION.dsn"
objConn.Open

DIM mySQL, objRS
mySQL = "SELECT * FROM myTABLE"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn
%>

Display data from database here.

<%
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

All you have to do is include the "objRS.Close...objConn.Close..." code from the bottom of this example anywhere on your web page after your data is displayed. Yes, it is necessary to close both the connection and the recordset. Be careful not to include your close connection code on the page before where your data is being displayed or guess what happens? That's right, the connection gets closed before the data can be displayed and you get lots of errors.

Well, that's it. Now, you know how to properly close a database connection. Be sure to include this in your ASP coding routine and it will be smoother sailing for both you and your hosting company or server administrator.