Classic ASP submitting multi-value checkbox select

Question

I've looked all over the web and cannot find the answer to this riddle.

I am still using VBSCRIPT (Classic ASP) and I have two tables in SQL, (Jobs and Tasks)

Jobs Table
ID
TaskID

Tasks Table
ID
TaskName

I basically have a form where a new job can be created and the user can choose one or more tasks from the Tasks Table using checkboxes. Let's assume the user checks Task #1 and #3. This form then submits the selected tasks and then inserts it into the TaskID field inside the Jobs table with a value like '1,3'

I then use this code to parse the values (which works great, I might add):

values = rsQuery("TaskID")
selections = Split(values, ",")
For i = 0 To UBound(selections)

<option selected value='Response.Write rsQuery.Fields("TaskID")'>Response.Write rsTasks.Fields("TaskName"))
Next

This gives me the items selected, but what I really want is this list mixed in with the other tasks (that were not checked) from the tasks table, without duplicating them. Is it possible that using an array might help? If so, I'm totally lost on how to use one in this scenario.

Any ideas? Thank you in advance!

Answer 1

Get the tasks in a recordset. Loop through the recordset to display the tasks and check for the ID in the Jobs.TaskID. If the value exists, show it as checked.

e.g.

sTaskID = rsQuery("TaskID")  ' this will have value from jobs table, say 1,3

set rsTasks = server.createobject("adodb.recordset")
ssql = "select * from tasks"
rsTasks.open ssql, <your connection>,....

do while not rsTasks.EOF
    if instr("," & sTaskID & ",", "," & rsTasks("ID") & ",") > 0 then
    ' Enclose the strings within commas, otherwise if the tasks selected has 10, then task ID 1 will show up as checked as well because the string 1 will be found within 10
        bChecked = " checked"
    end if

    %>
    <input type="checkbox" value="<%=rsTasks("ID")%>" <%=bChecked%>><%=rsTasks("TaskName")%>
    <%
    rsTasks.moveNext
loop

Please note that the comma separated values may be in the form 1,3 or 1, 3 i.e. a space after comma. You'll need to modify the code accordingly to handle that.