Re: CFMX Insert query problem
On 3/3/03, Marius Milosav penned: use: cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# and it should take care of the problem It is a change in behavior between CF5 and CFMX Hi. Nope, that doesn't do it Marius. That breaks the UPDATE statement. On INSERT cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# works. #form.product_name# throws an error. On UPDATE #form.product_name# cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# inserts double apostrophes (Bud''s Maid Service). Any clues? Hi All. I'm receiving an error on a database INSERT... [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Bud's Maid Service',etc... from the single quote in Bud's. If I escape the quote with another quote, there's no problem. The value I'm entering is simple: INSERT INTO Products (Product_Name,Etc...) VALUES ('#form.product_name#',etc...) Does anyone know if this error would be cause by the JDBC driver itself? Or by CFMX? Just curious before I report the error to Crystal Tech. Now, I understand that if I was hard coding it in, I'd have to write it like... INSERT INTO Products (Product_Name,Etc...) VALUES ('Bud''s Maid Service',etc...) I can fix it by doing... #replace(form.product_name, ', '', ALL)# But then of course on all non-broken systems it will be inserted as Bud''s Maid Service. The UPDATE query works correctly with the same variable... UPDATE Products SET Product_Name='#form.product_name#',etc... -- -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
The only time I have seen that single quotes are doubled when using cfqueryparam is when the string is trimmed prior to be used in a database transaction. If I remember correctly there was a discussion last week on the list to this effect. Check the archives and see if you can find anything Marius - Original Message - From: Bud [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, March 04, 2003 2:00 PM Subject: Re: CFMX Insert query problem On 3/3/03, Marius Milosav penned: use: cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# and it should take care of the problem It is a change in behavior between CF5 and CFMX Hi. Nope, that doesn't do it Marius. That breaks the UPDATE statement. On INSERT cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# works. #form.product_name# throws an error. On UPDATE #form.product_name# cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# inserts double apostrophes (Bud''s Maid Service). Any clues? Hi All. I'm receiving an error on a database INSERT... [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Bud's Maid Service',etc... from the single quote in Bud's. If I escape the quote with another quote, there's no problem. The value I'm entering is simple: INSERT INTO Products (Product_Name,Etc...) VALUES ('#form.product_name#',etc...) Does anyone know if this error would be cause by the JDBC driver itself? Or by CFMX? Just curious before I report the error to Crystal Tech. Now, I understand that if I was hard coding it in, I'd have to write it like... INSERT INTO Products (Product_Name,Etc...) VALUES ('Bud''s Maid Service',etc...) I can fix it by doing... #replace(form.product_name, ', '', ALL)# But then of course on all non-broken systems it will be inserted as Bud''s Maid Service. The UPDATE query works correctly with the same variable... UPDATE Products SET Product_Name='#form.product_name#',etc... -- -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
On 3/4/03, Marius Milosav penned: The only time I have seen that single quotes are doubled when using cfqueryparam is when the string is trimmed prior to be used in a database transaction. If I remember correctly there was a discussion last week on the list to this effect. Check the archives and see if you can find anything Thanks. I am trimming it. I always do that. Man, they sure don't make it easy. This is still either a CF or JDBC Driver problem though. If it was an intended action, I believe it would work the same on insert and update. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
Bud wrote: On 3/4/03, Marius Milosav penned: The only time I have seen that single quotes are doubled when using cfqueryparam is when the string is trimmed prior to be used in a database transaction. If I remember correctly there was a discussion last week on the list to this effect. Thanks. I am trimming it. I always do that. This is still either a CF or JDBC Driver problem though. If it was an intended action, I believe it would work the same on insert and update. It is a bug in CF MX, not in the JDBC driver. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
On 3/4/03, Marius Milosav penned: The only time I have seen that single quotes are doubled when using cfqueryparam is when the string is trimmed prior to be used in a database transaction. If I remember correctly there was a discussion last week on the list to this effect. Check the archives and see if you can find anything Yes, I checked and found the thread. I'm not sure if anyone ever got anything straight. The problem I'm having is, there's no rhyme nor freaking reason for anything. Same database. Same server. Same application. Inserting '#Trim(form.shippingcompany)#' into a Text field works fine. Inserting '#Trim(form.product_name)#' into another Text field throws an error. Both values have 1 single quote in them. The text fields are identical except for length, one is 80, one is 255. I can't see THAT making a difference. Updating works fine no matter what I do. They should take this product back to the drawing board. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
On 3/4/03, Jochem van Dieten penned: It is a bug in CF MX, not in the JDBC driver. Thanks Jochem. Are they aware of it? Is there a bug report form I can fill out? Is this Access specific or across all DBs? -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
Bud wrote: On 3/4/03, Jochem van Dieten penned: It is a bug in CF MX, not in the JDBC driver. Thanks Jochem. Are they aware of it? Is there a bug report form I can fill out? I would expect MM is aware of this issue, but I don't know how many people that discover this issue actually fill out the bug report form, so I am not certain. And IIRC repeated reports influence the priority: http://www.macromedia.com/support/email/wishform/?6213=3 Is this Access specific or across all DBs? Across DBs. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
Well, I found a workaround by accident. I went ahead and took the queries that were giving me problems, cut them from the templates they were in, saved them to a file, then made a copy of the file. On the copy I changed all the input parameters to cfqueryparam tags. Then I was going to do a cfinclude that you could just change to the other file and use the one that worked. That's why in my last post I'd said: +++ The problem I'm having is, there's no rhyme nor freaking reason for anything. Same database. Same server. Same application. Inserting '#Trim(form.shippingcompany)#' into a Text field works fine. Inserting '#Trim(form.product_name)#' into another Text field throws an error. Both values have 1 single quote in them. +++ Well, the first one I'd already put into a cfinclude. When I put the other one in a cfinclude, it worked also. To make a long story short. if Inserting '#Trim(your_variable)#' into a Text field gives you problems with MX. Try pasting th query into a file and calling it with cfinclude. Worked for me. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
use: cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# and it should take care of the problem It is a change in behavior between CF5 and CFMX Thanks Marius Milosav www.scorpiosoft.com It's not about technology, it's about people. Virtual Company (VICO) Application Demo www.scorpiosoft.com/vicodemo/login.cfm - Original Message - From: Bud [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, March 03, 2003 7:51 PM Subject: CFMX Insert query problem Hi All. I'm receiving an error on a database INSERT... [MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Bud's Maid Service',etc... from the single quote in Bud's. If I escape the quote with another quote, there's no problem. The value I'm entering is simple: INSERT INTO Products (Product_Name,Etc...) VALUES ('#form.product_name#',etc...) Does anyone know if this error would be cause by the JDBC driver itself? Or by CFMX? Just curious before I report the error to Crystal Tech. Now, I understand that if I was hard coding it in, I'd have to write it like... INSERT INTO Products (Product_Name,Etc...) VALUES ('Bud''s Maid Service',etc...) I can fix it by doing... #replace(form.product_name, ', '', ALL)# But then of course on all non-broken systems it will be inserted as Bud''s Maid Service. The UPDATE query works correctly with the same variable... UPDATE Products SET Product_Name='#form.product_name#',etc... -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
On 3/3/03, Marius Milosav penned: use: cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# and it should take care of the problem I'll try that, thanks. Curious. What version was cfqueryparam introduced in? It is a change in behavior between CF5 and CFMX I don't think that's the case, because if it was UPDATE would have changed also. UPDATE works as it always has. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CFMX Insert query problem
I think in 4.5 Marius Milosav www.scorpiosoft.com It's not about technology, it's about people. Virtual Company (VICO) Application Demo www.scorpiosoft.com/vicodemo/login.cfm - Original Message - From: Bud [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:11 PM Subject: Re: CFMX Insert query problem On 3/3/03, Marius Milosav penned: use: cfqueryparam cfsqltype=cf_sql_varchar value=#form.product_name# and it should take care of the problem I'll try that, thanks. Curious. What version was cfqueryparam introduced in? It is a change in behavior between CF5 and CFMX I don't think that's the case, because if it was UPDATE would have changed also. UPDATE works as it always has. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ http://www.cf-ezcart.com/ 954.721.3452 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4