Data Management

Work with databases in ASP

Learn how to tap into databases easily with ASP.

By Amy Cowen
(6/29/99)

One of ASP's greatest assets is that it allows you to tap into a database with ease. It's common to work with either an Access or a SQL database. Since Access is the easiest to start with, and is a tool you may already have, we'll use it for these examples. Once you learn core ASP techniques for working with your Access database, you'll find that many of the same skills will be necessary when you start working with SQL server.

When you want to tap into a database, you have to open it on the server. You can connect to and open the database by using either a Data Source Name (DSN) or by making a DSN-less connection directly in your script.

Creating a Data Source Name (DSN)
You can prep your database for use with ASP by setting up a System DSN for it in the control panel. On your local machine you can set up DSNs for any of the databases with which you are working. Then you can test your pages on your local server. If your site is being hosted by an ISP, and the ISP supports ASP, then it's likely you'll be provided with a GUI interface for creating a DSN for your database.

  1. In Windows 95/98/NT, open the Control Panel (Start/Settings/Control Panel) and double-click the ODBC entry.
  2. Select the System DSN tab and click Add.
  3. Select Microsoft Access Driver and click Finish.
  4. Fill in the Data Source Name. This is the name with which you'll refer to the database, so it operates as an alias.
  5. Click the Select button in the Database section and browse to find the Access database on your system.
  6. Click OK.

The new DSN will now be in the list of System DSNs and ready to use on your local server.

Connecting to a database
Let's run through creating a DSN-less connection and look at how you connect to the database. When you create a DSN, you are storing a chunk of information about the database, so you don't have to repeat it every time you need it: type of database, name, location, and, optionally, user and password.

To create a DSN-less connection, you'll have to supply the same information the long way. This sample, for example, shows a DSN-less connection being made to an Access database called products:

<%
StrConnect = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\db\products.mdb"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open StrConnect
%>
The second line defines the driver and the physical path for the database. To use a DSN-less connection, you need to know the actual location of the file from the root. Server.MapPath offers an easy work-around for anyone using a hosting service where the actual path can be hard to track down.

If we had set up a System DSN called products, the connection string would simply be:

<%
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "products"
%>
Now that the database is open, what can you do? Lots. The first thing to try, of course, is to read a set of records from the database and plop them onto your page. Before that, however, you'll need a recordset.

Recordset
A recordset is a collection of all the information stored in a specific database table. So, all the rows and columns in the table are available when you open the recordset. You need to open the recordset just as you needed to open the database connection. The commands are similar:

Set objRec = Server.CreateObject ("ADODB.Recordset")
objRec.Open "downloadable", strConnect, 0,1,2

This creates a recordset (objRec) of the table named downloadable that sits in the products database defined in strConnect. With the Recordset open, we can loop through the table and write all the contents to the screen. Or, we can test for specific contents and only write the data that matches our criteria to the screen.

Every column represents a field. So, if the database table looks something like this:

 Product ID  SKU  Name  File
 1  PR12345  Product A  install_a.exe
 2  PR12346  Product B  Install_b.exe

Then we have the following fields: ProductID, SKU, Name, and File. Your table would likely have many additional fields for everything from price to description. But this schematic lets you hook into the concept of the database table.

Writing the recordset contents
It's really easy to work with a recordset. If you wanted to loop through the database and print all the information to the screen, you could do this:

      While NOT objRec.EOF
' says to do this as long as we haven't reached the end of the file
  Response.Write objRec("ProductID") & ", "
  Response.Write objRec("SKU") & ", "
  Response.Write objRec("Name") & ", "
  Response.Write objRec("File") & "<BR>"
    objRec.MoveNext
      Wend
Even if you haven't used a loop like this before, you can probably tell by looking that this prints the information in a comma-delimited string and starts a new row for each new row in the table. You could use this same technique to write the data to an HTML table. Just insert your TABLE tags appropriately using Response.Write and keep a few things in mind:

  1. Your HTML tags and content go in quote marks.
  2. If your tag or content uses quote marks, double them:
    <FONT SIZE=""+2"">.
  3. Concatenate the variables and HTML/content information with ampersands.

Picking and choosing within the recordset
Imagine our products database also contains a field called OS for, you guessed it, a platform delimiter. Let's also imagine that the data stored in that field can only be one of the following values: Windows NT, Windows 95, Windows 98, Windows, Mac, Unix, or Linux.

We then could specify which records we wanted to write to the screen and ignore the others. Or, we could choose to format some results one way and others another—in different colors, for example.

A simple If...Then loop will quickly give us more control over the database. Let's first print out the records that are tagged as Windows NT products:

<TABLE BORDER=0 WIDTH=600>

<TR><TD COLSPAN=4 ALIGN=CENTER><FONT SIZE="+1"<<B>Windows NT Products</B></FONT></TD></TR>

<%
  While NOT objRec.EOF

  If objRec("OS") = "Windows NT" THEN ' specifies the criteria

Response.Write "<TR><TD BGCOLOR=""#FFFF66"">" & objRec("ProductID") & "</TD>"
Response.Write "<TD>" & objRec("SKU") & "</TD>"
Response.Write "<TD>" & objRec("Name") & "</TD>"
Response.Write "<TD>" & objRec("File") & "</TD></TR>"

  end if
  objRec.MoveNext
  Wend

%>
</TABLE>

Adding a record
Once you start working with the recordset and ASP, you'll soon be itching to add to a database via the Web. Adding is important whether you want your site visitors to be able to add comments or if you want to be able to make administrative updates.

The following code opens the recordset for a table that holds a list of books and their authors. You've seen this before, but this time the last three specifications defining the cursor type are different: adOpenStatic, adLockOptimistic, adCmdTable:

<%   ' database connection already made; code not shown here
Set objRec = Server.CreateObject ("ADODB.Recordset")
  objRec.Open "books", bookdb, adOpenStatic, adLockOptimistic, adCmdTable
%>
(If you are not using a copy of adovbs.inc, the line would be: objRec.Open "books", bookdb, 3,3,2).

The recordset is now ready to accept data. You just need to tell it what to add. In this case, let's assume we have pulled variables from a form: strBookTitle and strBookAuthor. Our table, books, has two fields, called Title and Author, so we can add a new record by doing this:

<%
objRec.AddNew
  ObjRec("Title") = strBookTitle
  objRec("Author") = strBookAuthor
  objRec.Update
%>
strBookTitle and strBookAuthor represent values, possibly entered by a user in a form. If you just want to test the add function, you can slot in a title and author in place of the variables—just remember to use quote marks. The first few times you try it, you'll probably want to open your database immediately to ensure that the update happened.

Recordset types
In the sample objRec.Open statement shown, you'll see the 0,1,2 at the end. These numbers stand for various cursor types. The type you use depends on what you are going to do. For example, if you're not going to add or edit any records, you use one Lock type. You'll use a different type when you plan to make changes or updates to the database.

0,1,2 actually stands for:

adOpenForwardOnly, adLockReadOnly, adCmdTable

You can use the words rather than the numbers if you have a copy of adovbs.inc stored on your server. It contains a list of these constants and many others.

Editor's Picks

Free Newsletters, In your Inbox