ASP Database Connections

[Total: 0    Average: 0/5]

Here’s our first tutorial in ASP. If you’ve read the introduction, you might remember that we talked about how the majority of ASP tasks are for database related reasons. Well, this tutorial will tackle the first hurdle in database interaction – making the connection. To create a connection to a database, you have 3 choices. You can either specify a path to the database, reference a DSN or you can use the Server.MapPath option. I always use the first option so that I don’t have to worry about any problems with a DSN or the MapPath option. I have had problems with these last two options before.

The first option is to create a specified connection to the database. When designing my first ASP application, I set up a connection to my database in every ASP page that I needed it. Later, I found that my testing platform was different than the server, so I had to redo every one of my connections to reflect this. If I would have known more and thought about it at the time, I would have created one ASP page to hold my database connection, and then simply refer to that anytime I needed to access that connection. We will be connecting to an Access 2000 database for this tutorial. The syntax for connecting to another database might be a little different depending on the type of database. If you are using a different type of database, contact your systems administrator for more information.

The way that you refer to your connection is to use it as an include file. An example:

What’s happening in your connection.asp page is that first you are creating a connection object on the server by using the Server.CreateObject(“ADODB.Connection”). Then you set the ConnectionString of that connection object equal to the type of database you are using (in this case an Access 2000 db) and the path to the database. You can also specify other options in your connection string such as username and password if your database uses these. If you decide to use this connection, please see the last section of this tutorial for the completion of the connection.

The second type of connection you can set up is a DSN reference connection. You can also house this connection in a separate page. To create a DSN connection for your database, go to Control Panel>ODBC Data Sources. A window will open and you want to click on the System DSN tab. Then click Add. Now you need to specify what kind of database you are connecting to. For now, we will specify the Microsoft Access Driver because we are using an Access database. Now click on Finish. You will have to give a Data Source Name and Description and then click on Select and locate your database. Then when you reference your database in your connection, your connection string will look like this:

For your DSN, you will use whatever you gave for a Data Source Name when you originally set up your DSN.

The third kind of connection you can use is the MapPath connection. This is referred to as a DSNless connection. The mappath connection can be useful when you don’t know the exact path to the database and you don’t have a DSN set up for the database. The syntax for this kind of a connection is listed below.

Finally, you will include this connection.asp page somewhere in your ASP page that requires a database connection. Something to remember (and I do mean REMEMBER!!!!) is to always close your connections as soon as possible. Whenever you open a connection or a recordset, you are taking up the server’s resources. Therefore, it’s best to close all connections as soon as possible. Don’t ever forget to close your connections! To include and use your connection.asp page, follow the code below.

And there you go…all you need to know to connect to your database. In our next tutorial we will cover retrieving information from our database and populating a table with that information. See ya next time.

~Geoff Swartz

Times Viewed: 39

Leave a Reply

Your email address will not be published. Required fields are marked *