Delete Multiple Records Using Checkboxes...

This is a short tutorial describing ONE method of deleting multiple records from a database simultaneously, which you will specify using checkboxes.

In my example I have a database table called tblProducts containing 2 Fields: ID and PRODUCT.

This solution has two pages...

default.asp (the main page displaying the recordset, in this case all my products are listed inside a form, with checkboxes allowing me to mark them for deletion)
dodelete.asp (this page simply uses the data from the form to create an array which will tell me which records to delete)

default.asp

1. First create the recordset rsProducts using UD's recordset behaviour, just select all from the tblProducts table

2. Now insert a form on your page.

3. Inside this form place a table with 2 rows and 2 columns. The first row conatins titles, so you can type Delete? in the first column of the first row and Product in the second.
The second row is the row that will be repeated to show all the products.

4. Put a checkbox in the first column and drag the field PRODUCT from the databindings window to the second column. Apply the UD repeat region behaviour to this second row.

5. In code view, rename the checkbox "del" and bind the ID Column from the rsProducts recordset as its value. Below this table - but still within the form - also place a submit button.

When viewed you should see something like this...

Here is the complete code for the default.asp page....

<%@LANGUAGE="VBSCRIPT"%>

<%
set rsProducts = Server.CreateObject("ADODB.Recordset")
rsProducts.ActiveConnection = MM_test_STRING
rsProducts.Source = "SELECT * FROM tblProducts"
rsProducts.CursorType = 0
rsProducts.CursorLocation = 2
rsProducts.LockType = 3
rsProducts.Open()
rsProducts_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsProducts_numRows = rsProducts_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="DeleteChecked" method="post" action="dodelete.asp">
<table border="0" cellspacing="2" cellpadding="0" width="300">
<tr>
   <td>Delete?</td>
   <td>Product</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsProducts.EOF))
%>
<tr>
   <td>
  <input type="checkbox" name="del" value="<%=(rsProducts.Fields.Item("ID").Value)%>">
   </td>
   <td><%=(rsProducts.Fields.Item("Product").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsProducts.MoveNext()
Wend
%>
<tr>
   <td> </td>
   <td>
  <input type="submit" value="Delete">
'Ensure that your button DOES NOT have a NAME property, as this would be passed to the delete page too and ruin things!
</td>
</tr>
</table>
</form>
</body>
</html>
<%
rsProducts.Close()
%>

The part in red is the part edited in code view. When this form is submitted it will pass form parameters containing the record ID's that you want to delete. Now for the second page.

dodelete.asp

1. On this page simply copy and paste the recordset from your default page.

2. Now, below the recordset code add the following lines of code...

<%
strIDs = Replace(Request.Form, "&del=", ",")
strIDs = Replace(strIDs, "del=", "")
arrIDs = Split(strIDs, ",")
%>

This part parses the form data submitted and creates an array of the checked recordID's

3. Now for every item in this array (arrIDs) we want to delete the corresponding record from the database... so add this code next...

<%
For intCount = 0 To UBound(arrIDs)
rsProducts.Filter = "ID = " & arrIDs(intCount)
rsProducts.Delete
Next
rsProducts.Close()
Response.Redirect("default.asp")
%>

This snippet basically loops through the array and for each item it filters the recordset by the ID and deletes that record. Once it has deleted all the records you selected, it redirects you back to your main page where you will now see only the remaining products!!

TA DA..

Thats it, nothing to it. This technique can also be used to do other things like updates, send selctive emails etc etc etc etc,

Cheers,
Leed

PS You can also use an SQL command to do your delete.... use this code on the dodelete.asp page....

<%
strIDs = Replace(Request.Form, "&del=", ",")
strIDs = Replace(strIDs, "del=", "")

set DoDelete = Server.CreateObject("ADODB.Command")
DoDelete.ActiveConnection = MM_test_STRING
DoDelete.CommandText = "DELETE FROM tblProducts WHERE ID IN (" & strIDs & ")"
DoDelete.CommandType = 1
DoDelete.CommandTimeout = 0
DoDelete.Prepared = true
DoDelete.Execute()
Response.Redirect("default.asp")
%>

Cheers to Olivier Golaz for the pointer,...