Published: Friday, October 01, 1999
Using Forms to Do Batch Database Updates
Two days ago I posted an article on Using SQL Set Notation to
do Batch Deletes. For that article we used an example database table named Products,
which was defined as follows:
| Products |
|---|
| ProductID | Autonumber |
| Name | Text |
| Cost | Currency |
We listed each product in the Products table with a checkbox next to each one. The user
could then select one to many checkboxes, click on the Delete Selected Products, and the
chosen products would be deleted.
Wouldn't it be nice to be able to extend that interface and have the user also be able to update
the costs of the products? In this article, we are going to show how to allow for multiple products'
costs to be updated. Our interface will look like:
To allow for multiple updates, we need some way
to identify each product cost with a
ProductID. Also, since
the number of products the user can update is dynamic, we have to
pass this number to the ASP page that will actually carry out the database updates.
We need to modify
ListProducts.asp from our previous article. This modification will need
to include several new changes. First, we will need to add a text box for the currency. We will
also need to create a new button,
Update Product Costs. Most importantly, we must provide
a way to tie each input box with a uniqe
ProductID.
Let's start our coding for
ListProducts.asp:
<% Option Explicit %>
<!--#include file="adovbs.inc"-->
<%
<![CDATA[
'First things first, connect to the database and get the Product information
'Open a connection to the database
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=TestDB"
objConn.Open
'Get the table information for products
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Products", objConn, adOpenForwardOnly, adLockReadOnly, adCmdTable
'Display the FORM and the top of the TABLE
Response.Write "<FORM METHOD=POST ACTION="" DeleteProducts.asp"">"
Response.Write "<TABLE BORDER=1 CELLSPACING=1>"
Dim iLoop
Response.Write "<TR>"
Response.Write "<TH>Delete</TH>"
Response.Write "<TH>Name</TH>"
Response.Write "<TH>Cost</TH>"
Response.Write "</TR>"
]]>
|
The above code is identical to the beginning of the code in our article on batch
deletes. We are simply connecting to the database specified by the TestDB DSN, and
opening a recordset object (objRS) that contains the table data in our Products
table. We've also started our form and started the TABLE. Since we need to keep track of
how many total products we will retrieve from the database, let's create a counter variable now:
Now we need to step through the recordset object one record at a time. We want to display a delete checkbox,
the name of the product, and a text box containing the product's current cost. To identify each cost with
a ProductID, we will create an extra, HIDDEN, form field. This form field will
contain the ProductID.
<![CDATA[
'Display the name and price of each product
Do While Not objRS.EOF
Response.Write "<TR>" & vbCrLf
'Create a checkbox to check for deleting, setting the checkboxes
'Value equal to the current items ProductID
Response.Write "<TD ALIGN=CENTER><INPUT TYPE=CHECKBOX NAME=Delete "
Response.Write "VALUE=" & CInt(objRS(" & " ProductID"))></TD>"
'Display the name and cost of the product
Response.Write vbCrLf & "<TD>" & objRS("Name") & "</TD>"
Response.Write vbCrLf & "<TD><INPUT SIZE=10 TYPE=TEXT NAME="" & _
iCount & ".Cost"" VALUE="" & _
FormatCurrency(objRS("Cost"),2) & """ " & _
"STYLE="" " " text-align:right;"">" & vbCrLf
'Create a HIDDEN field that will contain the ProductID
Response.Write "<INPUT TYPE=HIDDEN NAME="" & iCount & ".ID"" " & _
"VALUE="" & objRS("ProductID") & """ " ">" & vbCrLf
Response.Write "</TD></TR>" & vbCrLf & vbCrLf
'Move to the next record...
objRS.MoveNext
'Increment the count variable
iCount = iCount + 1
Loop
'Print the end of the table, the submit button, and the
'the end of the form.
Response.Write "</TABLE>"
]]>
|
Note that we use the iCount variable to identify each cost and ProductID.
In the ASP page that performs the updates, UpdateCosts.asp, we will loop from 0 to
iCount, using Request(currentiCountLoop & ".Cost") to get the cost of a
product, and Request(currentiCountLoop & ".ID") to get the ProductID. Don't
worry, we'll discuss that in more detail later. Right now, let's finish the code for
ListProducts.asp.
Now that we've listed all of the products with delete checkboxes and a text box to modify the product
cost, we need to close and clean up our ADO objects. Also, we need to show another button,
Update Product Costs. We will have both of our buttons, Update Product Costs
and Deleted Selected Products, call JavaScript functions when they are clicked. If
the user is clicked the delete button, they will be prompted the verify that they do, in fact, wish to
delete the products. If they click the update button, the form's action will be changed to
UpdateCosts.asp and the form will be submitted.
Before we get to the JavaScript, let's look at the code to close and free our ADO objects, and display
the remainder of our HTML page and form.
<![CDATA[
'Clean up our ADO objects
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
]]>
%>
<![CDATA[
<P>
<INPUT TYPE=BUTTON VALUE="Update Product Costs" ONCLICK="UpdateCosts();">
<P>
<INPUT TYPE=BUTTON VALUE="Delete Selected Products" ONCLICK="DeleteProducts();">
]]>
<INPUT TYPE=HIDDEN NAME=Count VALUE="<%=iCount - 1 %>">
<![CDATA[
</FORM>
</BODY>
</HTML>
]]>
|
Note the two JavaScript functions that are called when the buttons are clicked. Also note that we are
storing the value of iCount - 1 in a HIDDEN variable named Count.
The following JavaScript functions should be placed before the ASP code is executed (view the
source for ListProducts.asp to see
where, exactly, they belong).
<![CDATA[
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
function DeleteProducts()
{
if (confirm("This will delete products! Are you sure you want to do this!?"))
{
document.forms[0].action = "DeleteProducts.asp";
document.forms[0].submit();
}
}
function UpdateCosts()
{
document.forms[0].action = "UpdateCosts.asp";
document.forms[0].submit();
}
// -->
</SCRIPT>
<BODY>
]]>
|
Now that we've looked at ListProducts.asp, we need to examine the ASP page that will perform
the batch update, UpdateCosts.asp. This is addressed in Part 2!
Read Part 2!
