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.
- 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=" " name="fName_0" 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")%> name=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
AuthorID=Request.Form(“ID_0”)
FName=Request.Form(“fName_0”)
LName=Request.Form(“lName_0”)
Phone=Request.Form(“Phone_0”)
Address=Request.Form(“Address_0”)
City=Request.Form(“City_0”)
State=Request.Form(“State_0”)
‘ SQL will break on names like O’Leary
‘ if single quotes are not doubled.
LName=Replace(LName,”‘”,”””)
‘ 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
intCount=1
End If
Next
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
intCount=intCount+1
End If
End If
Next
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
