I have seen a lot of people struggle with this sort of thing, so after having written a solution for a friend of mine, I decided that it would be useful to people to see this way of doing things.
As an example I will keep things very simple, the problem is this:
You have a table (called
aTable in this example), with a primary key column named
id and a bit (yes/no for access people) field called
active. What you want to do is display a
form with a
checkbox for each record in
aTable and allow the user to update the
active field for every record in one submission (where
active will be set to 1 if the
checkbox is ticked and set to 0 if it is unticked). Obviously you could simplify things by having each record in it's own
form but this would make updating large numbers of records a real pain.
This is my solution to the problem. Below is an example of the HTML generated for the
<form name="form1" action="chkBoxes.asp" method="post">
<td><input type="checkbox" name="chkBox" value="1"></td>
<td><input type="checkbox" name="chkBox" value="2"></td>
<td><input type="checkbox" name="chkBox" value="3"></td>
<td><input type="checkbox" name="chkBox" value="4"></td>
<td><input type="checkbox" name="chkBox" value="5"></td>
<td colspan="2"><input type="submit" value="go"></td>
<input type="hidden" name="allBoxes" value="1,2,3,4,5">
The value of the
checkboxes would be set as the
aTable. As you can see the
checkboxes are all named the same thing. This simplifies retrieving the results as otherwise we would have to do some dynamic
request.form calls which are really ugly to look at.
Ok so that's our
form, now what do we do with it? Well let's take a look at the first bit of code.
arrAll = split(Request.Form ("allBoxes"), ",")
arrVals = split(Request.Form ("chkBox"), ",")
All we do here is fill two arrays with the results from our
form. The first array
arrAll is filled from the
hidden input which as you can see in the
form code is a string containing all of the values for the
checkboxes.The second array
arrVals is filled with the
checkboxes that were checked (remember if a
checkbox is not checked it does not submit a value in the
form post.). All simple stuff so far, now we generate the
SQL statement which updates the values that were ticked.
strSql = ""
strSql = strSql & "UPDATE aTable SET active = 1 WHERE "
for i = 0 to ubound(arrVals)
if i = 0 then
strSql = strSql & "id = "& arrVals(i)
strSql = strSql & " AND id = "& arrVals(i)
Response.Write strSql & "<hr>"
What we do here is simply loop through the
checkbox values which are contained in the
arrVals array. If you remember these are the values which were checked so the
SQL statement is setting the
active column to 1 (i.e. make it active). Still very simple, next we need to generate the
SQL statement which marks the unchecked values as inactive.
redim arrInActive(ubound(arrAll) - ubound(arrVals))
IDX = 0
for i = 0 to ubound(arrAll)
bThere = false
for z = 0 to ubound(arrVals)
if trim(arrVals(z)) = trim(arrAll(i)) then
bThere = true
if bThere = false then
arrInactive(IDX) = arrAll(i)
IDX = IDX + 1
strSql = ""
strSql = strSql & "UPDATE aTable SET active = 0 WHERE "
for i = 0 to ubound(arrInactive)
if arrInactive(i) <> "" then
if i = 0 then
strSql = strSql & "id = "& arrInactive(i)
strSql = strSql & " AND id = "& arrInactive(i)
This code segment is a bit more complicated. The first thing is to dimension an array to store the values which were not checked. Basically what follows is to loop through all of the values contained in the
arrAll array, then for any values which do not appear in the
arrVals array (i.e. they were not checked) add them to the
arrInactive array. Finally we loop through this newly populated array to generate the
Look at the comments in the code for a better explanation of each section of code.
You should find this code quite simple to modify so that you can use it with a database, I chose to write it like this so that the concepts would be clearer. If you are struggling to implement this in the context of the database, then just let me know and I'll try and help you out.
I hope this helps some people out with this potentially murky problem. Good luck and as allways suggestions and constructive criticisms are very much welcome.
Inspired by an article by Bill Wilkinson on aspFaqs.com. I thought Bill's method was a bit overly complex so I decided to post my solution (although Bill's article is probably better written).