Avatar of chipsterva69
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?
ASPVB ScriptMicrosoft SQL Server

Avatar of undefined
Last Comment
Mark Franz

8/22/2022 - Mon
Robert Schutt

I couldn't help myself and put in both parts...

<%
Option Explicit

Response.Buffer = True
Response.Expires = 0

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, 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":
		conn.Execute "UPDATE tbl_codes Set checked = Case When ', " & Request.Form("opt") & ", ' Like '%, '+convert(varchar,[id])+', %' Then 1 Else 0 End"
		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

%>

Open in new window

Mark Franz

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 ID
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>...  'insert all your fields you want to display
</tr>
<%
rs.MoveNext
Loop
%>

This is untested of course.
     
chipsterva69

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Robert Schutt

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")
Robert Schutt

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.

"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"

Open in new window


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
Robert Schutt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
chipsterva69

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robert Schutt

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.
Robert Schutt

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

%>

Open in new window

chipsterva69

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/20110808codetest.asp, line 26

D292 happens to be part of the user ID - in this case, 2320D292-035A-46DC-BF3E-1F9F67CA38EC.  

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" & _


I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Franz

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
%>

Open in new window

chipsterva69

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!!
chipsterva69

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 #?
<%
' 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>

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mark Franz

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,
Robert Schutt

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

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
chipsterva69

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!
Mark Franz

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_codes_select.option_ID AND tbl_Codes_Select.User_ID = 1

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.