RE: Running a Dynamically Created Query
I did a cfoutput to check and see what the query looked like before running it on SQL Server. ... UserID=1 and GroupID=56 and City = 'Atlanta' . So I know that it has single quotes on it before executing the query. I have also made sure that all entries are trimmed. I have also used LIKE instead of =. Chad -Original Message- From: Nat Papovich [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 19, 2000 12:51 AM To: CF-Talk Subject: RE: Running a Dynamically Created Query The best, fastest thing to try would be to remove the cfquery tag and replace it with a cfoutput tag. That way, you can see exactly what is happening, and what all the variables are evaluating to. But maybe you've already done that... Are you using PreserveSingleQuotes()? Nat Papovich ICQ 32676414 "If it was hard to write," says the Real Programmer, "it should be hard to understand." -Original Message- From: Chad Elley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 18, 2000 9:19 PM To: CF-Talk Subject: Running a Dynamically Created Query I have set up a series of Cold Fusion templates that build a dynamic SQL statement. I have run into a "stump in the road" as a result. The SQL statement consists almost entirely of variables equaling an integer, but when I have a variable equal to a string, I get errors. For example, when I attempt to drop a value enclosed in single quotes (i.e. city = 'Atlanta'), I get errors. Here is the problem though... We can copy the code into SQL server 2K and it runs perfectly, but when it executes it through Cold Fusion passing it into SQL server, it bombs out. Any suggestions? Thanks, Chad Elley Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Running a Dynamically Created Query
Chad, Nat and the others are correct; you need to surround the City variable with PreserveSingleQuotes(). It looks fine before running it because CF's friendly single-quote-escaper hasn't yet acted upon it, but it will, it will. -David On Thu, 19 Oct 2000 08:21:36 -0400 "Chad Elley" [EMAIL PROTECTED] writes: I did a cfoutput to check and see what the query looked like before running it on SQL Server. ... UserID=1 and GroupID=56 and City = 'Atlanta' . So I know that it has single quotes on it before executing the query. I have also made sure that all entries are trimmed. I have also used LIKE instead of =. Chad -Original Message- From: Nat Papovich [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 19, 2000 12:51 AM To: CF-Talk Subject: RE: Running a Dynamically Created Query The best, fastest thing to try would be to remove the cfquery tag and replace it with a cfoutput tag. That way, you can see exactly what is happening, and what all the variables are evaluating to. But maybe you've already done that... Are you using PreserveSingleQuotes()? Nat Papovich ICQ 32676414 "If it was hard to write," says the Real Programmer, "it should be hard to understand." -Original Message- From: Chad Elley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 18, 2000 9:19 PM To: CF-Talk Subject: Running a Dynamically Created Query I have set up a series of Cold Fusion templates that build a dynamic SQL statement. I have run into a "stump in the road" as a result. The SQL statement consists almost entirely of variables equaling an integer, but when I have a variable equal to a string, I get errors. For example, when I attempt to drop a value enclosed in single quotes (i.e. city = 'Atlanta'), I get errors. Here is the problem though... We can copy the code into SQL server 2K and it runs perfectly, but when it executes it through Cold Fusion passing it into SQL server, it bombs out. Any suggestions? Thanks, Chad Elley YOU'RE PAYING TOO MUCH FOR THE INTERNET! Juno now offers FREE Internet Access! Try it today - there's no risk! For your FREE software, visit: http://dl.www.juno.com/get/tagj. Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Running a Dynamically Created Query
I have set up a series of Cold Fusion templates that build a dynamic SQL statement. I have run into a "stump in the road" as a result. The SQL statement consists almost entirely of variables equaling an integer, but when I have a variable equal to a string, I get errors. For example, when I attempt to drop a value enclosed in single quotes (i.e. city = 'Atlanta'), I get errors. Here is the problem though... We can copy the code into SQL server 2K and it runs perfectly, but when it executes it through Cold Fusion passing it into SQL server, it bombs out. Any suggestions? Thanks, Chad Elley Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Running a Dynamically Created Query
The best, fastest thing to try would be to remove the cfquery tag and replace it with a cfoutput tag. That way, you can see exactly what is happening, and what all the variables are evaluating to. But maybe you've already done that... Are you using PreserveSingleQuotes()? Nat Papovich ICQ 32676414 "If it was hard to write," says the Real Programmer, "it should be hard to understand." -Original Message- From: Chad Elley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 18, 2000 9:19 PM To: CF-Talk Subject: Running a Dynamically Created Query I have set up a series of Cold Fusion templates that build a dynamic SQL statement. I have run into a "stump in the road" as a result. The SQL statement consists almost entirely of variables equaling an integer, but when I have a variable equal to a string, I get errors. For example, when I attempt to drop a value enclosed in single quotes (i.e. city = 'Atlanta'), I get errors. Here is the problem though... We can copy the code into SQL server 2K and it runs perfectly, but when it executes it through Cold Fusion passing it into SQL server, it bombs out. Any suggestions? Thanks, Chad Elley Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Running a Dynamically Created Query
#preserveSingleQuotes(sqlStatement)# Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: Running a Dynamically Created Query
Chad- Make sure you are using the PreserveSingleQuotes function around any string value. ColdFusion will convert your single quotes to double quotes unless you specify otherwise, and this can throw SQL Server off. Norman Elton Information Technology College of William Mary -Original Message- From: Chad Elley [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 19, 2000 12:19 AM To: CF-Talk Subject: Running a Dynamically Created Query I have set up a series of Cold Fusion templates that build a dynamic SQL statement. I have run into a "stump in the road" as a result. The SQL statement consists almost entirely of variables equaling an integer, but when I have a variable equal to a string, I get errors. For example, when I attempt to drop a value enclosed in single quotes (i.e. city = 'Atlanta'), I get errors. Here is the problem though... We can copy the code into SQL server 2K and it runs perfectly, but when it executes it through Cold Fusion passing it into SQL server, it bombs out. Any suggestions? Thanks, Chad Elley Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
Re: Running a Dynamically Created Query
FYI, CF doesn't convert single quotes to double quotes. It escapes
single quotes with another single quote. Here's what the help says:
--
Developing Web Applications with ColdFusion | Selecting and Presenting
Data | Using Dynamic Query Parameters
ODBC allows you to denote a single quote inside a quote-delimited string
by using two consecutive single quote characters (''). Since you can't
possibly be expected to do this yourself, ColdFusion automatically
replaces the single quote (') with two single quotes (") before including
parameter values in SQL statements.
In special cases, you might want to suppress automatic escaping of single
quotes. To do this, use the PreserveSingleQuotes function.
--
-David
On Thu, 19 Oct 2000 00:52:25 -0400 "Norman Elton" [EMAIL PROTECTED] writes:
Make sure you are using the PreserveSingleQuotes function around
any string value. ColdFusion will convert your single quotes to double
quotes unless you specify otherwise.
YOU'RE PAYING TOO MUCH FOR THE INTERNET!
Juno now offers FREE Internet Access!
Try it today - there's no risk! For your FREE software, visit:
http://dl.www.juno.com/get/tagj.
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
