CODEWITHSUNDEEP
sqlvbscriptasp-classic
Phil
Phil

Reputation: 405

Populate checkbox list from a database, and if previously checked then auto check on load

I have a user form which will populate certain categories based on fields stored in a database, this works all well and good using: 

<%
DSN = "DSN=LocalSQLServer"
set panDB = server.createobject("ADODB.recordset")
panDB.open "select attribute_name,attribute_id,attribute_question_value from   attributes where attribute_type=6 and attribute_id>217 and attribute_id<250",DSN
Do while Not panDB.eof 
    response.write "<input type=checkbox value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>" & panDB("attribute_name")
    response.write "<BR>"
    panDB.MoveNext
loop%>

The table attributes is just a listing of various attribute_names, their corresponding attribute_type, and attribute_id. When one of these checkboxes is selected and the form is saved a second table is updated to record the form_attribute_id, form_id, and attribute_id. My problem is having the fields which were previously selected checked when the form is reopened. Any ideas?

This is what I've gotten so far for retrieving whether a field was checked or not, basically listing all of the fields in the category so that the checkboxes will all still be populated and then the idea is to have the checkbox checked if the attribute_id field is not null. I'm getting an object required error though at "if panDB("attribute_id") is not null then"

DSN = "DSN=LocalSQLServer"
set panDB = server.createobject("ADODB.recordset")
panDB.open "select a.attribute_question_value, s.test as 'attribute_id', a.attribute_name from attributes a left outer join (select attribute_id as 'test' from survey_attributes where survey_id=2091) s on a.attribute_id=s.test where a.attribute_type=6 and a.attribute_question_value < 4",DSN
Do while Not panDB.eof 
    if panDB("attribute_id") is not null then
        response.write "<input type='checkbox' checked value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>" & panDB("attribute_name")
    else
        response.write "<input type='checkbox' value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>" & panDB("attribute_name")
    end if 
        response.write "<BR>"
    panDB.MoveNext
loop
set panDB = Nothing

Upvotes: 1

Views: 1991

Answers (2)

Phil
Phil

Reputation: 405

I was able to devise a query which produced the output I was looking for in order to both populate the lists and have them checked depending on data stored. I realize the if statement could be cleaned up but I put it in there while testing.

DSN = "DSN=LocalSQLServer"
set panDB = server.createobject("ADODB.recordset")
panDB.open "select a.attribute_question_value, s.test, a.attribute_name from attributes a left outer join (select attribute_id as 'test' from survey_attributes where survey_id=" & survey_id & ") s on a.attribute_id=s.test where a.attribute_type=6 and a.attribute_question_value < 4",DSN
Do while Not panDB.eof 
    if panDB("test") > 0 then
        response.write "<input type='checkbox' checked value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>" & panDB("attribute_name")
    else
        response.write "<input type='checkbox' value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>" & panDB("attribute_name")
    end if 
        response.write "<BR>"
    panDB.MoveNext
loop
set panDB = Nothing

Upvotes: 0

Nick Rolando
Nick Rolando

Reputation: 26167

Just add the attribute checked and set it to true or false based on your DB value

"<input type='checkbox' " & (if (DB_Expression) then "checked='checked'" else "") & "value='" & panDB("attribute_question_value") & "' name='country_" & panDB("attribute_question_value") & "'>"

Don't forget quotes around checkbox

Upvotes: 1

Related Questions