Need to Perform Multiple Updates?

While this article shows how to use a single HTML form to delete multiple records from a database, you may be wondering how to allow a user to update multiple database entries via one form / Web page. To find the answer, look no further than: - continued - Using Forms to Do Batch Database Updates How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a database query?

Chances are you've seen a web page that lists several records from a database, with a checkbox next to each record. You can easily delete a number of records at once by simply checking the records you'd like to delete, and then clicking a submit button. Believe it or not, but to create such a page using ASP is painfully simple!

In a previous article we talked about SQL Set Notation. Using set notation when working with SQL allows you to process a number of rows in just one statement. If you haven't read this article, I highly suggest you do so now. If you have a table that has a unique ID, you can use the following single SQL statement to delete a number of records:

DELETE FROM TableName WHERE TableID IN (Comma-delimited list of IDs)

If the comma-delimited list contains seven IDs in the table, seven records will be deleted. For our example, I created a simple Access database containing one table, Products. Products contains the following definition:

Now, to create our web page that lists all of the items in the Products table, with a "delete checkbox" next to each, we will use the following code (ListProducts.asp):

<%@ Language=VBScript %> <% Option Explicit %> <% 'Open a connection to the database Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=Products" 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>" 'Display each element in the table... 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><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>" & FormatCurrency(objRS("Cost"),2) Response.Write "</TD></TR>" & vbCrLf & vbCrLf 'Move to the next record... objRS.MoveNext Loop 'Print the end of the table, the submit button, and the 'the end of the form. Response.Write "</TABLE>" Response.Write "<P><INPUT TYPE=SUBMIT VALUE=""Delete Selected Products"">" Response.Write "</FORM>" 'Clean up our ADO objects objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing %>

The code is fairly self-explanatory. We open up a recordset object of the table Products and loop through each record in the table. We create a checkbox next to each product. The screen shot to the right shows what the output of ListImages.asp looks like with some same data.

Note that we give every single checkbox the same NAME. This passes to DeleteProducts.asp the VALUEs of the checked checkboxes in a comma-delimited list! Needless to say, we set the VALUEs of our checkboxes to the ProductID, our unique identifier!! All we need to do now is write the DeleteProducts.asp:

<%@ Language=VBScript %> <% Option Explicit %> <% 'We want to delete our products. The list of ProductIDs that need 'to be deleted are in a comma-delimited list... Dim strDeleteList strDeleteList = Request("Delete") if strDeleteList = "" then 'No items to delete Response.Write "You did not select any items to delete!" Else 'Open a connection to the database Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=Products" objConn.Open 'Now, use the SQL set notation to delete all of the records 'specified by strDeleteList Dim strSQL strSQL = "DELETE FROM Products " & _ "WHERE ProductID IN (" & strDeleteList & ")" objConn.Execute strSQL 'Clean up objConn.Close Set objConn = Nothing 'Display to the user that the product have been deleted. Response.Write Request("Delete").Count & " products were deleted..." End If %>

Pretty neat, eh!? This will delete all of the checked products. If no products are checked, a message will be displayed, indicating that no checkboxes were checked.

Happy Programming!

Attachments: