When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, General


Question:

How can I effectively use a checkbox to update data in a database? (A typical example might be changing the status of members in a roster from active to inactive or vice versa.)


[Print this FAQ]

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


FAQ posted by Bill Wilkinson at 3/4/2001 10:17:10 PM to the Databases, General category. This FAQ has been viewed 83,811 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article