Andy's thinking about sex at work again! "passiong!"
J
-----Original Message-----
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: 25 April 2001 17:31
To: CF-Talk
Subject: RE: ' in SQL 2000
ok...well it doesn't look like it's due to dynamic field names as the error
is occuring in the value list. The first thing to check (although I'm sure
you already have) is that the datatypes you are passiong in match the
fields. i.e. check that the field smstep1note is a varchar field as you are
tring to insert a string.
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Diana Nichols [mailto:[EMAIL PROTECTED]]
Sent: 25 April 2001 17:06
To: CF-Talk
Subject: RE: ' in SQL 2000
This is the error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax
near 'pain'.
(I've also re-arranged the fields list, and tried putting it in the middle
of the SQL, at the end, etc..... and it always chokes on the first item in
the list)
Thanks!
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: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 11:38 AM
To: CF-Talk
Subject: RE: ' in SQL 2000
what error message are you getting? Can you post it here.
I've had problems in the past with using dynamic field names in SQL. Again
try building up an SQL string and executing it
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Diana Nichols [mailto:[EMAIL PROTECTED]]
Sent: 25 April 2001 16:13
To: CF-Talk
Subject: RE: ' in SQL 2000
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
