This
tutorial shows you how to update multiple records in a table with the help
of checkboxes.I am going to use the UD40 site as an example. Let's say I needed a way to easily "remove" certain of my tutorials from the site. I can accomplish this easily without actually deleting anything from the database.
What I can do is to add a Yes/No field called Status to my table, with an initial value of -1 as shown in the snapshot. "-1" corresponds to a Yes and "0" corresponds to a No in our current scenario.
Next we will build an administrative page to do the "deletion", which, technically, is an update. What I am going to do is to update the status of the tutorials I want to remove from -1 to 0. In the SQL that I use to display my tutorials I simply set something like:
SELECT * FROM Tutorials WHERE Status = Yes
Any tutorial that has a status of 0 would not be selected and therefore becomes "invisible" from view.
Let us start building the administrative page. Create a recordset that selects ALL records from the tutorial table. Next, construct a table to hold the tutorial titles and checkboxes in place. Make sure that the entire table is placed within a form. Set the action of the form to point to another .asp page (page2.asp) that will perform the actual updating.
As in Tutorial 6 we will name the checkox as Checkbox for clarity, and assign to its Checked Value the autonumber ID field from our recordset. Finally add a repeat region that to the <TR></TR> and select Show All Records.
At this stage we need to know which records have an existing value of -1 and make the respective checkboxes to reflect so. Click on the checkbox and switch to the HTML Source window. Manually add in the hightlighted text shown below and be very careful that the codes are inserted at the correct place.
Now save this page as page1.asp and move on to create another page call page2.asp. On this new page create a Command by selecting it from the Data Bindings pallete. We will be using set notation again to perform the updating for us. We will call this Command comUpdate.
At this stage if you run a test whereby you click on a few previously unchecked records and submit the form, the changes would be reflected in the database. But this is not the end of the story yet. Now uncheck a few of the records and submit again. You will notice that the records are still returned as checked, i.e. the updating didn't work. The reason this is happening is because we didn't specify in our SQL to make unchecked records to have a value of 0.
A work around is to first make ALL records to have a value of 0, and then update those that we have selected using the checkboxes to a value of -1. Therefore we will need another Command to do so:
Now pay close attention to what I am about to say. If you have been following this tutorial step by step, then you will need to go to the HTML Source and move the codes for comClear to above those for comUpdate. Otherwise, if you created comClear first followed by comUpdate you will need to juggle the code blocks quite a bit (refer to the last snapshot at the end of this page).
There is one more important step that we have to do. Run another test by unchecking ALL records and hit submit. You will get an error that says something like:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'ID IN ()'.
To avoid that error you will need to enclose the comUpdate code blocks within a pair of:
and 
One final bit is to add the redirect target at the very end of your update code after doing the updating.
A word of warning here. Because of the approach we took in setting all the checkboxes to 0 before updating them, this method will not work if you have a long list and have separated the recordset into a few pages. The bottom line is: if you have selected Show All Records when adding the Repeat Region behavior you should be all right. Otherwise you will notice all the other records not shown on the current admin page would be set to 0 as a consequence of executing comClear.
It is always a good practice to close your connections and recordsets, the same with commands. Since UltraDev doesn't do that automatically, you will need to do it manually by setting both comClear and ComUpdate to Nothing. The final code would look like this:
| Demo | Back |
Copyright © 2001, Swee Hoe Ong
All rights reserved.