Simulate an MS Access Datasheet in ASP (Part I)

November 2, 2000

Often Requested Solution

I often see posts at where developers are asking for advice on how to publish their Microsoft Access databases to the web. Replies almost always include advice to learn ASP, and a reference to the book, ASP 3.0 Programmer's Reference from Wrox is usually provided.

Fact is, publishing Access tables to the web is not trivial, in spite of the Publish To Web Wizard (which I have never been fond of). Also, Access developers like myself are often disappointed at losing the rich functionality that comes with Access at no extra charge, such as the automatic saving of edits as well as inherent INSERT and DELETE functionality.

Take, for example, the simple datasheet. A table of rows and columns. How can this be reproduced on an ASP page? That was my goal a few months ago when I set about to publish a simple phone list database at my web site. Using the Wrox book mentioned above, I put together some simple ASP code that would allow me to display records from an MS Access table in editable Input Boxes and save changes made by users.

Build the ASP Datasheet

Since the code is available in the download, I will not reproduce it here, but merely describe the process.

For this demonstration I'll be using an MS Access version of the Authors table from the Pubs database in SQL Server. I want to create ADO Database and Recordset objects, connect to the database and return a sorted list of all records from the Authors table. The SQL looks like this:
    SELECT * FROM Authors ORDER BY au_lName, au_fName

Now comes one of the 2 tricky parts. As you loop through the records, you use the key field (Authors.au_id in this case) to associate each Input box with a specific record. The ASP code below is an example of naming the Author First Name text box as "fname_" plus the value of au_id.
    <TD><input type="text"
The source that is output to the client browser looks like this:
    <TD><input type="text" 
name="fname_409-56-7008" ></TD>
This process is repeated for each field: ID, FName, LName, Phone, etc. until the entire row of the recordset has been converted to a row in our ASP Datasheet. In my example, I set the ReadOnly property to True for the Primary Key field (au_id) and it could just as easily be hidden.

Write Code to Update Edited Fields

Great! We have an ASP page of text boxes, filled with values from our MS Access database. Now we need to add code to our page that will save all changes made by the user.

Since we only want this code to execute the second time the page is loaded (after the user has made some edits) we will add a QueryString argument named Processed and set it's value to 1 to indicate that an update needs to be done.
If Request.QueryString("Processed")=1 Then fPerformUpdate = True
If this argument returns a value, then we know that the page has been called it's own Submit button and an UPDATE needs to be executed. Here is where the next trick comes in. We will use a For Each loop to cycle through ALL text boxes looking for ID fields. Once we have found an ID field and extracted the ID value, we will know how to address all associated fields. As illustrated above, for the record corresponding to au_id=409-56-7008 we have a First Name box identified by the property: name=fname_409-56-7008.
If fPerformUpdate = True Then
  Dim AuthorID
  Dim FName, LName, Phone
  Dim Address, City, State
  ' Loop through each control on the page
  For Each item in Request.Form
    ' Our record ID controls have prefix ID_
    If Left(item,3)="ID_" then
      ' Load variables with text box values
      AuthorID =  Mid(Item,4)
      FName=Request.Form("fName_" & AuthorID)
      LName=Request.Form("lName_" & AuthorID)
      Phone=Request.Form("Phone_" & AuthorID)
      Address=Request.Form("Address_" & AuthorID)
      City=Request.Form("City_" & AuthorID)
      State=Request.Form("State_" & AuthorID)
      ' SQL will break on names like O'Leary
      ' if single quotes are not doubled.
      ' Create the SQL UPDATE statement
      sql = " UPDATE Authors SET "
      sql = sql & " au_fName='" & FName & "', "
      sql = sql & " au_lName='" & LName & "', "
      sql = sql & " Phone='" & Phone & "', "
      sql = sql & " Address='" & Address & "', "
      sql = sql & " City='" & City & "', "
      sql = sql & " State='" & State & "' "
      sql = sql & " WHERE au_ID='" & AuthorID & "'"
      ' Execute the UPDATE
      cnnPubs.Execute sql
    End If
There are 2 drawbacks to this method:
  1. You update ALL records, whether edited or not
  2. Submit ALWAYS causes the update to occur
It would, of course, be better if the posted form had a way to know which records, if any, had been updated. That would prevent the execution of an update statement on a record that has no changes (not to mention a server round trip) Sounds like a project for a rainy day.

We haven't yet shown how to ADD New records or DELETE records. That will be the subject of Part II in this series.

See All Articles by Columnist Danny Lesandrini

The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers