Published: Tuesday, September 28, 1999
Using SQL Set Notation to do Batch Deletes
| 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:
|
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:
| Products |
|---|
| ProductID | Autonumber |
| Name | Text |
| Cost | Currency |
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 %>
<%
<![CDATA[
'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 %>
<%
<![CDATA[
'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:
View the source code for ListProducts.asp in text format
View the source code for DeleteProducts.asp in text format
