|
rdouglass -> A little checkbox function (1/20/2005 14:18:25)
|
Problem: Want to easily/quickly display a series of checkboxes (and values) to store in a single DB field.
Solution: Write a function
This issue is a common question frequently asked here. I had a similar issue in which I wanted to "qualify" users in a DB for each state that they qualify for. I also didn't want to build an intermediate table to take care of the relationship between users and states. (Lazy I know!)
Now I'll setup the disclaimer right here and say that I do NOT recommend using this for large sets of checkbox items nor calling this function on a page more than once. The DB call is internal to the function and is intended for single use per page. For instance, just on a user edit form. I will soon post a revised version that should be suitable for multiple use per page but I want to keep this one simple.
So the first thing I did was to add a field called "qualifiedStates" to my Users table and made it text. I set my field to 255 character limit but smaller checkbox sets may not need that many. (Again, larger sets are not recommended with this function.)
Then I put this code before the <body> tag on my userEdit.asp page and created a space large enough to hold a 300 px table (which seemed to be a good starting point).
<%
'****************** GENERIC CHECKBOX LIST
FUNCTION genericCheckBoxes(idList,fieldList,tableName,sortFieldName,formFieldName,numberColumns,tableWidth)
'Function builds a checkbox field with ID values being returned in a comma delimited string
'idList is the list of boxes that should be checked by ID number
'fieldList requires the first two fields to be the checkbox value and label fields respectively
' !!the asterisk (*) for fieldList works fine for tables / queries that have that structure in mind
' For instance, a table with just record ID's and checkbox values for fields.
'tableName is the name of the table or query being used (obviously)
'sortFieldName is the field that the query is to sort on; if none, be sure to use "" in the calling code
'formFieldName is the name of the form field you use when you Request.Form(fieldName)
'numberColumns is how many cells per row you want to display inside the generated table
'tableWidth is (surprise!) how wide in pixels you want the generated table
myGenericArray = split(idList,",")
IF (sortFieldName&""="") THEN
myClassSort = ""
ELSE
myClassSort = " ORDER BY " & sortFieldName
END IF
myDSN = 'MUST USE YOUR DSN STUFF HERE
mySQL = "SELECT " & fieldList & " FROM " & tableName & myClassSort
' response.write(mySQL)
' response.end
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(mySQL)
IF rstemp.eof THEN
response.write ("ERROR 127:NO Records in " & tableName & ": " & mySQL)
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
response.end
ELSE
arraydata=rstemp.getrows
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
END IF
Response.write("<table border='0' width='" & tableWidth & "'>" & VbCrLf)
FOR i = 0 TO ubound(arraydata,2)
IF i MOD cint(numberColumns) = 0 THEN
Response.write("<tr>" & VbCrLf)
END IF
Response.write("<td valign='top' align='right' nowrap><font size='1'>" & arraydata(1,i) & ": </font><input type='checkbox' name='" & formFieldName & "' value='" & arraydata(0,i) & "'")
FOR j = 0 to ubound(myGenericArray)
IF cint(myGenericArray(j)) = cint(arraydata(0,i)) THEN
Response.write(" checked")
END IF
NEXT
Response.write("></td>" & VbCrLf)
IF (i+1) MOD cint(numberColumns) = 0 THEN
Response.write("</tr>" & VbCrLf)
END IF
NEXT
IF i MOD cint(numberColumns) <> 0 THEN
Response.write("</tr>" & VbCrLf)
END IF
Response.write("</table>" & VbCrLf)
END FUNCTION
%>
Then, in the place where I made the 300 px space I put this line:
<%=genericCheckBoxes(userdata(13,i),"StateID,StateAb","tblStates","StateAb","QualifiedStates",5,300)%>
The result (once I browsed to the form with a found user) was a checkbox field like the attached and when I UPDATE my user record, I only have to set "QualifiedStates = '" & Request.form("QualifiedStates") & "' for my checkbox info to be written back to the user record.
To breakdown the function:
genericCheckBoxes(idList,fieldList,tableName,sortFieldName,formFieldName,numberColumns,tableWidth)
1. idList is the list of boxes that should be checked by ID number. With my example, I was using the "QualifiedStates" field that the data looked something like "3,9,27,44".
2. fieldList requires the first two fields to be the checkbox value and label fields respectively
!!the asterisk ("*") for fieldList works fine for tables / queries that have that structure in mind as log as you use the double quotes. For instance, a table with just record ID's and checkbox values for fields.
3. tableName is the name of the table or query being used (obviously). Mine was a simple table of StateID, STateName, and StateAb.
4. sortFieldName is the field that the query is to sort on; if none, be sure to use "" in the calling code (empty string).
5. formFieldName is the name of the form field you use when you Request.Form(fieldName)
6. numberColumns is how many cells per row you want to display inside the generated table
7. tableWidth is (surprise!) how wide in pixels you want the generated table
If you don't want all the table stuff, just remove all the "<table>","<td>", etc. stuff.
Again, I will soon post a revision to this function to optimize multiple "user records" as in if you wish to show this kind of thing in a grid fashion. I hope someone can use this or is some help to someone. I seem to see these checkbox type questions a lot.
[image]local://upfiles/2412/72C9C5D450E444B68F763636DF6757A4.jpg[/image]
|
|
|
|
