Using checkboxes to update multiple database entries

• Download source files - 1.31 Kb

Introduction

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:

<form name="form1" action="chkBoxes.asp" method="post">
 <table>
  <tr>
   <td>Checkbox1:</td>
   <td><input type="checkbox" name="chkBox" value="1"></td>
  </tr>
  
  <tr>
   <td>Checkbox2:</td>
   <td><input type="checkbox" name="chkBox" value="2"></td>
  </tr>
  
  <tr>
   <td>Checkbox3:</td>
   <td><input type="checkbox" name="chkBox" value="3"></td>
  </tr>
  
  <tr>
   <td>Checkbox4:</td>
   <td><input type="checkbox" name="chkBox" value="4"></td>
  </tr>
  
  <tr>
   <td>Checkbox5:</td>
   <td><input type="checkbox" name="chkBox" value="5"></td>
  </tr>
  <tr>
   <td colspan="2"><input type="submit" value="go"></td>
  </tr>
 </table>
 <input type="hidden" name="allBoxes" value="1,2,3,4,5">
</form>

The value of the checkboxes would be set as the id from 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.

'get the array with all of the checkbox id's
arrAll = split(Request.Form ("allBoxes"), ",")
 
'get the array with the id's that were ticked
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 allBoxes 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 "

'loop through the checkboxes which were ticked
for i = 0 to ubound(arrVals)
 if i = 0 then
  strSql = strSql & "id = "& arrVals(i)
 else
  'only add the " AND " if this is not the first value
  strSql = strSql & " AND id = "& arrVals(i)
 end if
next

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.

'dimension the array to the size we need
redim arrInActive(ubound(arrAll) - ubound(arrVals))

'set our indexer variable
IDX = 0

'loop through all the checkbox values
for i = 0 to ubound(arrAll)
 'a boolean value to check if we match or not
 bThere = false
 'loop through the values which were submitted
 for z = 0 to ubound(arrVals)
  'if it is found then set the boolean to true
  'this is so we don't add it to the new array
  if trim(arrVals(z)) = trim(arrAll(i)) then
   bThere = true
  end if
 next 
        'if it wasn't in the submitted array (i.e. it wasn't checked)
 if bThere = false then 
  'add the value to the new array
  arrInactive(IDX) = arrAll(i)
  'increment our indexer
  IDX = IDX + 1
 end if
next

strSql = ""
strSql = strSql & "UPDATE aTable SET active = 0 WHERE "
'loop through the array which holds the values that were NOT ticked
for i = 0 to ubound(arrInactive)
 if arrInactive(i) <> "" then
  if i = 0 then
   strSql = strSql & "id = "& arrInactive(i)
  else
   'only add the " ADD " if this is not the first variable
   strSql = strSql & " AND id = "& arrInactive(i)
  end if
 end if
next

Response.Write strSql

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 SQL statement.

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).