| Answer: |
One moderately common operation using databases and ASP and HTML FORMs is to show the state of some true/false or yes/no field in the database by the use of a checkbox on the HTML page. And, of course, many ASP authors would then like to detect that a user has changed the state of a given checkbox and, if so, update the database to reflect that change.
However, one "feature" of checkboxes is that an unchecked checkbox produces no value in the Request.Form or Request.QueryString collections! So, the only way you can detect an unchecked box is to first know that it exists on the HTML page and then assume that if you don't see a value it wasn't checked.
If you are only showing one record, this is more than adequate.
But supposing you are showing many records. The traditional way to identify which record is affected is to include the identifier of the record in the name or value of the form field. That works really well for most fields, including TEXT and SELECT and many more. But, again, it doesn't work well for checkboxes.
So...
Here is my solution. It is really quite simple: In addition to the checkbox that the user can change, I also put a Hidden field into the form that reflects the prior state of the field in that particular record. And I use "parallel" names to tie the two fields together.
So, without further ado, herewith my solution:
NOTES: I am assuming that we have a table (and RecordSet) with the fields recordID (an autonumber field that uniquely identifies the record), isActive (a true/false field that indicates whether this particular user is active...in what we don't care), and userName (hopefully its meaning is obvious).
<%
... make your connection ...
... get your recordset ...
Do Until RS.EOF
' recordID is something that uniquely identifies
' the particular record, often an autonumber field!
id = RS("recordID")
cbName = "CB_" & id
hvName = "HV_" & id
' the isActive field might have been specified as
' a Yes/No field in Access...or as a Boolean field
' in any database, of course.
If RS("isActive") = True Then
active = "YES"
chkd = " CHECKED "
Else
active = "NO"
chkd = ""
End If
' Now output the info about one record in the recordset
Response.Write _
"<INPUT Type='CheckBox' " _
& "Name='" & cbName & "' Value='YES'" & chkd & ">" _
& "<INPUT Type='Hidden' " _
& "Name='" & hvName & "' Value='" & active & "'>" _
& " -- " & RS("userName") & "<BR>" & vbNewLine
RS.MoveNext
Next
...
%>
|
Do you see how that works? The resultant HTML output (for one record) might look like this:
<INPUT Type='CheckBox' Name='CB_3371' Value='YES' CHECKED>
<INPUT Type='Hidden' Name='HV_3371' Value='YES'
Harry James
<INPUT Type='CheckBox' Name='CB_3393' Value='YES'>
<INPUT Type='Hidden' Name='HV_3393' Value='NO'
John Doe
|
Note how the value of the Hidden field reflects the state of the CHECKED attribute of the corresponding CheckBox field.
Then on the next (ASP) page, we do:
<%
' We look at all items in the form...
For Each item In Request.Form
' ...but only process the HV (Hidden Value) items...
If Left( item, 3 ) = "HV_" Then
' find the record id that is embedded in the field name
recID = Mid( item, 4 ) ' chop off the HV_ part!
' and get the checkbox name that corresponds
cbName = "CB_" & recID ' constructed same as in other page!
' now get the values of the two fields as posted...
priorValue = Request.Form(item)
newValue = "" & Request.Form(cbName)
' but if the checkbox wasn't checked, it won't have
' any value at all...so give it one to simplify coding:
If newValue = "" Then newValue = "NO"
' now comes the logic:
If newValue <> priorValue Then
' aha! we need change this record!
active = ( newValue = "YES" ) ' active will be true or false!
SQL = "UPDATE table SET isActive=" & active _
& " WHERE recordID=" & recID
yourConnection.Execute( SQL )
End If
End If
Next
%>
|
Do you see that? ONLY those records where a change was actually made will be updated!
Much more effective than updating every record because you don't know what the prior state was.
HOWEVER...There is room for improvement in that code.
If the user changes the state of 13 checkboxes, then that code will do 13 separate calls to the Execute function to perform the SQL UPDATE operation.
How about if we can guarantee that we'll never need more than TWO calls to Execute?
Like this:
<%
' set up for two lists:
yesIDs = ""
noIDs = ""
' We look at all items in the form...
For Each item In Request.Form
' ...but only process the HV (Hidden Value) items...
If Left( item, 3 ) = "HV_" Then
' find the record id that is embedded in the field name
recID = Mid( item, 4 ) ' chop off the HV_ part!
' and get the checkbox name that corresponds
cbName = "CB_" & recID ' constructed same as in other page!
' now get the values of the two fields as posted...
priorValue = Request.Form(item)
newValue = "" & Request.Form(cbName)
' but if the checkbox wasn't checked, it won't have
' any value at all...so give it one to simplify coding:
If newValue = "" Then newValue = "NO"
' now comes the logic:
If newValue <> priorValue Then
' aha! we need change this record!
' which list should it go on?
If newValue = "YES" Then
yesIDs = yesIDs & "," & recID
Else
noIDs = noIDs & "," & recID
End If
End If
End If
Next
' *NOW* we do the updates...if there are any to do:
If yesIDs <> "" Then
SQL = "UPDATE table SET isActive=True " _
& "WHERE recordID IN (" & Mid(yesIDs,2) & ")"
yourConnection.Execute( SQL )
End If
If noIDs <> "" Then
SQL = "UPDATE table SET isActive=False " _
& "WHERE recordID IN (" & Mid(noIDs,2) & ")"
yourConnection.Execute( SQL )
End If
%>
|
Do you see *that* idea?
We do all the change-to-True records in a single Execute and then all the change-to-False records in a second one. If you are not familiar with the IN clause of SQL, it's time to read up on it.
Give the techniques above a try next time you work with checkboxes on a page where you allow updates. And if something doesn't work, ask me in the forums or email me at Bill@ClearviewDesign.com (I don't necessarily check this address daily, so be patient and don't overload it!).
Bill Wilkinson
|
