article

Database Basics: Part I

Email
Submitted on: 1/5/2015 6:39:00 AM
By: BebeZed (from psc cd)  
Level: Advanced
User Rating: By 9 Users
Compatibility: ASP (Active Server Pages), VbScript (browser/client side)
Views: 611
 
     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

 
				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


Other 2 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Advanced category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.