Click here to Skip to main content
14,741,685 members
Articles » Database » Database » General
Article
Posted 10 Mar 2002

Tagged as

Stats

163K views
1.7K downloads
36 bookmarked

Using checkboxes to update multiple database entries

Rate me:
Please Sign up or sign in to vote.
4.67/5 (12 votes)
10 Mar 2002
Code which allows you to update any number of records with yes/no type values using checkboxes

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Torsten Mauz
Web Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
Generalworking but not updating the active field in dbase Pin
phil666711-Oct-07 19:58
Memberphil666711-Oct-07 19:58 
GeneralConnecting to a database Pin
phil66679-Sep-07 21:52
Memberphil66679-Sep-07 21:52 
Questionstill stumpped! Pin
lister23-Oct-06 2:10
Memberlister23-Oct-06 2:10 
GeneralAccess seems to be limited to 95 records Pin
codepuss7-Jan-06 22:49
Membercodepuss7-Jan-06 22:49 
GeneralSimple solution... Pin
Anonymous30-Jul-04 1:30
MemberAnonymous30-Jul-04 1:30 
GeneralRe: Simple solution... Pin
morphias16-Jul-05 22:18
Membermorphias16-Jul-05 22:18 
QuestionRe: Simple solution... Pin
lister23-Oct-06 2:10
Memberlister23-Oct-06 2:10 
GeneralYou really should just use an IN clause... Pin
Anonymous17-May-04 9:01
MemberAnonymous17-May-04 9:01 
GeneralTorsten Mauz !! Help me with multiple checkbox Pin
Guidingstar17-Mar-04 18:38
MemberGuidingstar17-Mar-04 18:38 
GeneralRe: Problems with the script solved... Pin
_Milhouse2719-Mar-04 15:54
suss_Milhouse2719-Mar-04 15:54 
Generaluncheck all Pin
Member 2038435-Feb-03 7:31
MemberMember 2038435-Feb-03 7:31 
GeneralHeLp pLz~! Pin
DarkSith21-Aug-02 17:38
MemberDarkSith21-Aug-02 17:38 
GeneralThe SQL is bogus Pin
Dancebert19-Jun-02 22:12
MemberDancebert19-Jun-02 22:12 
GeneralRe: The SQL is bogus Pin
Torsten Mauz19-Jun-02 23:52
MemberTorsten Mauz19-Jun-02 23:52 
GeneralRe: The SQL is bogus Pin
Member 20384328-Jan-03 7:59
MemberMember 20384328-Jan-03 7:59 
Generaluseful when u dont have stored proc Pin
Horatiu CRISTEA21-Mar-02 2:44
MemberHoratiu CRISTEA21-Mar-02 2:44 
GeneralRe: in MS Access is like this: Pin
Horatiu CRISTEA8-Jun-02 4:19
MemberHoratiu CRISTEA8-Jun-02 4:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.