I'm using MySQL.
Its almost there - it works for a single value but not multiple ones.
<cfparam name="form.pracarealist" default="">
<cfquery name="qInsertStaffPracAreas" datasource="#request.db_dsn#"
username="#request.db_login#" password="#request.db_pwd#">
<cfif listlen(form.pracarealist) GT 1>
<cfloop list="#form.pracarealist#" index="id">
INSERT INTO tbl_staffpracareas(practiceid,staffid)
Values (#id#,#form.staffid#)
</cfloop>
<cfelseif listlen(form.pracarealist EQ 1)>
INSERT INTO tbl_staffpracareas(practiceid,staffid)
VALUES(#form.pracarealist#,#form.staffid#)
</cfif>
</cfquery>
Error Executing Database Query.
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 'INSERT INTO
tbl_staffpracareas(practiceid,staffid) Values (5,3)' at line 1
The error occurred in
C:\Inetpub\wwwroot\yardy\controlpanel\staff\modify_process.cfm: line 60
58 : <cfloop list="#form.pracarealist#" index="id"> INSERT INTO
tbl_staffpracareas(practiceid,staffid) Values (#id#,#form.staffid#)
</cfloop>
59 : <cfelseif listlen(form.pracarealist EQ 1)> INSERT INTO
tbl_staffpracareas(practiceid,staffid)
60 : VALUES(#form.pracarealist#,#form.staffid#) </cfif>
61 : </cfquery>
62 :
_____
SQL
INSERT INTO tbl_staffpracareas(practiceid,staffid) Values (2,3) INSERT
INTO tbl_staffpracareas(practiceid,staffid) Values (5,3)
DATASOURCE
yardy
VENDORERRORCODE
1064
SQLSTATE
42000
Resources:
-----Original Message-----
From: Michael Bondar [mailto:[EMAIL PROTECTED]
Sent: Sunday, 26 November 2006 2:01 AM
To: CF-Talk
Subject: Re: multiple selection drop down list
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:261655
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
