Guys, You can insert multiple values if you replace VALUE section with SELECT.
Your SQL code will look something like this:
<cfquery name="qInsertStaffPracAreas" datasource="#request.db_dsn#"
username="#request.db_login#" password="#request.db_pwd#">
INSERT INTO staff_lookup
(
staffID,
practiceID
)
SELECT <cfqueryparam value="#form.staffid#"
cfsqltype="CF_SQL_INTEGER">,
p.practiceid
FROM tbl_practiceareas p
WHERE p.practiceid IN (<cfqueryparam value="#form.practicearea#"
cfsqltype="CF_SQL_INTEGER" separator="," list="Yes">)
</cfquery>
Michael
>See my previous reply.
>
>Russ
>
>-----Original Message-----
>From: Pete [mailto:[EMAIL PROTECTED]
>Sent: 25 November 2006 15:33
>To: CF-Talk
>Subject: RE: multiple selection drop down list
>
>Hi there
>
>
>Its almost there
>
>
>If a user only selects one checkbox and hits submit then everything works
>fine. I would have the values 3,2 to work with. 3 being the value for
>pracarealist and 2 being the value for the staffed.
>
>If a user selects multiple checkboxes, the it falls over. I get values such
>as 2,4,5,6,2 - with 2,4,5,6 being in the pracarealist column and 2 bing the
>staffed. In this example I need to write 4 records into the db, i.e,
>2,2
>4,2
>5,2
>6,2
>
>
>Any suggestions as to how to handle this? My code is below.
>
>Also I need to be handle if no selections are made (its not doing this
>correctly at present.
>
><cfparam name="form.pracarealist" default="0">
>
><cfquery name="qInsertStaffPracAreas" datasource="#request.db_dsn#"
>username="#request.db_login#" password="#request.db_pwd#">
><cfloop index="pa" list="#form.pracarealist#">
>INSERT INTO tbl_staffpracareas(practiceid,staffid)
>VALUES(#form.pracarealist#,#form.staffid#)
></cfloop>
></cfquery>
>
>
>
>-----Original Message-----
>From: Jim Wright [mailto:[EMAIL PROTECTED]
>Sent: Saturday, 25 November 2006 11:48 PM
>To: CF-Talk
>Subject: Re: multiple selection drop down list
>
>Pete wrote:
>>
>> You have an error in your SQL syntax; check the manual that corresponds to
>> your MySQL server version for the right syntax to use near 'Law,2) INSERT
>> INTO tbl_staffpracareas(practiceid,staffid) VALUES(Planning L' at line 2
>>
>
>I had assumed the practiceid would be a integer, but it looks like you
>are using a string... use '#pa#' in your query(or better yet, look at
>cfqueryparam).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261654
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
