Open Database Connection

The basic purpose of ASP is to allow a website to connect to a database and show Dynamic data on your website.

So how do you do connect to a database?  First, you need to understand that there are two ways to connect to a database.  You can use a DSN or DSN-less connection, which can both accomplish the same thing.  DSN is short for Data Source Name, which is setup on the server.  You can think of it as a shortcut to your database because it contains the driver and database path details to your database.  If you have your website hosted by an outside company like most people do, you may need to contact your host 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.

TIP > Not all hosting companies support ASP.  ASP code must be run on a Windows server, it will not work on a Linux server.  If you are looking for a reliable hosting company for your ASP website, we use and highly recommend HostGator.com.

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 connectionstring 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.

 

For maintenance purposes, it’s highly recommend that you place your database connection in a separate file like /includes/connection.asp.  Then, simply use an include statement to include your connection string in each of your pages like this:

<!--#INCLUDE VIRTUAL="/includes/connection.asp"-->

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.

< Back to ASP Tutorials