To read the article online, visit http://www.4GuysFromRolla.com/webtech/100199-2.shtml

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
ProductIDAutonumber
NameText
CostCurrency

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"-->
<%
'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:

Dim iCount iCount = 0

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.

'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.

'Clean up our ADO objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %> <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 %>">
</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).

<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!


  • Article Information
    Article Title: Using Forms to Do Batch Database Updates
    Article Author: Scott Mitchell
    Published Date: Friday, October 01, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/100199-2.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers