chipsterva69
asked on
build a dynamic list of checkboxes based on one table with selected set from another
I have been tasked with creating a page that allows logged in users to view a list of job options attached to their record and if they wish, uncheck items and check others, then submit the list.
We use ASP VB.script pages and are pulling data from a proprietary AMS system.
The items making up the list (40 options, in this case) come from one table in the database (we'll call it tbl_codes), with the data in the table being option ID, option description and some other fields. What users have selected are kept in another table (we'll call this tbl_codes_select), with the data in the table being a row ID, a user ID, and a option ID (nvarchar, 16 characters for some reason), along with some other fields.
What I would like to put together is some kind of code that dynamically reads the option table and generates a list of check boxes, one for each option, with the correct checkboxes checked based on the info in the codes_select table.
I initially put together a static table with each radio box dynamically checking it's corollary field in the codes_select table, but then it occurred to me that this will get very cumbersome as we add other options at a later date (I was also stumped by a single user having multiple options), so I began thinking of an array, but I have no clue how to put one together for this particular project.
The second half of this (to be asked once this part is solved to try to max out the points) is once the checkbox list is displayed, how do I craft the update statement?
We use ASP VB.script pages and are pulling data from a proprietary AMS system.
The items making up the list (40 options, in this case) come from one table in the database (we'll call it tbl_codes), with the data in the table being option ID, option description and some other fields. What users have selected are kept in another table (we'll call this tbl_codes_select), with the data in the table being a row ID, a user ID, and a option ID (nvarchar, 16 characters for some reason), along with some other fields.
What I would like to put together is some kind of code that dynamically reads the option table and generates a list of check boxes, one for each option, with the correct checkboxes checked based on the info in the codes_select table.
I initially put together a static table with each radio box dynamically checking it's corollary field in the codes_select table, but then it occurred to me that this will get very cumbersome as we add other options at a later date (I was also stumped by a single user having multiple options), so I began thinking of an array, but I have no clue how to put one together for this particular project.
The second half of this (to be asked once this part is solved to try to max out the points) is once the checkbox list is displayed, how do I craft the update statement?
I would guess that you will need a rather creative SQL string that uses a INNER LEFT JOIN statement nested within it. Try this SQL; [untested]
SELECT *, tbl_codes_select.row ID, tbl_codes_select.user ID FROM tbl_codes LEFT JOIN tbl_codes_select ON tbl_codes.option ID=tbl_codes_select.option
ORDER BY tbl_codes.option ID
What this will do is return everything from the tbl_codes table and the data from the tbl_codes_select table into the recordset. Once the recordset is built you need to build your checkbox table using a simple Do While loop on each row;
<form>
<table>
<%
Do While Not RS.EOF
%>
<tr>
<%
If rs("User_ID") Is Not NULL or Is Not Empty Then 'This checks the User_ID in the RS, if the row has a value then the checkbox gets checked.
%>
<td><input type="checkbox" name="rs("Option_ID")" value="1" checked></td>
<%
else
%>
<td><input type="checkbox" name="rs("Option_ID")" value="0"></td>
<%
end if
%>
<td>rs("Option_ID")</td>..
</tr>
<%
rs.MoveNext
Loop
%>
This is untested of course.
SELECT *, tbl_codes_select.row ID, tbl_codes_select.user ID FROM tbl_codes LEFT JOIN tbl_codes_select ON tbl_codes.option ID=tbl_codes_select.option
ORDER BY tbl_codes.option ID
What this will do is return everything from the tbl_codes table and the data from the tbl_codes_select table into the recordset. Once the recordset is built you need to build your checkbox table using a simple Do While loop on each row;
<form>
<table>
<%
Do While Not RS.EOF
%>
<tr>
<%
If rs("User_ID") Is Not NULL or Is Not Empty Then 'This checks the User_ID in the RS, if the row has a value then the checkbox gets checked.
%>
<td><input type="checkbox" name="rs("Option_ID")" value="1" checked></td>
<%
else
%>
<td><input type="checkbox" name="rs("Option_ID")" value="0"></td>
<%
end if
%>
<td>rs("Option_ID")</td>..
</tr>
<%
rs.MoveNext
Loop
%>
This is untested of course.
ASKER
Wow - thanks! This code works great in pulling the list out of the first table. However, I think I didn't describe the checked parameter well enough, my apologies.
Looking at your code, it appears that the checked value is determined by data in the tbl_codes table, but unfortunately, it isn't. The checked value should be set based on whether or not the user has already checked some of the options, and that data is in a second table, in my description called tbl_codes_select (I know, pain in the neck doing it this way, but that is the data I am given to work with).
Another catch that stumped me at the outset - there will be instances of a user having several entries in the tbl_codes_select table.
Looking at your code, it appears that the checked value is determined by data in the tbl_codes table, but unfortunately, it isn't. The checked value should be set based on whether or not the user has already checked some of the options, and that data is in a second table, in my description called tbl_codes_select (I know, pain in the neck doing it this way, but that is the data I am given to work with).
Another catch that stumped me at the outset - there will be instances of a user having several entries in the tbl_codes_select table.
Yeah, now I see I skipped over part of your question...
I'll make some additions, but in the mean time a question: could it be that the option ID in the second table is a char field because it was planned to store the selected options as a string? (ie "2, 16, 39")
I'll make some additions, but in the mean time a question: could it be that the option ID in the second table is a char field because it was planned to store the selected options as a string? (ie "2, 16, 39")
If you change the query on line 27 to the following, it checks the second table, but that assumes that there's 1 option id in each row, if there can be more than we need another 'like' clause like used in the update. The update itself is obviously obsolete for now, we may need quite a bit more code there.
I put "user_id = 1" in there for now, assuming you have something like a session variable for current user or another variable containing the currently selected user.
"SELECT id, descr, CASE WHEN EXISTS (SELECT * from tbl_codes_select where uid=1 and option_id=id) THEN 1 ELSE 0 END as checked from tbl_codes"I put "user_id = 1" in there for now, assuming you have something like a session variable for current user or another variable containing the currently selected user.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Looking at the SQL tables, the option ID fields are primary key and foreign key fields (depending on the tables) and are 32 characters long.
I will work with the code you have included above - and your concern about the insert is dead on! One thing at a time though - once I get the table to render out correctly, then i will worry about the insert!
Thanks again - how long does it take to become so adept at coding? I have been doing coding of various projects and levels in ASP VB for several years and your stuff is WAY over my head writing wise. I am pretty happy I can follow along theoretically.
I will work with the code you have included above - and your concern about the insert is dead on! One thing at a time though - once I get the table to render out correctly, then i will worry about the insert!
Thanks again - how long does it take to become so adept at coding? I have been doing coding of various projects and levels in ASP VB for several years and your stuff is WAY over my head writing wise. I am pretty happy I can follow along theoretically.
Thanks, ASP/VBS and SQL server have been my favourite programming environment for some 10 years, only just beginning to convert to .NET now...
I guess concatenating the options has to go then, let me rework it and I'll post back in a few minutes hopefully.
I guess concatenating the options has to go then, let me rework it and I'll post back in a few minutes hopefully.
ok, here we go, really hope I understood it correctly now:
<%
Option Explicit
Response.Buffer = True
Response.Expires = 0
Dim g_CurrentUserId
g_CurrentUserId = 1
Dim conn, rst, redir
redir = False
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=ee;User Id=ee;Password=ee"
Select Case Request.QueryString("mode")
Case "": ' show options
%>
<html>
<head>
</head>
<body>
<p>Now = <%= Now() %></p>
<form name="frm" action="<%= Request.ServerVariables("URL") %>?mode=update" method="post">
<table border=1 cellpadding=2 cellspacing=1>
<%
Set rst = Server.CreateObject("ADODB.RecordSet")
rst.Open "SELECT id, descr, CASE WHEN EXISTS (SELECT * from tbl_codes_select" & _
" where user_id = " & g_CurrentUserId & _
" and option_id = id) THEN 1 ELSE 0 END as checked" & _
" from tbl_codes", conn, 0, 1
While Not rst.eof
Response.Write "<tr>" & _
"<td><input type='checkbox' id='opt" & rst.Fields("id").Value & _
"' name='opt'" & iif(rst.Fields("checked").Value, " checked", "") & _
" value='" & rst.Fields("id").Value & "'></td>" & _
"<td><label for='opt" & rst.Fields("id").Value & "'>" & _
rst.Fields("descr").Value & "</label></td></tr>" & vbCrLf
rst.MoveNext
Wend
rst.close
Set rst = Nothing
Response.Write "<tr><td colspan=3 align='right'>" & _
"<input type='submit' value='Update'></td></tr>" & vbCrLf
%>
</table>
</form>
</body>
</html>
<%
Case "update":
Set rst = Server.CreateObject("ADODB.RecordSet")
conn.Execute "DELETE From tbl_codes_select WHERE user_id = " & g_CurrentUserId & _
iif(Request.Form("opt") <> "", " and not option_id in (" & Request.Form("opt") & ")", "")
Dim opt1
For Each opt1 In Split(Request.Form("opt"), ", ")
rst.Open "SELECT * From tbl_codes_select WHERE option_id = " & opt1 & _
" and user_id = " & g_CurrentUserId, conn, 0, 3
If rst.eof Then
rst.AddNew
rst.Fields("user_id").Value = g_CurrentUserId
rst.Fields("option_id").Value = opt1
rst.Update
End If
rst.Close
Next
Set rst = Nothing
redir = true
End Select
conn.close
Set conn = Nothing
If redir Then Response.Redirect Request.ServerVariables("URL") '' back to 'show' after 'update'
Function iif(b, v1, v2)
If b Then
iif = v1
Else
iif = v2
End If
End Function
%>ASKER
Okay - I have dropped in the code and it generates the following error:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'D292'.
/Scripts/data/20110808code
D292 happens to be part of the user ID - in this case, 2320D292-035A-46DC-BF3E-1F
I am assuming that the hyphens in the ID field are problematic?
BTW - the error coincides with this line:
rst.Open "SELECT id, descr, CASE WHEN EXISTS (SELECT * from tbl_codes_select" & _
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 'D292'.
/Scripts/data/20110808code
D292 happens to be part of the user ID - in this case, 2320D292-035A-46DC-BF3E-1F
I am assuming that the hyphens in the ID field are problematic?
BTW - the error coincides with this line:
rst.Open "SELECT id, descr, CASE WHEN EXISTS (SELECT * from tbl_codes_select" & _
This works as for the correct listing of your data, you will have to add the required fields to the strSQL to suit your needs. Haven't started on the update portion, but its just a simple UPDATE call to the tbl_codes_select table.
<%
'declare your variables
Dim strSQL
'create an ADO connection and recordset object
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
' Using Access, your db string may be different
Conn.Open "DBQ="&Server.MapPath("_database\test.mdb")&";Driver={Microsoft Access Driver (*.mdb)};"
'declare your SQL statement that will query the database
strSQL = "SELECT tbl_Codes.option_Desc, tbl_Codes.option_ID, tbl_Codes_Select.row_ID, tbl_Codes_Select.User_ID FROM tbl_codes LEFT JOIN tbl_Codes_Select ON tbl_Codes.option_ID=tbl_codes_select.option_ID ORDER BY tbl_codes.option_ID"
Set Rs = Conn.Execute(strSQL)
%>
<form>
<table>
<%
Do While Not RS.EOF
%>
<tr>
<%
If Rs.Fields("User_ID") > "" Then
%>
<td><input type="checkbox" name="<%=rs("Option_ID")%>" value="1" checked></td>
<%
Else
%>
<td><input type="checkbox" name="<%=rs("Option_ID")%>" value="0"></td>
<%
End if
%>
<td><%=rs("Option_ID")%></td>
<td><%=rs("option_Desc")%></td>
</tr>
<%
rs.MoveNext
Loop
'close the connection and recordset objects and free up resources
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
%>ASKER
mgfranz - your code works well also, thanks!! I was able to work in the parameter to limit it to one particular user and it pulls the selects together with the codes perfectly. However, and I am sure I buggered it up in putting the code on the page, but it only lists those codes that the user has selected. I need it to list all of the codes, with only the ones the user has already selected marked as selected.
mr. schutt - I still haven't figured out how to get around the user ID issue with your code, but I am still working on it.
Thank you both for all of your help so far!!
mr. schutt - I still haven't figured out how to get around the user ID issue with your code, but I am still working on it.
Thank you both for all of your help so far!!
ASKER
mr. schutt - i figured out a way around the user_id code issue, using a mix of your code and code that is generated out of Dreamweaver 5.5. What is did is below. I am not 100% sure of what exactly I did to the code structure from the actual performance perspective, but it functions as intended!
My question heading into the second part (and can post this as a second question to pop some more points) is what role does the 'opt' in the update? Should that be replaced with the id #?
My question heading into the second part (and can post this as a second question to pop some more points) is what role does the 'opt' in the update? Should that be replaced with the id #?
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_webaccess_STRING
MM_webaccess_STRING = "connection string info"
%>
<%
Response.Buffer = True
Response.Expires = 0
Dim g_CurrentUserId
g_CurrentUserId = Request.QueryString("nn")
Dim conn, rst, rst_cmd, redir
redir = False
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "connection string info"
Select Case Request.QueryString("mode")
Case "": ' show options
Set rst_cmd = Server.CreateObject("ADODB.Command")
rst_cmd.ActiveConnection = MM_webaccess_STRING
rst_cmd.CommandText = "SELECT id, descr, CASE WHEN EXISTS (SELECT * from tbl_codes_select where user_id = ? and option_id_select = option_id) THEN 1 ELSE 0 END as checked from tbl_codes"
rst_cmd.Prepared = true
rst_cmd.Parameters.Append rst_cmd.CreateParameter("param1", 200, 1, 36, g_CurrentUserId) ' adVarChar
Set rst = rst_cmd.Execute
rst_numRows = 0
%>
<html>
<head>
</head>
<body>
<p>Now = <%= Now() %></p>
<form name="frm" action="<%= Request.ServerVariables("URL") %>?mode=update" method="post">
<table border=1 cellpadding=2 cellspacing=1>
<%
While Not rst.eof
Response.Write "<tr>" & _
"<td><input type='checkbox' id='opt" & rst.Fields("id").Value & _
"' name='opt'" & iif(rst.Fields("checked").Value, " checked", "") & _
" value='" & rst.Fields("id").Value & "'></td>" & _
"<td><label for='opt" & rst.Fields("id").Value & "'>" & _
rst.Fields("descr").Value & "</label></td></tr>" & vbCrLf
rst.MoveNext
Wend
rst.close
Set rst = Nothing
Response.Write "<tr><td colspan=3 align='right'>" & _
"<input type='submit' value='Update'></td></tr>" & vbCrLf
%>
</table>
</form>
</body>
</html>
Well, I was going to say that simply adding the predicator WHERE to the SQL would work, but now that I think about it all that will do is return the rows that match the users ID... Sounds like a good use of a nested SELECT SQL,
I was off to the pub last night, but let's have another look.
The error you posted before had to do with the column type, I had assumed user_id to be an integer... so it would have been necessary to surround the value with quotes (in the SQL string) but using the command with parameters is a very nice solution, always better than just concatenating SQL.
The way I used the 'opt' form field means that the value of all selected boxes get put in that field, like so: "4, 8, 15, 16, 23, 42". Again, for the option_id column I had used an integer type before, so this may become a problem as well. But the mechanism of deleting the options that are no longer present in the list of selected options and adding the ones that are not present yet seems ok to me.
The error you posted before had to do with the column type, I had assumed user_id to be an integer... so it would have been necessary to surround the value with quotes (in the SQL string) but using the command with parameters is a very nice solution, always better than just concatenating SQL.
The way I used the 'opt' form field means that the value of all selected boxes get put in that field, like so: "4, 8, 15, 16, 23, 42". Again, for the option_id column I had used an integer type before, so this may become a problem as well. But the mechanism of deleting the options that are no longer present in the list of selected options and adding the ones that are not present yet seems ok to me.
ASKER
The answer was dead on but I had to adapt the code to handle the data that was being pulled out of the database. See subsequent comments and code examples.
ASKER
I screwed up the points - my apologies. The solution actually came from robert_schutt, not mgfranz. Both were immense helps, but I was able to get Mr. Schutt's code to work. I have submitted a request to customer service to correct the points.
Again, my apologies, and thank you both for educating me!
Again, my apologies, and thank you both for educating me!
Try this SQL with my code;
SELECT tbl_Codes.option_Desc, tbl_Codes.option_ID, tbl_Codes_Select.row_ID, tbl_Codes_Select.User_ID FROM tbl_codes LEFT JOIN tbl_Codes_Select ON tbl_Codes.option_ID=tbl_co
I just hard coded the User_ID but you can pass a variable to it with the proper integer. I don't like to use CASE statements due to the overhead they cause, my SQL makes the database do all the work. Although Roberts code will work just fine.
SELECT tbl_Codes.option_Desc, tbl_Codes.option_ID, tbl_Codes_Select.row_ID, tbl_Codes_Select.User_ID FROM tbl_codes LEFT JOIN tbl_Codes_Select ON tbl_Codes.option_ID=tbl_co
I just hard coded the User_ID but you can pass a variable to it with the proper integer. I don't like to use CASE statements due to the overhead they cause, my SQL makes the database do all the work. Although Roberts code will work just fine.
Open in new window