RE: ' in SQL 2000
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 OKhere is code :) This generates the SQL lists: 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# The above query works fine. This is the query that chokes: 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 new
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 OKhere is code :) This generates the SQL lists: 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# The above query works fine. This is the query that chokes: 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 And, here is the output from the above SQL (cut and pasted into a ): insert into projects (smstep1note,smstep1,smstep1date,smstep2note,smstep2,smstep2date,smstep3note ,smstep3,smstep3
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
OKhere is code :)
This generates the SQL lists:
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#
The above query works fine.
This is the query that chokes:
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
And, here is the output from the above SQL (cut and pasted into a
):
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.:
then in the query:
insert into table1(#list1#)
values (#list2#)
When I output the generated SQL, it looks perfectbut SQL seems to be
having a problem with the ' (single quotes) surrounding strings I've
tried preservesinglequot
RE: ' in SQL 2000
Sorry diannaforgot to explain To build a SQL string in a stored proc you need to declare a variable and then build up the string eg: DECLARE @sqlstring as varchar(2000) SELECT @sqlstring = "Select * FROM table WHERE field = " & @field & etc... Then execute the string once you have built it up by doing EXEC(sqlstring) I'm not sure this will solve your problem although it may. As I said before I have encountered problems before where SQL didn't like dynamic field names. -- 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:16 To: CF-Talk Subject: RE: ' in SQL 2000 I'm not sure what you mean by "build up a sql string in SQL and execute it using EXEC(sqlstring)" .. (Recent convert from Access to SQL - and still learning the tools) ?? 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 10:11 AM To: CF-Talk Subject: RE: ' in SQL 2000 Can you output the generated SQL and post it to this list. In the meantime you could always try to build up a sql string in SQL and execute it using EXEC(sqlstring) to see if this works. Sometimes I've found it makes a difference -- 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 14:42 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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
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
OKhere is code :)
This generates the SQL lists:
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#
The above query works fine.
This is the query that chokes:
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
And, here is the output from the above SQL (cut and pasted into a
):
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.:
then in the query:
insert into table1(#list1#)
values (#list2#)
When I output the generated SQL, it looks perfectbut 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
RE: ' in SQL 2000
Wish it was that easy.that was my first guess, though. (See the code I posted in another message). Thanks anyway! 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 Cortes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 11:02 AM To: CF-Talk Subject: RE: ' in SQL 2000 Diana, Could you by chance have stray single quotes in the data you are trying to insert? - Chris Christopher Cortes |Coming Q3 2001: Consultant | Stonebridge Technologies, Inc. | Optimizing ColdFusion Putting Technology To Work | By Visit us at www.sbti.com | Christopher Cortes Ofc: 713.985.6244 cel: 281.235.6100 | www.osborne.com <---!> -Original Message- From: Diana Nichols [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 8: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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
RE: ' in SQL 2000
I'm not sure what you mean by "build up a sql string in SQL and execute it using EXEC(sqlstring)" .. (Recent convert from Access to SQL - and still learning the tools) ?? 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 10:11 AM To: CF-Talk Subject: RE: ' in SQL 2000 Can you output the generated SQL and post it to this list. In the meantime you could always try to build up a sql string in SQL and execute it using EXEC(sqlstring) to see if this works. Sometimes I've found it makes a difference -- 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 14:42 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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
RE: ' in SQL 2000
OKhere is code :)
This generates the SQL lists:
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#
The above query works fine.
This is the query that chokes:
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
And, here is the output from the above SQL (cut and pasted into a
):
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.:
then in the query:
insert into table1(#list1#)
values (#list2#)
When I output the generated SQL, it looks perfectbut 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
RE: ' in SQL 2000
Diana, Could you by chance have stray single quotes in the data you are trying to insert? - Chris Christopher Cortes |Coming Q3 2001: Consultant | Stonebridge Technologies, Inc. | Optimizing ColdFusion Putting Technology To Work | By Visit us at www.sbti.com | Christopher Cortes Ofc: 713.985.6244 cel: 281.235.6100 | www.osborne.com <---!> -Original Message- From: Diana Nichols [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 8: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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
RE: ' in SQL 2000
Can you output the generated SQL and post it to this list. In the meantime you could always try to build up a sql string in SQL and execute it using EXEC(sqlstring) to see if this works. Sometimes I've found it makes a difference -- 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 14:42 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.: then in the query: insert into table1(#list1#) values (#list2#) When I output the generated SQL, it looks perfectbut 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
