Database Basics: Part I
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...
Reprinted with permission from http://www.web-savant.com/users/kathi/asp
Original Author: BebeZed
Code
Modifying Records Adding New Records Deleting Records Submitting Scripts to Paging Through Recordsets SummaryFor 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)
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
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
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
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
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
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...
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.