OK....here is code :)
This generates the SQL lists:
<cfloop list="1,2,3" index="stepno">
<!--- create list of steps to include in query --->
<cfset findlist=listappend("#findlist#", "lstep#stepno#, lstep#stepno#date,
lstep#stepno#note")>
<!--- create list of steps for insert into projects --->
<cfset steps=listappend("#steps#",
"smstep#stepno#note,smstep#stepno#,smstep#stepno#date")>
<cfset interim="
##get.lstep#stepno#note##,##get.lstep#stepno###,##dateformat(get.lstep#stepn
o#date)## ">
<cfset ivaluelist=listappend(#ivaluelist#, #interim#)>
</cfloop>
<!--- get all info from leads --->
<cfquery name=get datasource=#dsn#>
select company, address, addressline2, city, state, zip, country, region,
corebus, phone1, phone2, cell, email, email2, pager, fax, prename, fname,
mname, lname, sufname, nickname, homephone, website, leadfrom, status,
annualrev, employees, assignedto, notes, bio, birthday, title, department,
assistant, assistanttitle, assistantemail, assistantphone, homeaddress,
homeaddressline2, homecity, homestate, homezip, homecountry, probability,
firstcontact, enterdate, #findlist#
from leads
where leadID=#leadID# and companyID = #companyID#
</cfquery>
The above query works fine.
<cfloop list="#ivaluelist#" index="item">
<cfset stepsdata=listappend("#stepsdata#", "'#evaluate(item)#'")>
</cfloop>
This is the query that chokes:
<!--- copy into projects --->
<cfquery name=getproject datasource=#dsn#>
set nocount on
insert into projects (#steps#, clientID, userID, contacts, projectname,
startdate, status, leadfrom, probability)
values (#stepsdata#, #getclient.newID#, #get.assignedto#,
'#getcontact.newID#', 'New Opportunity', '#today#', 'active',
'#get.leadfrom#', 0#get.probability#)
select @@identity as newID
set nocount off
</cfquery>
And, here is the output from the above SQL (cut and pasted into a
<cfoutput>):
insert into projects
(smstep1note,smstep1,smstep1date,smstep2note,smstep2,smstep2date,smstep3note
,smstep3,smstep3date, clientID, userID, contacts, projectname, startdate,
status, leadfrom, probability)
values
('pain','yes','11-Apr-01','budget','yes','11-Apr-01','deadline','yes','16-Ap
r-01', 84, 1, '67', 'New Opportunity', '4/25/2001', 'active', 'magazine ad',
0.30)
D
*************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
"One man's magic is another man's engineering." ---Lazarus Long
-----Original Message-----
From: Christopher Olive, CIO [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 10:28 AM
To: CF-Talk
Subject: RE: ' in SQL 2000
perhaps you could post the code you're using. we could take a look.
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-----Original Message-----
From: Diana Nichols [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 9:42 AM
To: CF-Talk
Subject: ' in SQL 2000
I'm having a frustrating problem....
I have several queries in which I'm composing the SQL statement and then
inserting it into the query through a variable (Necessary because I'm
looping through a list to get the fieldnames and values).
e.g.: <cfset list1="#field1#, #field2#"><cfset list2=" '#value1#',
'#value2#' ">
then in the query:
insert into table1(#list1#)
values (#list2#)
When I output the generated SQL, it looks perfect....but SQL seems to be
having a problem with the ' (single quotes) surrounding strings ....I've
tried preservesinglequotes(), using chr(39), etc.......and I still get
syntax errors on insert or update. When I take the output data and manually
insert it into the query, it works fine - eliminating the possibility of
data type errors.
I've tried this with 4 different queries, with the same results.
I've now run out of things to try......Ideas?
TIA!
D
*************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
"One man's magic is another man's engineering." ---Lazarus Long
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
