Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Nov 9, 2000

Simulate an MS Access Datasheet in ASP (Part II)

By Danny Lesandrini

More ASP Datasheet Tricks

This article is a follow-up to last week's installment about reproducing an Access table in datasheet view using ASP. Last week we looked at the ASP and VB code necessary to create a grid of updateable Input boxes. This week we will create a new page based on the same recordset, through which we can both Add and Delete records.

Before we begin, there's one point I need to make.
  1. Keep in mind that I am an ASP-Developer-Wanna-Be. I am not suggesting that this process for duplicating an Access datasheet is a "Best Practice". Fact is, I don't recall ever seeing anyone attempt this trick, and I don't know if it's even a good idea. Fortunately, that little detail has never stopped me from writing code or implementing a solution. I hope to get more feedback and advice on what might constitute a "gotcha" with respect to this idea.

Build the ASP Datasheet

Once again, since the code is available in the download, I'll try not to reproduce the entire text here, but rather just explain some of the less intuitive aspects.

For this demonstration I'll be using an MS Access version of the Authors table from the Pubs database in SQL Server. Last week we explained how to draw the datasheet. Now let's focus on adding a new record. To do this, we'll need to add another row to the datasheet-- an empty one. Although Access puts this empty row at the bottom of the table, I've decided to draw my row at the top.

In the previous article we used the Author ID (au_id) to tag all the controls on each row of the datasheet so that values for each could be found by iterating through the Form Controls collection. Since a new record doesn't have an ID, let's give it the value of zero (0). We'll append a zero the name of each control in the first row.

  <TD><INPUT type="text"  size="12" 
value=" "
style="FONT-SIZE: 10"></TD>
Another change to the datasheet is required in order to facilitate the DELETE of records. Web sites, such as Hotmail, often add a checkbox to table rows to allow the user to perform a batch process such as delete or move records. Accordingly, I have added a check box to a new (left hand) column. The column header reads Delete, and each box is initialized as Unchecked.
<TD><INPUT id=chk_<%=rsAuthors("au_id")%>
type=checkbox ></TD>

Code that Does the Real Work

Once again we only want this code to execute the second time the page is loaded (after the user has added a record or tagged some for delete). We use a QueryString argument named Processed to determine if we should check for a record to add or delete.
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 records need to be processed. First, we check the to see if a value has been provided for AuthorID (au_id). If so, then it is assumed that we have a new record to INSERT. Validation could be done to ensure that the ID is unique and of proper type, but we won't worry about that in this example.
If fPerformUpdate = True Then
  ' First, INSERT New Record, if ID exists
  ' (Only au_id is required)
  If Len(Request.Form("ID_0"))>0 Then
      ' Load variables with text box values
      ' SQL will break on names like O'Leary
      ' if single quotes are not doubled.
      ' Create the SQL INSERT statement
      sql = " INSERT INTO Authors (au_id, au_fName, "
      sql = sql & "au_lName, phone, address, city, state)"
      sql = sql & " VALUES('" & AuthorID & "','" & FName 
      sql = sql & "','" & LName & "','" & Phone
      sql = sql & "','" & Address & "','" & City
      sql = sql & "','" & State & "')"
      ' Execute the UPDATE
      cnnPubs.Execute sql
    End If
That's all there is to it!

The code to DELETE tagged records is similar to last week's code to UPDATE rows. Notice that we loop through all ITEMS in the Request.Form collection to find each checkbox (prefixed with the string, "chk_"). We then check the value to see if the checkbox is "on", and if so, create and execute the DELETE SQL on the table.
  ' Now, loop through all the other records, looking
  ' for CHECKED check boxes.  Delete all those found.
  Dim intCount
  For Each item in Request.Form
    If Left(item,4)="chk_" then
      AuthorID = Mid(Item,5)
      ' Only delete CHECKED (on) records
      If Request.Form("chk_" & AuthorID) = "on" Then 
        ' Create the UPDATE SQL Statement 
        sql = " DELETE FROM Authors WHERE au_ID='" & AuthorID & "'"
        '  Execute the UPDATE SQL
        cnnPubs.Execute sql
      End If
    End If

Well, that's about it. There is some "housekeeping" code as usual, which sends messages back to the user about how many records were affected and/or errors, but the above summarizes the most critical pieces of code.

As I said before, I am interested in feedback about this idea. Especially interested in suggestions for improvement and warnings about potential "gotcha's".

See All Articles by Columnist Danny Lesandrini

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM