Update multiple selects

[cfquest]

I have a list box on a form that allows for multiple selections. The selections
are dumps into multiple rows and are tied into another table thru a foreign
key. I need to find out how to update the table with the multiple rows.

Scenario: We have an online application that allows individuals to sign up as
volunteers and they have an option of selecting a preferred school(s) from a
list box. If the volunteer at one point selected 3 preferred schools but would
like to go back and delete one and add 2 different ones.

Below is how I handled the original insert for the multiple selections.

ANY assistance will be greatly appreciated. Thanks!



<cfset strPrefList = "#FORM.PREFSCHOOL#">
<cfparam name="strPrefList" default="0">

<!-- Start to loop thru list of chosen PrefSchools -->
<cfloop index="listindex" list="#strPrefList" delimiters=",">

<cfquery name="InsertPrefSchool" dbtype="odbc" datasource="dsn">
INSERT INTO VolPrefSchool(VOLIDFK,PREFSCHOOL)
VALUES(#qGetRegID.NEWID#,#listindex#)
</cfquery>

</cfloop>

[mxstu]

There are few ways to do this. The simplest method would probably be to first
DELETE all of the volunteer's previous selections from the table and then
INSERT their new selections.

Another method would be to store the original selections in a hidden form
field and then on the action page, use the "original" and "new" selection lists
to DELETE any items that were de-selected and INSERT items that were newly
selected. This method would probably be more efficient.

[cfquest]

I do know how to delete from a table but I'm not sure how to do the second
suggestion. Since it is more efficient, that is the suggestion I would rather
go with. Can you or someone provide an example on how I might lay that one
out? Thanks

[mxstu]

(This example needs some work as I excluded some validation, CFTRANSACTION,
CFQUERYPARAM, etc for clarity)

Assuming you pass three variables to the action page:

form.volunteerID
form.newSchools (comma delimited list of numeric school IDs)
form.originalSchools (comma delimited list of original school IDs)








<cfparam name="form.newSchools" default="">
<cfparam name="form.originalSchools" default="">

<!--- A) you could put the list code in a UDF and simplify this page --->

<!--- get schools that were de-selected--->
<cfset removeSchools = "">
<cfloop list="#form.originalSchools#" index="schoolID">
<cfif ListFind(form.newSchools, schoolID) EQ 0>
<cfset removeSchools = ListAppend(removeSchools, schoolID)>
</cfif>
</cfloop>

<!--- get schools that were newly added --->
<cfset addSchools = "">
<cfloop list="#form.newSchools#" index="schoolID">
<cfif ListFind(form.originalSchools, schoolID) EQ 0>
<cfset addSchools = ListAppend(addSchools, schoolID)>
</cfif>
</cfloop>

<!--- B) should also use CFQUERYPARAM --->
<!--- C) should wrap both queries within CFTRANSACTION --->
<!--- so if one query fails, both will fail --->
<cfif ListLen(removeSchools) GT 0>
<cfquery name="removeSchools" datasource="#yourDSN#">
DELETE FROM PREFSCHOOL
WHERE VolunteerID = #form.volunteerID# AND
SchoolID IN (#removeSchools#)
</cfquery>
</cfif>

<cfif ListLen(addSchools) GT 0>
<cfloop list="#addSchools#" index="schoolID">
<cfquery name="addSchools" datasource="#yourDSN#">
INSERT INTO PREFSCHOOL (VolunteerID, SchoolID)
VALUES (#form.volunteerID#, #schoolID#)
</cfquery>
</cfloop>
</cfif>

[cfquest]

Would this still work if I was using a list box on the update page that has the
prior selections preselected? In other words, the list box is named
#form.prefSchool# on the update page and that is passed to the action page.

[mxstu]

Yes. Your field "form.prefSchool" would correspond to "form.newSchools" in the
sample code. Just make sure you also pass a comma delimited list of the prior
selections in a hidden form field. In the example, the prior selections are
passed in a field called "form.originalSchools".

Note: Make sure to change the query column names as well. I changed them for
clarity.

[cfquest]

I am not all too familiar with CFQUERYPARAM but I have looked at sample syntax. Should I place the CFQUERYPARAM in the <cfif> statement for the removeSchools or addSchools (in your example)?

[mxstu]

MM recommends that you use it everywhere that you pass a parameter (ex.
#form.myFieldName#..) to a CFQUERY. So it should be used for every form
variable used in the CFQUERY ... DELETE and ... INSERT statements.