Back to Tutorial Index

Database Basics: Part I
By Kathi O'Shea (kathi@web-savant.com)
Reprinted with permission from http://www.web-savant.com/users/kathi/asp



Putting ASP to Work

Since ASP is especially good at reading and writing to databases, let's start with a very simple database and scripts that we'll eventually build into a guestbook. For now, start with a database with Name, City, State, and Country. I've built my database with MS Access, but you can use any ODBC-compliant database you'd like. First, follow the link below to view the ASP page, then click on "view code" to see the code behind the page. If you'd like, you can download the MS Access database and scripts. (Zip format, 18K)

View Working Sample      View Code

Modifying Records
Now that you've seen how simple it is to display contents of your database, let's go to the next step - modifying the records that exist in the database. We'll start with a very simple page that queries the database and displays a summary of the contents (in this case, we'll just list the names), and then allows you to click on a listing to modify the information contained in that record.

View working Sample      View Code        Top of Page

Adding New Records
The above works just fine if you have records in the database. But what if you want to add records? No problem! First we'll create a regular html form that passes the variables to a script for processing, then the script itself that adds the new record to the database. In this script, we'll also introduce simple form validation prior to processing.

View Working Sample      View Code        Top of Page

Deleting Records
The last thing we'll need to do is add a provision to delete unwanted records from our database. Please note that in the example, all records can be deleted except for one - I wanted to make sure that there would be at least one record in the database at all times for demonstration purposes.

View Working Sample      View Code        Top of Page

Submitting Scripts to Themselves
Sometimes its advantageous to use a single script for multiple purposes. But how do we accomplish this? By using scripts that submit back to themselves, using some sort of "flag" to determine what actions need to be performed. Let's use our database scripts as an example. We had an html form for entering information into a new record, which submits to a separate ASP script to actually insert the record into the database. We'll combine both files into a single script that displays the form if there's no flag passed and inserts the record into the database if another flag value is passed to the script. Just for fun we'll even add a routine to display the information for verification before its submitted - and use a flag for that, too. Our "flags" and values could be just about anything you'd like, but to keep things very simple, we'll use "Flag" and make the values numeric.

View Working Sample      View Code        Top of Page

Paging Through Recordsets
Now that we've gone throught the basics of database access, let's go a step further. The sample script above for viewing a database is fine if you only have a few records. Imagine, however, that you have several hundred - or even thousands - of records. Clearly the above script won't work. We will need to add some sort of paging mechanism so we can navigate through our recordset. The key to this is built-in features of ADO: .RecordCount, .PageSize, and .AbsoluteCount. We'll use these features to figure out how many records are in the recordset, specify how many records you want per page, and which page of the recordset we want to work with. This is also an example of a script that submits to itself; in this case the script acts upon the page number that's passed to it. If there is no page number passed to the script, the script sets the page number to 1. The sample script is part of the Free Guestbook download.

View Working Sample      View Code        Top of Page

Summary
In this section, we've learned how to connect to a database, perform a simple query, and display the contents on a dynamically generated web page. We've also learned how to modify records in the database, and how to delete records. Now that we've got the basic skills to read and write to a database, its not much of a jump to extend these skills to writing a database-generated guestbook. The next page will show you how...

  Top of Page       Building a Guestbook


Back to Tutorial Index