Database Programming in ASP Part II: Building a Guestbook (Article)

Category:

ASP, HTML and XML

Type:

Snippets

Difficulty:

Beginning

Author:

Kathi

Version Compatibility: ASP

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

<CENTER><A NAME = #THE_TOP>Database Programming in ASP -- Part II: Building a Guestbook</a>
By Kathi O'Shea 
Reprinted with permission from 
<a href="http://www.web-savant.com/users/kathi/asp">http://www.web-savant.com/users/kathi/asp</a> 
</center> 

<a name="setup">Setting Up the Database</a>
This is Part II of 2 articles that offer an
introduction to Database Programming in ASP. Part I can be found <a href="http://www.freevbcode.com/ShowCode.asp?ID=1178">here</a>.
Setting up the database is an important part of
building your guestbook application - the way its set up dictates what fields
will be required and what sort of scripting will be required to write to the
database. This guestbook uses the following fields:
<ul>
 <li>ID (primary key) - autonumber field
 <li>Date - (required) - general number format; default value is Now()
 <li>Name (required) - text field
 <li>City (required) - text field
 <li>State (optional) - text field
 <li>Country (required) - text field
 <li>Email address (optional) - text field
 <li>URL (optional) - text field
 <li>Comments (required) - memo field</li>
</ul>
All required fields are set to disallow zero-length strings, while the optional
fields will allow zero-length strings. If you'd like, you can <a href="http://www.web-savant.com/users/kathi/guestbook/guestbook.zip">download</a>
the database and guestbook scripts. (ZIP format, 17K) 
Note: if you downloaded the guestbook files prior to 1/9/99, you might
want to download the newest version, as it has some significant improvements
over previous versions.
Please note: in this example the autonumber field ID is set to the
primary key. Email address is always a good primary key, as its a unique value,
but there are two problems with this for this project:
<ol>
 <li>We do not require users to enter an email address. If you wanted to use
 email as a primary key, this should be a required field, and the ID field
 could be safely omitted.
 <li>If the email field was set as primary key, and a user wanted to make more
 than one entry, this wouldn't be possible unless he or she used a different
 email address. This may or may not be a problem in your view, but I didn't
 want to limit it to just one entry per email address.</li>
</ol>
If the above issues aren't a problem for you, you can always delete the ID
autonumber field and use the email field as primary key. Remember to change
ID-based queries to queries based on the email field or your scripts won't work.
<a href="#THE_TOP">Top of Page</a>
<a name="required">Required Scripts</a> 
First, we will need a script to display the entries in the guestbook. This is
almost exactly the same as the script we used to display the sample database,
but its also got a routine in it to page through the records, which is handy is
you have a large recordset.
Of couse, we will need a form for signing the guestbook, and an associated
script to process the form and add the new entry into the database. The form,
verification and add new record scripts were all incorporated into a single
script that submits to itself using a numeric flag.
Since its nice to have a provision to delete records in the database, I have
included an Administration script. This is another example of a script that
submits to itself. First it displays a login screen, and if the login is
successful, it displays a summary of the records in the database. When a record
is deleted, the form submits to the same script to execute the SQL statement and
display a message when the operation is successful. I haven't included a
provision to edit records, but it could possibly be added to the administration
script (and might be if I get enough reques...