Deleting Multiple Records via Checkboxes

How this is done

Now we can start:

1) Drop a form onto your page.

2) Place a table into your form. The only reason for doing so, is to get a tidy place where to place your fields and the delete button in. I suppose you'll want to have the titles and maybe some other field listed, for a better identification of the records you wish to delete.

3) Place in the first cell of the new table a checkbox, then put in the next one a placeholder for your title column. Finally, add a submit button to the form.

4) Now bind the checkbox with the ID field and the title placeholder with the title field.

5) Select the row (<tr> tag) containing these 2 dynamic items and add to it the Repeat Region behaviour. At this point, save the page, run your Personal Web Server and you shoud see the multiple records displayed on your page.

Now it is time that you create an empty page (your future delete page) and give it a name that will help you recognise it as such. I've called it just Elim.asp.

Now comes the critical settings part:

6) Find your checkbox properties and make sure that its name is the one you intend to use to identify the parameter passing the ID values to the delete page. For the sake of this example, let's just call it checkbox.

7) Check your form and make sure that the method is set to GET and that the action is pointing to your delete page.(Elim.asp). Now, still in the form properties, click on the folder symbol next to the action attributes. In the Select File menu of the form action properties, press the Parameters button. In the left column (Name) type the chosen parameter name (checkbox, in this example). In the right column, click on the lightning symbol and find the ID column in your database. Double click on it and you're done.

Testing the submit page

To test if everything is working properly, save the page and run it on your Personal Web Server again. Now, if you select a record or two using the checkboxes and press the submit button, the form should take you to the delete page, which is still empty. Never mind: what you need to check is the address bar in your browser, that shoud be showing the delete page address, followed by a question mark and the ID values that have been passed by the submit page. Based on the names used in this example, the URL should read something like:

http://localhost/ Elim.asp?checkbox=2&checkbox=4&Submit=Submit

As you can see here, the submit page is passing a value, whose name is checkbox and whose values are 2 and 4, corresponding to the ID recordnumbers that you have chosen for deletion.

 

The delete page

Now everything would be simple if a SQL instruction could accept a delete statement with the WHERE condition ID=2,4 . Unfortunately this is not allowed by the syntax. What is allowed however is the condition ID=2 OR ID=4. So you need a little hand coded script, to convert the comma separated values into correct SQL. This solution was provided by my guru, Gianni De Rosa, from Naples to whom goes all my gratitude.

The quickest way to set up the delete page is the following:

1) Go to the empty delete page that you have previously created (Elim.asp). Open the Data Bindings window, press the + button and select Command. In the Command window, find your database Connection; in the connection Type box select Delete. Leave it incomplete and close the window.

2) Now open the page HTML source, go to the top and copy, right after the end of the   

<%@LANGUAGE="VBSCRIPT"%> <% @LANGUAGE="VBSCRIPT" %>

tag, the following code:

<% dim num

dim xide

dim whr

dim sql

whr=""

xide=split(request.querystring("checkbox"),",",-1,1)

for each num in xide

if whr<>"" then whr=whr & " or "

whr=whr & "ID=" & num

next

sql="DELETE FROM HereGoesYourTableName WHERE "

sql=sql & whr %>

3) Then find the line containing the CommandText and replace the expression "DELETE FROM WHERE " with the word sql , with no quotation marks.

4) Finally, to make sure that you can come back from the delete page, go to the bottom of the command lines that have been created by UD and add a new line, after

Command1.Execute() %>

and type:

<%Response.Redirect("your submit page name")%>

*Please note that the names in boldface are relative to this example or placeholders.

PS: if you can't resist opening the Command window in the Elim.asp page, please note that UD also can't resist changing the Command type to Stored Procedure, rather than Delete, after the manual changes we made to the code. This results in the CommandType line value to be changed from 1 to 4 and in a page error. So don't press OK, but Cancel.

One final tip: when working with code you may want to use a more powerful and flexible tool than UD's code editor or Windows NotePad.
Try NoteTab Pro, the coder's Swiss Army Knife, the coolest text editor I've ever found!

You may wish to add another feature: preventing users from getting an error message if they submit the page with no delete choice made.

The code to add is:

dim ver
ver = request.querystring("checkbox")
if ver<>"" then

This goes right after the ASP opening tag <% and before the dim num line.

Then you should place the "else" condition after the <% Response.Redirect("Sel.asp")%> line, redirecting to another page of your choice (the same Sel.asp one is not a bad idea), like:

<% else Response.Redirect("Sel.asp")%>

followed by the closure of the If condition:

<%end if %>

I hope this last minute addition was clear :-)