Syntax error in SQL statement
I am not a SQL expert but I play one on TV :) I have the following query select DataLoadStatus,DataLoadData,DataLoadFileLineNumber,exception.description from PUB.DataCenterDataload left outer join PUB.DataCenterHoldingArea on(DataCenterDataload.DataLoadNumber = DataCenterHoldingArea.DataLoadnumber left outer join DataCenterLoadError on(DataCenterHoldingArea.DataLoadNumber = DataCenterLoadError.DataLoadNumber and DataCenterHoldingArea.DataLoadFileLineNumber = DataCenterLoadError.DataLoadFileLineNumber) left outer join exception on(DataCenterLoadError.DataLoadError = exception.excepcode) where DataCenterDataLoad.DataLFileName = '#vfile#' order by DataLoadFileLineNumber I am getting a syntax error on or after 'left outer join DataCenterLoadError' The table relationships are correct as are the join structures left outers. Can someone point me to where I am going wrong. I have been staring at it for most of the past hour. Thanks --S ^ -- Erma Bombeck - Never have more children than you have car windows. - http://www.brainyquote.com/quotes/authors/e/erma_bombeck.html ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323459 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Syntax error in SQL statement
Steve Lichtenberg wrote: I am not a SQL expert but I play one on TV :) I have the following query select DataLoadStatus,DataLoadData,DataLoadFileLineNumber,exception.description from PUB.DataCenterDataload left outer join PUB.DataCenterHoldingArea on(DataCenterDataload.DataLoadNumber = DataCenterHoldingArea.DataLoadnumber left outer join DataCenterLoadError on(DataCenterHoldingArea.DataLoadNumber = DataCenterLoadError.DataLoadNumber and DataCenterHoldingArea.DataLoadFileLineNumber = DataCenterLoadError.DataLoadFileLineNumber) left outer join exception on(DataCenterLoadError.DataLoadError = exception.excepcode) where DataCenterDataLoad.DataLFileName = '#vfile#' order by DataLoadFileLineNumber I am getting a syntax error on or after 'left outer join DataCenterLoadError' The table relationships are correct as are the join structures left outers. Can someone point me to where I am going wrong. I have been staring at it for most of the past hour. Don't you need another close parantheses after the first ON? -- Visit http://www.misshunt.com/ for fun and creative items including the famous Clean/Dirty dishwasher magnet, now available in velcro. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323461 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax error in SQL statement
Doesn't look like any of the parens are actually necessary on this query, are they? Shot in the dark here, but is exception a reserved word in SQL Server? ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323463 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax error in SQL statement
Does this work? SELECT DataLoadStatus , DataLoadData , DataLoadFileLineNumber , EX.description FROM pub.DataCenterDataload DL LEFT JOIN pub.DataCenterHoldingArea HA ON DL.DataLoadNumber = HA.DataLoadnumber LEFT JOIN DataCenterLoadError LE ON HA.DataLoadNumber = LE.DataLoadNumber AND HA.DataLoadFileLineNumber = LE.DataLoadFileLineNumber LEFT JOIN [exception] EX ON LE.DataLoadError = EX.excepcode WHERE DL.DataLFileName = cfqueryparam value=#vfile# cfsqltype=cf_sql_varchar/ ORDER BY DataLoadFileLineNumber ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323465 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Syntax error
I forgot the code snippet. cfquery name=qTransCheck datasource=#DSN# dbtype=odbc select distinct(count prodcode) from product where product.manufid = '#scan.manufid#' cfif variables.stError.v010.occurred AND product.famcode2 matches '#left(scan.famcode, 2)#.' /cfif cfif variables.stError.v012.occurred ANDproduct.famcode2 matches '#left(scan.famcode,1)#..' /cfif cfif variables.stError.v008.occurred!--- Nothing ---/cfif cfif not variables.stError.v010.occurred and not variables.stError.v012.occurred and not variables.stError.v008.occurred AND product.famcode2 = '#scan.famcode#' /cfif and product.famcode '000' and product.prodcode not in (select prodcode from product where manufid='#scan.manufid#' cfif variables.stError.v010.occurred and product.famcode matches '#left(scan.famcode, 2)#.') /cfif cfif variables.stError.v012.occurred and product.famcode matches '#left(scan.famcode,1)#..') /cfif cfif variables.stError.v008.occurred ) !--- Nothing --- /cfif cfif not variables.stError.v010.occurred and not variables.stError.v012.occurred and not variables.stError.v008.occurred AND product.famcode = '#scan.famcode#' ) /cfif /cfquery On Mon, Mar 16, 2009 at 3:45 PM, Steve Lichtenberg slich...@starpower.net wrote: Can anyone point me to the error in this code and the generated query? I am kind of stuck right now as it all looks ok to me. Thanks --S ^ [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about prodcode) from product where product.man (10713) The error occurred in C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 191 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 123 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 191 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 123 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 189 : not variables.stError.v008.occurred 190 : AND product.famcode = '#scan.famcode#' ) 191 : /cfif 192 : /cfquery 193 : /cfif SQLselect distinct(count prodcode) from product where product.manufid = '030030' AND product.famcode2 = '111' and product.famcode '000' and product.prodcode not in (select prodcode from product where manufid='030030' AND product.famcode = '111' ) -- Jean Anouilh - What you get free costs too much. -- Jean Anouilh - What you get free costs too much. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320553 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
SQL Syntax error
Can anyone point me to the error in this code and the generated query? I am kind of stuck right now as it all looks ok to me. Thanks --S ^ [Macromedia][SequeLink JDBC Driver][ODBC Socket][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about prodcode) from product where product.man (10713) The error occurred in C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 191 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 123 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 191 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 123 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 Called from C:\htdocs\couponchek.com\barcode\pdf_bc_results.cfm: line 1 189 : not variables.stError.v008.occurred 190 : AND product.famcode = '#scan.famcode#' ) 191 : /cfif 192 : /cfquery 193 : /cfif SQLselect distinct(count prodcode) from product where product.manufid = '030030' AND product.famcode2 = '111' and product.famcode '000' and product.prodcode not in (select prodcode from product where manufid='030030' AND product.famcode = '111' ) -- Jean Anouilh - What you get free costs too much. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320554 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: SQL Syntax error
This is invalid: distinct(count prodcode) Are you trying to do a DISTINCT on the aggregate function COUNT()? Not sure that would work, but it would have to be DISTINCT COUNT(prodcode) if that's what you're after. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320555 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Syntax error
Thanks. I found it finally. The right syntax (for this issue) is select count(distinct prodcode) as prodcnt There was a second issue with computed_field_1 the was being bounced by the ODBC driver as well. On Mon, Mar 16, 2009 at 3:54 PM, Jason Fisher ja...@wanax.com wrote: This is invalid: distinct(count prodcode) Are you trying to do a DISTINCT on the aggregate function COUNT()? Not sure that would work, but it would have to be DISTINCT COUNT(prodcode) if that's what you're after. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320556 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Syntax error
Interesting ... COUNT(DISTINCT prodcode) will work, huh? That may come in handy some day. Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320557 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Syntax error
COUNT(DISTINCT [fieldName] is very important to remember when using JOINS. For example, if you have the query... SELECT COUNT(A.code) AS total FROM TableA A INNER JOIN TableB ON A.code = B.code And you wanted to know how many records in TableA have records in TableB. If this query returns 10 records, there may only be 3 DISTINCT records returned from TableA. If you use COUNT(A.code) as written above, the result will be 10. If you change it to COUNT(DISTINCT A.code), you will get your desired answer of 3. Interesting ... COUNT(DISTINCT prodcode) will work, huh? That may come in handy some day. Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320558 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Syntax error
worked for me. Plus, that is what Adobe shows in their docs. 2009/3/16 Jason Fisher ja...@wanax.com: Interesting ... COUNT(DISTINCT prodcode) will work, huh? That may come in handy some day. Thanks! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320559 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
After a bit (!) of research, I am still a little confused about using CFQUERYPARAM. It is not in my SAMs Teach Yourself Coldfusion Express in 24 Hrs. Probably not. I'm not sure CFQUERYPARAM exists in CF Express. CFQUERYPARAM was introduced around the same time that CF Express came out, but CF Express was not really very useful when it came out, and things have changed quite a bit since it came out, making it even less useful. It's not necessary for the completion of your exercises to use CFQUERYPARAM, but you would definitely want to use that in a real application. To learn more about CFQUERYPARAM, read the online CF 8 documentation and this Adobe Devnet article: http://www.adobe.com/support/documentation/en/coldfusion/ http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html Understand that CS_SQL_BIT can be 1 or 0. But also found that msAccess CheckBox data can be 1,0 or NULL. NULL is the absence of a value. Any field can be set to allow NULL, unless the field is part of a unique index or something similar (identity columns, etc). Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317268 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
I just want to point out that you should be using cfqueryparam around all of those form elements. The way you have it will last about 2 hours before someone hacks into your database. see: http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignoredhttp://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored for details At 11:45 AM 12/28/2008, you wrote: Thanks, but that is supposed to be the right bracket of the SET Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Yep, but syntactically (sqlwise) you can't have the construct (opening and closing bracket) the way it's laid out unlike your INSERT statements. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317222 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
How can CFQUERYPARAM be applied to a boolean (Yes/No) field ? I cannot find it in the CDSQLTYPE list - Original Message - From: Al Musella, DPM muse...@virtualtrials.com To: cf-talk cf-talk@houseoffusion.com Sent: Monday, December 29, 2008 5:40 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options I just want to point out that you should be using cfqueryparam around all of those form elements. The way you have it will last about 2 hours before someone hacks into your database. see: http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignoredhttp://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored for details At 11:45 AM 12/28/2008, you wrote: Thanks, but that is supposed to be the right bracket of the SET Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Yep, but syntactically (sqlwise) you can't have the construct (opening and closing bracket) the way it's laid out unlike your INSERT statements. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317225 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
How can CFQUERYPARAM be applied to a boolean (Yes/No) field ? I cannot find it in the CFSQLTYPE list - Original Message - From: Al Musella, DPM muse...@virtualtrials.com To: cf-talk cf-talk@houseoffusion.com Sent: Monday, December 29, 2008 5:40 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options I just want to point out that you should be using cfqueryparam around all of those form elements. The way you have it will last about 2 hours before someone hacks into your database. see: http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignoredhttp://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored for details At 11:45 AM 12/28/2008, you wrote: Thanks, but that is supposed to be the right bracket of the SET Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Yep, but syntactically (sqlwise) you can't have the construct (opening and closing bracket) the way it's laid out unlike your INSERT statements. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317226 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
How can CFQUERYPARAM be applied to a boolean (Yes/No) field ? I cannot find it in the CFSQLTYPE list Use BIT. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317228 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: CF Express ... Syntax error - CFQUERY - UPDATE Options
Use the bit field and 1's or 0's. Mark A. Kruger, CFG, MCSE (402) 408-3733 ext 105 www.cfwebtools.com www.coldfusionmuse.com www.necfug.com -Original Message- From: BobSharp [mailto:bobsh...@ntlworld.com] Sent: Monday, December 29, 2008 12:21 PM To: cf-talk Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options How can CFQUERYPARAM be applied to a boolean (Yes/No) field ? I cannot find it in the CDSQLTYPE list - Original Message - From: Al Musella, DPM muse...@virtualtrials.com To: cf-talk cf-talk@houseoffusion.com Sent: Monday, December 29, 2008 5:40 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options I just want to point out that you should be using cfqueryparam around all of those form elements. The way you have it will last about 2 hours before someone hacks into your database. see: http://www.forta.com/blog/index.cfm/2005/12/21/SQL-Injection-Attacks-Easy-T o-Prevent-But-Apparently-Still-Ignoredhttp://www.forta.com/blog/index.cfm/2 005/12/21/SQL-Injection-Attacks-Easy-To-Prevent-But-Apparently-Still-Ignored for details At 11:45 AM 12/28/2008, you wrote: Thanks, but that is supposed to be the right bracket of the SET Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Yep, but syntactically (sqlwise) you can't have the construct (opening and closing bracket) the way it's laid out unlike your INSERT statements. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317229 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
CF Express ... Syntax error - CFQUERY - UPDATE Options
Can anyone tell me exactly what is causing this error ? !---Update using a CFQUERY with SQL UPDATE --- CFquery NAME=qUpdate DATASOURCE=learncfe!--- error --- UPDATE GuestBook SET (GuestName = '#Form.GuestName#', Email = '#Form.Email#', FirstVisit = #Form.FirstVisit#, GotHere = '#Form.GotHere#', Rating = '#Form.Rating#', Comments = '#trim(Form.Comments)#') WHERE GuestBook_ID = #Form.GuestBook_ID# /CFquery ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (43:5) to (43:88). Date/Time: 12/28/08 12:20:44 Browser: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.0.5) Gecko/2008120122 Firefox/3.0.5 Remote Address: 127.0.0.1 HTTP Referer: http://localhost/tt380/GuestMaint.cfm?op=ugid=1 Template: c:\inetpub\wwwroot\tt380\GuestUpdate.cfm ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317173 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317174 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Sorry that should be Comments = '#trim(Form.Comments)#)' ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317175 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
On Sun, Dec 28, 2008 at 11:33 AM, Jide Aliu jide_a...@hotmail.com wrote: Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Remove the opening bracket as well CFquery NAME=qUpdate DATASOURCE=learncfe!--- error --- UPDATE GuestBook SET GuestName = '#Form.GuestName#', Email = '#Form.Email#', FirstVisit = #Form.FirstVisit#, GotHere = '#Form.GotHere#', Rating = '#Form.Rating#', Comments = '#trim(Form.Comments)#' WHERE GuestBook_ID = #Form.GuestBook_ID# /CFquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317176 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Thanks, but that is supposed to be the right bracket of the SET - Original Message - From: Jide Aliu jide_a...@hotmail.com To: cf-talk cf-talk@houseoffusion.com Sent: Sunday, December 28, 2008 4:33 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317177 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Remove the opening bracket as well CFquery NAME=qUpdate DATASOURCE=learncfe!--- error --- UPDATE GuestBook SET GuestName = '#Form.GuestName#', Email = '#Form.Email#', FirstVisit = #Form.FirstVisit#, GotHere = '#Form.GotHere#', Rating = '#Form.Rating#', Comments = '#trim(Form.Comments)#' WHERE GuestBook_ID = #Form.GuestBook_ID# /CFquery 'Scuse my mad typing, yes Casey is right it's the opening and closing brackets. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317178 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Woo !Thanks for that. works ok now. - Original Message - From: Casey Dougall ca...@uberwebsitesolutions.com To: cf-talk cf-talk@houseoffusion.com Sent: Sunday, December 28, 2008 4:37 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options On Sun, Dec 28, 2008 at 11:33 AM, Jide Aliu jide_a...@hotmail.com wrote: Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Remove the opening bracket as well CFquery NAME=qUpdate DATASOURCE=learncfe!--- error --- UPDATE GuestBook SET GuestName = '#Form.GuestName#', Email = '#Form.Email#', FirstVisit = #Form.FirstVisit#, GotHere = '#Form.GotHere#', Rating = '#Form.Rating#', Comments = '#trim(Form.Comments)#' WHERE GuestBook_ID = #Form.GuestBook_ID# /CFquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317179 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Woo ! Thanks for thatworks ok now. - Original Message - From: Jide Aliu jide_a...@hotmail.com To: cf-talk cf-talk@houseoffusion.com Sent: Sunday, December 28, 2008 4:35 PM Subject: Re: CF Express ... Syntax error - CFQUERY - UPDATE Options Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Sorry that should be Comments = '#trim(Form.Comments)#)' ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317180 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: CF Express ... Syntax error - CFQUERY - UPDATE Options
Thanks, but that is supposed to be the right bracket of the SET Comments = '#trim(Form.Comments)#') Should be Comments = Comments = '#trim(Form.Comments)#' Yep, but syntactically (sqlwise) you can't have the construct (opening and closing bracket) the way it's laid out unlike your INSERT statements. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:317181 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
syntax error in from clause on godaddy hosting
We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311176 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
judging by the picture, it *looks* like you're missing single quotes on the variables, replace with cfqueryparam value=#variable_name# cfsqltype = CF_SQL_CHAR / (no quotes around cfqueryparam) kevin pieto wrote: We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311178 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
Probably not a good idea to post your DSN on a shared hosting box.. The query looks good... I think USER is a reserved word in queries.. but I don't know why it would have worked and then not.. On Mon, Aug 18, 2008 at 8:45 AM, kevin pieto [EMAIL PROTECTED] wrote: We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
Ensure there really is a single tick before the uname variable. This would probably gain some security benefit as a parameterized query. Try enclosing your table name with backticks: `user` http://dev.mysql.com/doc/refman/5.0/en/identifiers.html - Original Message - From: Greg Morphis [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, August 18, 2008 9:33 AM Subject: Re: syntax error in from clause on godaddy hosting ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311181 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
One more thing... I'm going to assume, since the error references the Microsoft Jet Engine, that this an Access DB.. You can enclose user in square brackets ie: [user], and that will get you around any reserved work issue. You really want to use cfqueryparams with this.Especially with the rash of SQL Injection attacks that have happened recently. kevin pieto wrote: We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311185 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: syntax error in from clause on godaddy hosting
Hmm, so it is. Please disregard the MySQL link I posted then. For some reason I was thinking it was MySQL. ~Brad - Original Message - From: Scott Stewart [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Monday, August 18, 2008 10:13 AM Subject: Re: syntax error in from clause on godaddy hosting One more thing... I'm going to assume, since the error references the Microsoft Jet Engine, that this an Access DB.. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311188 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
We moved our site over to a new hosting company (godaddy) and are now
receiving an error on admin login. It worked fine with the previous
host be now has an error with godaddy. Any suggestions?
error shown here
http://www.frontierstravel.com/pic10.jpg
Thanks for the help, however I'm not a coder and try the suggestions to no
avail. all your help is appreciated. Here's my code
!---cftry---
cfset uname=attributes.uname
cfset pword=attributes.pword
cfquery name=user_login datasource=#request.dsn#
SELECT * FROM user WHERE uname='#uname#' AND pword='#pword#'
/cfquery
!--- invalid ---
cfif user_login.recordcount EQ 0
cfset caller.uname=uname
cflock timeout=30 throwontimeout=Yes type=EXCLUSIVE
scope=SESSION
cfscript
if (structkeyexists(session, user) is false )
session.user=structNew();
session.user.admin_loggedin=false;
/cfscript
/cflock
!--- valid ---
cfelse
cflock timeout=30 throwontimeout=Yes type=EXCLUSIVE
scope=SESSION
cfscript
if (structkeyexists(session, user) is false )
session.user=structNew();
session.user.admin_loggedin=true;
session.user.userid=user_login.userid;
session.user.uname=user_login.uname;
session.user.user_fname=user_login.user_fname;
session.user.user_lname=user_login.user_lname;
session.user.is_admin=user_login.is_admin;
session.masteradmin = N;
if (session.user.uname eq ben OR
session.user.uname eq jgray4567) {
session.masteradmin = Y;
}
/cfscript
/cflock
/cfif
!---cfcatch type=Any
cflocation url=login.cfm addtoken=no
/cfcatch
/cftry---
Thanks
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311201
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: syntax error in from clause on godaddy hosting
SELECT *
FROM [user]
WHERE uname = cfqueryparam value=#uname# cfsqltype=cf_sql_char AND
pword= cfqueryparam value=#pword# cfsqltype=cf_sql_char
and...
you now need to remove this line, because you've exposed your master password
to the internet
if (session.user.uname eq ben OR session.user.uname eq jgray4567) {
session.masteradmin = Y;
}
never, ever, ever hard code this kind of thing...
kevin pieto wrote:
We moved our site over to a new hosting company (godaddy) and are now
receiving an error on admin login. It worked fine with the previous
host be now has an error with godaddy. Any suggestions?
error shown here
http://www.frontierstravel.com/pic10.jpg
Thanks for the help, however I'm not a coder and try the suggestions to no
avail. all your help is appreciated. Here's my code
!---cftry---
cfset uname=attributes.uname
cfset pword=attributes.pword
cfquery name=user_login datasource=#request.dsn#
SELECT * FROM user WHERE uname='#uname#' AND pword='#pword#'
/cfquery
!--- invalid ---
cfif user_login.recordcount EQ 0
cfset caller.uname=uname
cflock timeout=30 throwontimeout=Yes type=EXCLUSIVE
scope=SESSION
cfscript
if (structkeyexists(session, user) is false )
session.user=structNew();
session.user.admin_loggedin=false;
/cfscript
/cflock
!--- valid ---
cfelse
cflock timeout=30 throwontimeout=Yes type=EXCLUSIVE
scope=SESSION
cfscript
if (structkeyexists(session, user) is false )
session.user=structNew();
session.user.admin_loggedin=true;
session.user.userid=user_login.userid;
session.user.uname=user_login.uname;
session.user.user_fname=user_login.user_fname;
session.user.user_lname=user_login.user_lname;
session.user.is_admin=user_login.is_admin;
session.masteradmin = N;
if (session.user.uname eq ben OR
session.user.uname eq jgray4567) {
session.masteradmin = Y;
}
/cfscript
/cflock
/cfif
!---cfcatch type=Any
cflocation url=login.cfm addtoken=no
/cfcatch
/cftry---
Thanks
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311207
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg Worked Thanks so much! I did not code the site im just patching. I have removed the line u suggested. Thanks again. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311215 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: syntax error in from clause on godaddy hosting
I'm glad I could help, but you'll still need a superuser account, whomever is coding for you is going to need to add a flag to the database, and write code to check for the superuser flag.. sas kevin pieto wrote: We moved our site over to a new hosting company (godaddy) and are now receiving an error on admin login. It worked fine with the previous host be now has an error with godaddy. Any suggestions? error shown here http://www.frontierstravel.com/pic10.jpg Worked Thanks so much! I did not code the site im just patching. I have removed the line u suggested. Thanks again. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311217 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Syntax Error In Update Statement
Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305963 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305964 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
I actually want that field to be a 0 if no data was entered on the form. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:22 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305965 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Syntax Error In Update Statement
What error does CF give? On Fri, May 23, 2008 at 3:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305966 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:25 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement What error does CF give? On Fri, May 23, 2008 at 3:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
On Fri, May 23, 2008 at 1:25 PM, [EMAIL PROTECTED] wrote: I actually want that field to be a 0 if no data was entered on the form. then the 0 value should be surrounded by single quotes? -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:22 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305968 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
Also.. what is location? Do yow know what a CLOB is? That's a HUGE character field.. can that not be a varchar? On Fri, May 23, 2008 at 3:25 PM, [EMAIL PROTECTED] wrote: I actually want that field to be a 0 if no data was entered on the form. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:22 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305969 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Syntax Error In Update Statement
Even if it is a numeric field in the database? I have tried removing the entire statement from the update query and it still gives an error. Thanks, Patrick J. Hedgepath Pegasus Web Productions [EMAIL PROTECTED] 803-996-0578 -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:30 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:25 PM, [EMAIL PROTECTED] wrote: I actually want that field to be a 0 if no data was entered on the form. then the 0 value should be surrounded by single quotes? -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:22 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305970 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Syntax Error In Update Statement
Is this the same query? The query you posted was using a different table cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) The query below uses Physician On Fri, May 23, 2008 at 3:28 PM, [EMAIL PROTECTED] wrote: One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:25 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement What error does CF give? On Fri, May 23, 2008 at 3:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305971 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
Location is a number field. When I try changing it to cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 I still get an error. In fact when I remove the statement entirely I still get the error. -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:30 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement Also.. what is location? Do yow know what a CLOB is? That's a HUGE character field.. can that not be a varchar? On Fri, May 23, 2008 at 3:25 PM, [EMAIL PROTECTED] wrote: I actually want that field to be a 0 if no data was entered on the form. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:22 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif shouldn't your cfelse be an empty string () instead of a 0? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305972 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
The query you posted (UPDATE HomeCareCompany) is not the query reporting the error ( UPDATE Physician ) On Fri, May 23, 2008 at 3:28 PM, [EMAIL PROTECTED] wrote: One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# http://form.id/# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305973 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
Ack sorry :( Having the same problem with another DW created query on another page. Here is the correct error. Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditCompany.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE HomeCareCompany SET Active= 1 , Location= (param 1) , CompanyName= (param 2) , Email= (param 3) , Password= '' , FirstName= (param 4) , LastName= (param 5) , Address= (param 6) , City= (param 7) , State= (param 8) , Zip= (param 9) , Phone= (param 10) , Fax= '' WHERE ID= (param 11) VENDORERRORCODE 3092 -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:33 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement Is this the same query? The query you posted was using a different table cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) The query below uses Physician On Fri, May 23, 2008 at 3:28 PM, [EMAIL PROTECTED] wrote: One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:25 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement What error does CF give? On Fri, May 23, 2008 at 3:14 PM, Patrick Hedgepath [EMAIL PROTECTED] wrote: Can anybody see the syntax error that CF8 keeps giving me for this query? The query was made by DW CS3 and worked until I added a new field by hand (Location). I can't see any reason it would not work. cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=cf_sql_clob maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305974 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
On Fri, May 23, 2008 at 1:32 PM, [EMAIL PROTECTED] wrote: Even if it is a numeric field in the database? I have tried removing the entire statement from the update query and it still gives an error. the cfsqltype you specified is CLOB. -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305975 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
If it's numeric in the DB it should NOT be cf_sql_clob On Fri, May 23, 2008 at 3:34 PM, morgan l [EMAIL PROTECTED] wrote: The query you posted (UPDATE HomeCareCompany) is not the query reporting the error ( UPDATE Physician ) On Fri, May 23, 2008 at 3:28 PM, [EMAIL PROTECTED] wrote: One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# http://form.id/# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 4:37 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement If it's numeric in the DB it should NOT be cf_sql_clob On Fri, May 23, 2008 at 3:34 PM, morgan l [EMAIL PROTECTED] wrote: The query you posted (UPDATE HomeCareCompany) is not the query reporting the error ( UPDATE Physician ) On Fri, May 23, 2008 at 3:28 PM, [EMAIL PROTECTED] wrote: One that is not very helpful :( Error Executing Database Query. Syntax error in UPDATE statement. The error occurred in C:\users\respiratorydiagnosticsllc.com\www\admin\EditPhysician.cfm: line 71 69 : '' 70 : /cfif 71 : WHERE ID=cfqueryparam value=#FORM.ID# http://form.id/# cfsqltype=cf_sql_numeric 72 : /cfquery 73 : cflocation url=main.cfm SQLSTATE nbsp; SQLUPDATE Physician SET Active= 1 , NPI= (param 1) , FirstName= (param 2) , LastName= (param 3) , Suffix= (param 4) , Email= (param 5) , Password= (param 6) , Address= (param 7) , City= (param 8) , State= (param 9) , Zip= (param 10) , Phone= (param 11) , Fax= '' WHERE ID= (param 12) VENDORERRORCODE 3092 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305977 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305978 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Syntax Error In Update Statement
It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305979 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
Holy F!?!?!?! .. surely you don't have the password, phone number, fax number, email and company name. I think this email just gave every DB person on this list a heart attack. Clob is for storing GIGS of data. Those are varchar's if not numeric.. Fix your query first and then let us know if you still have problems. On Fri, May 23, 2008 at 4:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305980 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
why is the column State in double quotes? On Fri, May 23, 2008 at 2:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305981 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
Well first off this was code generated by Dream Weaver using the testing server environment so yell at Adobe. Can I write it from scratch myself sure but I was trying to save time. All it appears to have done is waste time. Why would this give you a heart attack. This query was working until I added the additional field like I mentioned earlier. Then even removing the field does not help now. -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:26 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement Holy F!?!?!?! .. surely you don't have the password, phone number, fax number, email and company name. I think this email just gave every DB person on this list a heart attack. Clob is for storing GIGS of data. Those are varchar's if not numeric.. Fix your query first and then let us know if you still have problems. On Fri, May 23, 2008 at 4:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305996 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Syntax Error In Update Statement
I would assume it is because it is a keyword used in SQL or Database syntax. Again Dream Weaver wrote the code. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:30 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement why is the column State in double quotes? On Fri, May 23, 2008 at 2:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305997 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax Error In Update Statement
i'm confused about so many things... 1) you're using Access, right? does Access even support a CLOB datatype? what are the datatypes in the database? 2) i don't think 'State' is a reserved word in Access (again, i'm assuming this is Access as I seem to recall somewhere in this thread that it was mentioned... please confirm). Try without the double quotes. 3) was this ever working before you put the 'location' conditional? 4) wasting way too much time shooting in the dark. whittle the statement down to a single statement using as few variables as possible. get rid of the cfqueryparam. just make it work. then add the other columns/conditionals 1 at a time. then add the cfqueryparams 1 at a time. On Fri, May 23, 2008 at 7:06 PM, [EMAIL PROTECTED] wrote: I would assume it is because it is a keyword used in SQL or Database syntax. Again Dream Weaver wrote the code. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:30 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement why is the column State in double quotes? On Fri, May 23, 2008 at 2:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305999 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Syntax Error In Update Statement
You should really write it yourself so you know what it's doing and why ..:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 10:06 PM To: CF-Talk Subject: RE: Syntax Error In Update Statement Well first off this was code generated by Dream Weaver using the testing server environment so yell at Adobe. Can I write it from scratch myself sure but I was trying to save time. All it appears to have done is waste time. Why would this give you a heart attack. This query was working until I added the additional field like I mentioned earlier. Then even removing the field does not help now. -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:26 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement Holy F!?!?!?! .. surely you don't have the password, phone number, fax number, email and company name. I think this email just gave every DB person on this list a heart attack. Clob is for storing GIGS of data. Those are varchar's if not numeric.. Fix your query first and then let us know if you still have problems. On Fri, May 23, 2008 at 4:18 PM, [EMAIL PROTECTED] wrote: It is a number field in an Access DB. Below is the entire query. cfif IsDefined(FORM.MM_UpdateRecord) AND FORM.MM_UpdateRecord EQ form1 cfquery datasource=#DSN# name=UpdateCompany UPDATE HomeCareCompany SET Active=cfif IsDefined(FORM.Active) 1 cfelse 0 /cfif , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif , CompanyName=cfif IsDefined(FORM.CompanyName) AND #FORM.CompanyName# NEQ cfqueryparam value=#FORM.CompanyName# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , Email=cfif IsDefined(FORM.Email) AND #FORM.Email# NEQ cfqueryparam value=#FORM.Email# cfsqltype=cf_sql_clob maxlength=75 cfelse '' /cfif , Password=cfif IsDefined(FORM.Password) AND #FORM.Password# NEQ cfqueryparam value=#FORM.Password# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , FirstName=cfif IsDefined(FORM.FirstName) AND #FORM.FirstName# NEQ cfqueryparam value=#FORM.FirstName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , LastName=cfif IsDefined(FORM.LastName) AND #FORM.LastName# NEQ cfqueryparam value=#FORM.LastName# cfsqltype=cf_sql_clob maxlength=40 cfelse '' /cfif , Address=cfif IsDefined(FORM.Address) AND #FORM.Address# NEQ cfqueryparam value=#FORM.Address# cfsqltype=cf_sql_clob maxlength=200 cfelse '' /cfif , City=cfif IsDefined(FORM.City) AND #FORM.City# NEQ cfqueryparam value=#FORM.City# cfsqltype=cf_sql_clob maxlength=85 cfelse '' /cfif , State=cfif IsDefined(FORM.State) AND #FORM.State# NEQ cfqueryparam value=#FORM.State# cfsqltype=cf_sql_clob maxlength=2 cfelse '' /cfif , Zip=cfif IsDefined(FORM.Zip) AND #FORM.Zip# NEQ cfqueryparam value=#FORM.Zip# cfsqltype=cf_sql_clob maxlength=10 cfelse '' /cfif , Phone=cfif IsDefined(FORM.Phone) AND #FORM.Phone# NEQ cfqueryparam value=#FORM.Phone# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif , Fax=cfif IsDefined(FORM.Fax) AND #FORM.Fax# NEQ cfqueryparam value=#FORM.Fax# cfsqltype=cf_sql_clob maxlength=15 cfelse '' /cfif WHERE ID=cfqueryparam value=#FORM.ID# cfsqltype=cf_sql_numeric /cfquery -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Friday, May 23, 2008 5:06 PM To: CF-Talk Subject: Re: Syntax Error In Update Statement On Fri, May 23, 2008 at 1:58 PM, [EMAIL PROTECTED] wrote: When I change it to CF_SQL_NUMERIC the error persists. Even if I remove the entire statement , Location=cfif IsDefined(FORM.Location) AND #FORM.Location# NEQ cfqueryparam value=#FORM.Location# cfsqltype=CF_SQL_NUMERIC maxlength=15 cfelse 0 /cfif The error still persists. is it numeric or is it integer? if the error persists without that line, can you paste the query as you currently have it? -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306002 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Syntax error (missing operator) in query expression
Syntax error (missing operator) in query expression '(getUser.UserLogin = 'paulw') AND (getUser.Password' = '2324986')'. Getting this error message on a simple login application. App starts in the application.cfm as soon as the user enters the section of site that requires login. !---If user is not logged in force them to log in--- cfif NOT isDefined(SESSION.Auth.IsLoggedIn) !---If the user is now submitting login action via login form--- !---Include Login Check code to validate the user attempting to log in.--- cfinclude template=../Templates/loginForm.cfm cfabort /cfif Here is the login page: cfif isDefined(form.UserLogin) cfinclude template=loginCheck.cfm /cfif span class=HeaderEnter Access Code/span br cfform action=../PrivateAccess/showAR.cfm Name=LoginForm method=post input type=hidden name=UserLogin_required input type=hidden name=Password_required !---userLogin Field is required--- User Name: cfinput type=text name=UserLogin size=20 value= maxlength=24 required=yes message=Please enter your user name. Thank you. style=height:14px; width:125px; font-size:12px; font-weight:bold; color: maroon; letter-spacing:0.2em; text-align:left; pnbsp;/ppnbsp;/p Password: cfinput type=password name=Password size=24 value= maxlength=24 required=yes message=Please enter your assigned password to access this part of the site. Thank you. style=height:14px; width:125px; font-size:12px; font-weight:bold; color: maroon; letter-spacing:0.2em; text-align:left cfinput type=submit name=Enter /cfform Here is the final part...the loginCheck.cfm page. !---The following code queries the database to find the record of the user's password.--- cfparam name=Form.UserLogin type=string cfparam name=Form.Password type=string cfquery name=getUser datasource=#dsn# SELECT * FROM Users WHERE (getUser.UserLogin = '#Form.UserLogin#') AND (getUser.Password' = '#Form.Password#') /cfquery !--- If the user's password is correct the following code verifies it and obtains the information then creates a distinct session for each user that logs in. --- cfif getUser.RecordCount EQ 1 cflocation url=../PrivateAccess/showAR.cfm !--- The following code remembers the user's logged-in status their user type and their first name in the session structure. --- cfset SESSION.Auth = StructNew() cfset SESSION.Auth.IsLoggedIn = yes cfset SESSION.Auth.userID = getUser.userID cfset SESSION.Auth.FirstName = getUser.FirstName !--- Now that user is logged in, the following code sends them to PrivateAccess/showAR.cfm page and menu links. --- cflocation url=#CGI.SCRIPT_NAME# /cfif I have looked over this code so much and searched the net for an explanation of why I am getting this error. Oh, and both the fields in the query are text fields; no numerical fields. Someone, PLEASE tell me what you think is going on. Thanks in advance for any help. Nick [EMAIL PROTECTED] Below is the entire error message: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(getUser.UserLogin = 'paulw') AND (getUser.Password' = '2324986')'. The error occurred in C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginCheck.cfm: line 19 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginForm.cfm: line 206 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\PrivateAccess\Application.cfm: line 21 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginCheck.cfm: line 19 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginForm.cfm: line 206 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\PrivateAccess\Application.cfm: line 21 17 :SELECT * 18 : FROM Users 19 : WHERE (getUser.UserLogin = '#Form.UserLogin#') AND (getUser.Password' = '#Form.Password#') 20 : /cfquery 21 : SQLSELECT * FROM Users WHERE (getUser.UserLogin = 'paulw') AND (getUser.Password' = '2324986') DATASOURCERickRossiter VENDORERRORCODE -3100 SQLSTATE 42000 Resources: * Check the ColdFusion documentation to verify that you are using the correct syntax. * Search the Knowledge Base to find a solution to your problem. Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.11) Gecko/20071127 Firefox/2.0.0.11 Remote Address 76.216.61.225 Referrerhttps://usnexus.com/NLRSSL/NLR/PrivateAccess/showAR.cfm Date/Time 23-Jan-08 08:53 AM Stack Trace at cfloginCheck2ecfm627279261.runPage(C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginCheck.cfm:19) at cfloginForm2ecfm2102964576.runPage(C:\Inetpub
Re: Syntax error (missing operator) in query expression
cfquery name=getUser datasource=#dsn# SELECT * FROM Users WHERE (getUser.UserLogin = '#Form.UserLogin#') AND (getUser.Password' = '#Form.Password#') /cfquery You have an extra single quote after getUser.Password. -- Josh ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297144 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Syntax error (missing operator) in query expression
AND (getUser.Password' = '#Form.Password#') Remove the single quote after getUser.Password' Code Blindness is like snow blindness. ;) Jerry Guido Programmer MGT of America, Inc. [EMAIL PROTECTED] The information contained in this electronic communication is intended only for the use of the addressee, and may be a confidential communication. If you are not the intended recipient, you are hereby notified that you have received this transmittal in error; any review, dissemination, distribution or copying of this transmittal is strictly prohibited. -Original Message- From: Nick Ross [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 12:08 PM To: CF-Talk Subject: Syntax error (missing operator) in query expression Syntax error (missing operator) in query expression '(getUser.UserLogin = 'paulw') AND (getUser.Password' = '2324986')'. Getting this error message on a simple login application. App starts in the application.cfm as soon as the user enters the section of site that requires login. !---If user is not logged in force them to log in--- cfif NOT isDefined(SESSION.Auth.IsLoggedIn) !---If the user is now submitting login action via login form--- !---Include Login Check code to validate the user attempting to log in.--- cfinclude template=../Templates/loginForm.cfm cfabort /cfif Here is the login page: cfif isDefined(form.UserLogin) cfinclude template=loginCheck.cfm /cfif span class=HeaderEnter Access Code/span br cfform action=../PrivateAccess/showAR.cfm Name=LoginForm method=post input type=hidden name=UserLogin_required input type=hidden name=Password_required !---userLogin Field is required--- User Name: cfinput type=text name=UserLogin size=20 value= maxlength=24 required=yes message=Please enter your user name. Thank you. style=height:14px; width:125px; font-size:12px; font-weight:bold; color: maroon; letter-spacing:0.2em; text-align:left; pnbsp;/ppnbsp;/p Password: cfinput type=password name=Password size=24 value= maxlength=24 required=yes message=Please enter your assigned password to access this part of the site. Thank you. style=height:14px; width:125px; font-size:12px; font-weight:bold; color: maroon; letter-spacing:0.2em; text-align:left cfinput type=submit name=Enter /cfform Here is the final part..the loginCheck.cfm page. !---The following code queries the database to find the record of the user's password.--- cfparam name=Form.UserLogin type=string cfparam name=Form.Password type=string cfquery name=getUser datasource=#dsn# SELECT * FROM Users WHERE (getUser.UserLogin = '#Form.UserLogin#') AND (getUser.Password' = '#Form.Password#') /cfquery !--- If the user's password is correct the following code verifies it and obtains the information then creates a distinct session for each user that logs in. --- cfif getUser.RecordCount EQ 1 cflocation url=../PrivateAccess/showAR.cfm !--- The following code remembers the user's logged-in status their user type and their first name in the session structure. --- cfset SESSION.Auth = StructNew() cfset SESSION.Auth.IsLoggedIn = yes cfset SESSION.Auth.userID = getUser.userID cfset SESSION.Auth.FirstName = getUser.FirstName !--- Now that user is logged in, the following code sends them to PrivateAccess/showAR.cfm page and menu links. --- cflocation url=#CGI.SCRIPT_NAME# /cfif I have looked over this code so much and searched the net for an explanation of why I am getting this error. Oh, and both the fields in the query are text fields; no numerical fields. Someone, PLEASE tell me what you think is going on. Thanks in advance for any help. Nick [EMAIL PROTECTED] Below is the entire error message: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(getUser.UserLogin = 'paulw') AND (getUser.Password' = '2324986')'. The error occurred in C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginCheck.cfm: line 19 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginForm.cfm: line 206 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\PrivateAccess\Application.cfm: line 21 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginCheck.cfm: line 19 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\Templates\loginForm.cfm: line 206 Called from C:\Inetpub\wwwroot\RickRossiter\NLR\PrivateAccess\Application.cfm: line 21 17 :SELECT * 18 : FROM Users 19 : WHERE (getUser.UserLogin = '#Form.UserLogin#') AND (getUser.Password' = '#Form.Password#') 20 : /cfquery 21 : SQLSELECT * FROM Users WHERE (getUser.UserLogin
Syntax error in FROM clause.
Installed CF7 and get error but do not get the error in CF6. cfquery name=GetMonths datasource=Master SELECT Month FROM Month WHERE MonthID = #GetStatRecords.MonthID# /cfquery Is Month reserved and cannont be used in CF7? ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274619 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax error in FROM clause.
According to Live Docs it is: http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=0189.htm On 4/5/07, sheldon turchin [EMAIL PROTECTED] wrote: Installed CF7 and get error but do not get the error in CF6. cfquery name=GetMonths datasource=Master SELECT Month FROM Month WHERE MonthID = #GetStatRecords.MonthID# /cfquery Is Month reserved and cannont be used in CF7? ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274622 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Syntax error in FROM clause.
in most databases that i'm aware of, month() is a built in function and as such is a reserved word. as such, it really shouldn't have worked in CFMX 6. be glad it worked for as long as it did :) if you can't change the column name, enclose it in square brackets: SELECT [Month] FROM [Month] WHERE monthID = cfqueryparam value=#getStatRecords.monthID# cfsqltype=cf_sql_integer / oh, and use cfqueryparam :) might also want to alias the column name so as not to run into any nasty SNAFUs later on in using the data: SELECT [Month] AS someWordOtherThanMonth FROM [Month] WHERE monthID = cfqueryparam value=#getStatRecords.monthID# cfsqltype=cf_sql_integer / On 4/5/07, sheldon turchin [EMAIL PROTECTED] wrote: Installed CF7 and get error but do not get the error in CF6. cfquery name=GetMonths datasource=Master SELECT Month FROM Month WHERE MonthID = #GetStatRecords.MonthID# /cfquery Is Month reserved and cannont be used in CF7? ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274623 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Syntax error in FROM clause.
sheldon turchin wrote: Installed CF7 and get error but do not get the error in CF6. cfquery name=GetMonths datasource=Master SELECT Month FROM Month WHERE MonthID = #GetStatRecords.MonthID# /cfquery Is Month reserved and cannont be used in CF7? CF would not see that as a reserved word in that context, but the DB driver you are connecting through may...try delimiting the object names... SQL Server... SELECT [Month] FROM [Month] MySQL SELECT `Month` FROM `Month` OR possibly.. SELECT Month FROM Month if quoted identifiers are allowed. Better, though, to not use common function names as database object names. ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274625 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Syntax error in FROM clause.
It worked!! Thank you so much. I have lots to learn as a newbie. in most databases that i'm aware of, month() is a built in function and as such is a reserved word. as such, it really shouldn't have worked in CFMX 6. be glad it worked for as long as it did :) if you can't change the column name, enclose it in square brackets: SELECT [Month] FROM [Month] WHERE monthID = cfqueryparam value=#getStatRecords.monthID# cfsqltype=cf_sql_integer / oh, and use cfqueryparam :) might also want to alias the column name so as not to run into any nasty SNAFUs later on in using the data: SELECT [Month] AS someWordOtherThanMonth FROM [Month] WHERE monthID = cfqueryparam value=#getStatRecords.monthID# cfsqltype=cf_sql_integer / On 4/5/07, sheldon turchin [EMAIL PROTECTED] wrote: ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274630 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Syntax error on Where statement?
Anyone know why my syntax is wrong in my Where statement please? Cold Fusion Server logged the following error information: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '='. SQL = Update maintenance_report SET maint_report_impact_search = 'NO' Where maint_report_date = '06/10/2004 ' and id = 9 This is my code: cfquery name=search datasource=wweb_status_internal_test Update maintenance_report SET maint_report_impact_search = '#form.maint_report_impact_search#' Where maint_report_date = '#form.maint_report_date#' and id = #id# /cfquery I think I may have to trim the maint_report_date field. Is that it? Thanks as always. Robert O. HWW [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Syntax error on Where statement?
instead of asking 'is that it?' (in relation to trimming the report_date value), why not simply try it? other than that i'd suggest that if your maint_report_date column is a date/time datatype, you use a createODBCDate() function around the value (and drop the single quotes).I'd futher suggest using a cfqueryparam for both of those dynamic values (maint_report_date and id). WHERE maint_report_date = cfqueryparam value=#createODBCDate(form.maint_report_date)# cfsqltype=cf_sql_date AND id = cfqueryparam value=#id# cfsqltype=cf_sql_integer - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 7:56 AM Subject: Syntax error on Where statement? Anyone know why my syntax is wrong in my Where statement please? Cold Fusion Server logged the following error information: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '='. SQL = Update maintenance_report SET maint_report_impact_search = 'NO' Where maint_report_date = '06/10/2004 ' and id = 9 This is my code: cfquery name=search datasource=wweb_status_internal_test Update maintenance_report SET maint_report_impact_search = '#form.maint_report_impact_search#' Where maint_report_date = '#form.maint_report_date#' and id = #id# /cfquery I think I may have to trim the maint_report_date field. Is that it? Thanks as always. Robert O. HWW [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Syntax error on Where statement?
Thanks Charlie. I tried the trim function and it did not help. The maint_report_date column is a char field. Should it be a date/time datatype in order for this to work properly? The code you sent still gives the syntax error. Robert O. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:19 AM To: CF-Talk Subject: Re: Syntax error on Where statement? instead of asking 'is that it?' (in relation to trimming the report_date value), why not simply try it? other than that i'd suggest that if your maint_report_date column is a date/time datatype, you use a createODBCDate() function around the value (and drop the single quotes).I'd futher suggest using a cfqueryparam for both of those dynamic values (maint_report_date and id). WHERE maint_report_date = cfqueryparam value=#createODBCDate(form.maint_report_date)# cfsqltype=cf_sql_date AND id = cfqueryparam value=#id# cfsqltype=cf_sql_integer - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 7:56 AM Subject: Syntax error on Where statement? Anyone know why my syntax is wrong in my Where statement please? Cold Fusion Server logged the following error information: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near '='. SQL = Update maintenance_report SET maint_report_impact_search = 'NO' Where maint_report_date = '06/10/2004 ' and id = 9 This is my code: cfquery name=search datasource=wweb_status_internal_test Update maintenance_report SET maint_report_impact_search = '#form.maint_report_impact_search#' Where maint_report_date = '#form.maint_report_date#' and id = #id# /cfquery I think I may have to trim the maint_report_date field. Is that it? Thanks as always. Robert O. HWW _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Syntax error on Where statement?
SQL = Update maintenance_report SET maint_report_impact_search = 'NO' Where maint_report_date = '06/10/2004 ' and id = 9 One dumb question I might ask - what is the field type for the maint_report_impact_search and id fields? As far as the date field, you'll need to trim that data unless the database itself is holding the trailing space.Conversion to a date type field would be a good suggestion. Hatton --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.667 / Virus Database: 429 - Release Date: 4/23/2004 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Syntax error on Where statement?
As Hatton said, changing maint_report_date to a date/time datatype would probably be preferrable.Doing that, the code I gave you should work (assuming that id is an integer). Otherwise, if you're going to leave it as a char field, change the cfsqltype in the first cfqueryparam to cf_sql_char. opt for the former if possible. Charlie - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 8:23 AM Subject: RE: Syntax error on Where statement? Thanks Charlie. I tried the trim function and it did not help. The maint_report_date column is a char field. Should it be a date/time datatype in order for this to work properly? The code you sent still gives the syntax error. Robert O. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Syntax error on Where statement?
All are CHAR fields except ID which is a primary key INT type. Should maint_report_date be other than CHAR? I know date/time would be good, but...I'm kinda new to this SQL biz. Robert O. -Original Message- From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:39 AM To: CF-Talk Subject: RE: Syntax error on Where statement? SQL = Update maintenance_report SET maint_report_impact_search = 'NO' Where maint_report_date = '06/10/2004 ' and id = 9 One dumb question I might ask - what is the field type for the maint_report_impact_search and id fields? As far as the date field, you'll need to trim that data unless the database itself is holding the trailing space.Conversion to a date type field would be a good suggestion. Hatton --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.667 / Virus Database: 429 - Release Date: 4/23/2004 _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Syntax error on Where statement?
Sounds good. I converted it to date/time. My dates now display as 2004-03-08. How do I make it display as 03/08/2004 in my forms? I tried #convert(datetime,convert(char(10),search.maint_report_date,101))#, but it does not recognize datetime. Robert O. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:46 AM To: CF-Talk Subject: Re: Syntax error on Where statement? As Hatton said, changing maint_report_date to a date/time datatype would probably be preferrable.Doing that, the code I gave you should work (assuming that id is an integer). Otherwise, if you're going to leave it as a char field, change the cfsqltype in the first cfqueryparam to cf_sql_char. opt for the former if possible. Charlie - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 8:23 AM Subject: RE: Syntax error on Where statement? Thanks Charlie. I tried the trim function and it did not help. The maint_report_date column is a char field. Should it be a date/time datatype in order for this to work properly? The code you sent still gives the syntax error. Robert O. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Syntax error on Where statement?
use dateFormat() on the presentation side. #dateFormat(maint_report_date, 'mm/dd/')# - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 8:51 AM Subject: RE: Syntax error on Where statement? Sounds good. I converted it to date/time. My dates now display as 2004-03-08. How do I make it display as 03/08/2004 in my forms? I tried #convert(datetime,convert(char(10),search.maint_report_date,101))#, but it does not recognize datetime. Robert O. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Syntax error on Where statement?
Everything works wellnow. Thanks all. Need to date/time any date fields in the future. Sounds elementary, but there's just too much to learn at times. Robert O. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:46 AM To: CF-Talk Subject: Re: Syntax error on Where statement? As Hatton said, changing maint_report_date to a date/time datatype would probably be preferrable.Doing that, the code I gave you should work (assuming that id is an integer). Otherwise, if you're going to leave it as a char field, change the cfsqltype in the first cfqueryparam to cf_sql_char. opt for the former if possible. Charlie - Original Message - From: Robert Orlini [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 8:23 AM Subject: RE: Syntax error on Where statement? Thanks Charlie. I tried the trim function and it did not help. The maint_report_date column is a char field. Should it be a date/time datatype in order for this to work properly? The code you sent still gives the syntax error. Robert O. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT:SQL syntax error in mySQL
Your parenthesis look unbalanced. Check them out. On 12/4/02 3:19 PM, Trace [EMAIL PROTECTED] wrote: sorry...here is the error I get: Error Diagnostic Information ODBC Error Code = 42000 (Syntax error or access violation) [MySQL][ODBC 3.51 Driver][mysqld-3.23.53-max]You have an error in your SQL syntax near 'LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.Aut' at line 5 SQL = SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHERE t.transcriptstatus = 20 The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:1) to (18:57) in the template file C:\Inetpub\wwwroot\temp\mytemplate.cfm. Anyhow, if anybody has a clue about this, it would be GREATLY appreciated. Thanks! Trace - Original Message - From: Trace [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 3:09 PM Subject: OT:SQL syntax error in mySQL Hi everybody, I have just switched a cf site from using a MSAccess database to mySQL . The following query produces and error now (did work in MSAccess): SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN (tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHEREt.status = 20 If I take out the LEFT JOIN statement and use this query instead, it works just fine: SELECTt.transID, a.authorID, g.groupID FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t ON a.AuthorID = t.AuthorID WHERE t.status = 20 I am new to mySQL. Is there a limit to the number of nested joins in a query? The error I get is: ~| 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
OT:SQL syntax error in mySQL
Hi everybody, I have just switched a cf site from using a MSAccess database to mySQL . The following query produces and error now (did work in MSAccess): SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN (tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHEREt.status = 20 If I take out the LEFT JOIN statement and use this query instead, it works just fine: SELECTt.transID, a.authorID, g.groupID FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t ON a.AuthorID = t.AuthorID WHERE t.status = 20 I am new to mySQL. Is there a limit to the number of nested joins in a query? The error I get is: ~| 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: OT:SQL syntax error in mySQL
sorry...here is the error I get: Error Diagnostic Information ODBC Error Code = 42000 (Syntax error or access violation) [MySQL][ODBC 3.51 Driver][mysqld-3.23.53-max]You have an error in your SQL syntax near 'LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.Aut' at line 5 SQL = SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHERE t.transcriptstatus = 20 The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (18:1) to (18:57) in the template file C:\Inetpub\wwwroot\temp\mytemplate.cfm. Anyhow, if anybody has a clue about this, it would be GREATLY appreciated. Thanks! Trace - Original Message - From: Trace [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, December 04, 2002 3:09 PM Subject: OT:SQL syntax error in mySQL Hi everybody, I have just switched a cf site from using a MSAccess database to mySQL . The following query produces and error now (did work in MSAccess): SELECT t.transID, a.authorID, g.groupID, ts.transName FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN (tbl_transcripts t LEFT JOIN tbl_transcriptionists ts ON t.TransID=ts.TransID) ON a.AuthorID = t.AuthorID WHEREt.status = 20 If I take out the LEFT JOIN statement and use this query instead, it works just fine: SELECTt.transID, a.authorID, g.groupID FROM (tbl_authors a INNER JOIN tbl_groups g ON a.GroupID = g.groupID) INNER JOIN tbl_transcripts t ON a.AuthorID = t.AuthorID WHERE t.status = 20 I am new to mySQL. Is there a limit to the number of nested joins in a query? The error I get is: ~| 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
syntax error when inserting date
Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: syntax error when inserting date
Date is most likely a reserved word in your database. Try changing the column name to something more descriptive like Start_Date. HTH Brian - Original Message - From: megan sherman [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 9:01 AM Subject: syntax error when inserting date Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ 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: syntax error when inserting date
Your problem is most likely due to the format of the date. If the data field in the database is of type 'Date' you would have to use CreateODBCDate(Form.Date) in order to change the text to an ODBC date format. Regards, Rob Rusher Spectra Architect, Certified ColdFusion Instructor + Developer, Certified Java Programmer email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] phone: 303-885-7044 plan, build and integrate interactive applications and e-business services to enhance knowledge sharing, e-commerce and business communications initiatives -Original Message- From: megan sherman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 8:02 AM To: CF-Talk Subject: syntax error when inserting date Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ 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: syntax error when inserting date
Remember that date is a key word in sql and will produce undesired results if a field in a table is named date as in your code snippet. insert into class_schedule (classID, location, date) Try changing the field name to aDate or lastupdated or something. Bryan - Original Message - From: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 3:06 PM Subject: RE: syntax error when inserting date Your problem is most likely due to the format of the date. If the data field in the database is of type 'Date' you would have to use CreateODBCDate(Form.Date) in order to change the text to an ODBC date format. Regards, Rob Rusher Spectra Architect, Certified ColdFusion Instructor + Developer, Certified Java Programmer email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] phone: 303-885-7044 plan, build and integrate interactive applications and e-business services to enhance knowledge sharing, e-commerce and business communications initiatives -Original Message- From: megan sherman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 8:02 AM To: CF-Talk Subject: syntax error when inserting date Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ 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: syntax error when inserting date
Actually the problem is that you have a field in your database named 'Date' this is a reserved word and cannot be used as a field name in your db. Try changing the field name to something like 'mydate' or some other equivalent and try your code again, you will likely see better results. Chris -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 3:06 PM To: CF-Talk Subject: RE: syntax error when inserting date Your problem is most likely due to the format of the date. If the data field in the database is of type 'Date' you would have to use CreateODBCDate(Form.Date) in order to change the text to an ODBC date format. Regards, Rob Rusher Spectra Architect, Certified ColdFusion Instructor + Developer, Certified Java Programmer email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] phone: 303-885-7044 plan, build and integrate interactive applications and e-business services to enhance knowledge sharing, e-commerce and business communications initiatives -Original Message- From: megan sherman [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 09, 2001 8:02 AM To: CF-Talk Subject: syntax error when inserting date Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ 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: syntax error when inserting date
What database are you using? The problem may be that your database is expecting the date in a specific format. Using Oracle as an example, the value you are trying to insert into the Date column, '#onedate#', would be seen as a string and not a date which would generate an error. Oracle has a TO_DATE function that will convert the string to a date format. Check to make sure the format is recognizable by the database you are using and also check if the date string needs to be converted before inserting. Erik - Original Message - From: megan sherman [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Wednesday, May 09, 2001 10:01 AM Subject: syntax error when inserting date Hi all, My action template is supposed to insert classID, location and date into a table in my database. When creating new records in the form I provide, I want the user to be able to supply a comma delimited list of dates. I think the loop in the action template successfully extracts individual dates from the date list, but there is a syntax error in the insert command. Everything works if I don't try to insert the date, so that must be where the syntax error occurs. Can anybody tell me what my date syntax error is? Thanks in advance! Megan The action template code is shown below CFIF IsDefined(FORM.scheduleID) CFUPDATE DATASOURCE=calkayak TABLENAME=class_schedule CFELSE cfoutput cfset dategroup=form.date cfloop index=x from=1 to=#ListLen(dategroup)# cfset onedate=ListGetAt(dategroup,#x#) cfquery datasource=calkayak insert into class_schedule (classID, location, date) values(#form.classID#, '#form.location#', '#onedate#') /cfquery /cfloop /cfoutput /CFIF Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ~~ 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: anyone see what is causing a Syntax Error is this update statement??
Is price a number datatype? If so try removing the single quotes from '#price#' Tim P. - Original Message - From: Jeff Fongemie [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Saturday, May 05, 2001 6:57 PM Subject: anyone see what is causing a Syntax Error is this update statement?? Hello cf-talk, This simple update is generating a syntax error i can't seem to resolve. What am I missing?? cfquery DATASOURCE=#Application.dsn# UPDATE #form.table# SET name='#name#', desc='#desc#', price='#price#' WHERE id=#form.id# /cfquery Best regards, Jeff Fongemie mailto:[EMAIL PROTECTED] Internet Guns For Hire (603) 356-0768 ~~ 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: anyone see what is causing a Syntax Error is this update statement??
This might be because desc is a reserved word in MS SQL. Check the docs for the platform you're on and see if this might be the problem. Regards, Stephen HTH, Stephen Hello cf-talk, This simple update is generating a syntax error i can't seem to resolve. What am I missing?? cfquery DATASOURCE=#Application.dsn# UPDATE #form.table# SET name='#name#', desc='#desc#', price='#price#' WHERE id=#form.id# /cfquery Best regards, Jeff Fongemie mailto:[EMAIL PROTECTED] Internet Guns For Hire (603) 356-0768 ~~ 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
anyone see what is causing a Syntax Error is this update statement??
Hello cf-talk, This simple update is generating a syntax error i can't seem to resolve. What am I missing?? cfquery DATASOURCE=#Application.dsn# UPDATE #form.table# SET name='#name#', desc='#desc#', price='#price#' WHERE id=#form.id# /cfquery Best regards, Jeff Fongemie mailto:[EMAIL PROTECTED] Internet Guns For Hire (603) 356-0768 ~~ 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: anyone see what is causing a Syntax Error is this update statement??
WHat is the syntax error? I would guess that one of your fields is not a TEXT field - maybe Price ? If any of those fields are numeric it will get messed up - or if your Price field is a money type and you are putting in a entry with a $ dollar sign? -Original Message- From: Jeff Fongemie [mailto:[EMAIL PROTECTED]] Sent: Saturday, May 05, 2001 3:58 PM To: CF-Talk Subject: anyone see what is causing a Syntax Error is this update statement?? Hello cf-talk, This simple update is generating a syntax error i can't seem to resolve. What am I missing?? cfquery DATASOURCE=#Application.dsn# UPDATE #form.table# SET name='#name#', desc='#desc#', price='#price#' WHERE id=#form.id# /cfquery Best regards, Jeff Fongemie mailto:[EMAIL PROTECTED] Internet Guns For Hire (603) 356-0768 ~~ 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: anyone see what is causing a Syntax Error is this update statement??
desc is reserved word. Either change the name of the field or put square braces around it. HTH, Howie Hamlin - inFusion Project Manager On-Line Data Solutions, Inc. www.CoolFusion.com 631-737-4668 x101 inFusion Mail Server (iMS) - The Intelligent Mail Server - Original Message - From: Jeff Fongemie [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Saturday, May 05, 2001 6:57 PM Subject: anyone see what is causing a Syntax Error is this update statement?? Hello cf-talk, This simple update is generating a syntax error i can't seem to resolve. What am I missing?? cfquery DATASOURCE=#Application.dsn# UPDATE #form.table# SET name='#name#', desc='#desc#', price='#price#' WHERE id=#form.id# /cfquery Best regards, Jeff Fongemie mailto:[EMAIL PROTECTED] Internet Guns For Hire (603) 356-0768 ~~ 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
Syntax error or sharing violation
can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
live = 0, should be live =0 no comma. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 6:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
I've done this so many times! The comma in the last item is the culprit: live = 0, where id = #id# /cfquery Greg Creedon cf handyman for hire! Got something that needs getting done? Call the cf handyman! [EMAIL PROTECTED] -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 9:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
no comma after live=0 - Original Message - From: Darren Adams [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Thursday, April 26, 2001 9:52 AM Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
It's what you call extending functionality to an authoring tool and copying pages then deleting fields you dont want and accidentally living commas in. That is so infuriating !! -Original Message- From: Greg Creedon [mailto:[EMAIL PROTECTED]] Sent: 26 April 2001 15:06 To: CF-Talk Subject: RE: Syntax error or sharing violation I've done this so many times! The comma in the last item is the culprit: live = 0, where id = #id# /cfquery Greg Creedon cf handyman for hire! Got something that needs getting done? Call the cf handyman! [EMAIL PROTECTED] -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 9:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
ARRRGGG Talk about not seeing the wood for the trees !! dont you justy hate it when you get silly little things like that ? -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: 26 April 2001 15:08 To: CF-Talk Subject: RE: Syntax error or sharing violation live = 0, should be live =0 no comma. -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 6:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
I don't think that you need single quotes around the Date, additionally try formatting the date using CreateODBCDate(date). Jeff Garza Web Developer/Webmaster Spectrum Astro, Inc. 480.892.8200 [EMAIL PROTECTED] http://www.spectrumastro.com -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 6:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error or sharing violation
It's the comma after your live=0 line live = 0, where id = #id# -Original Message- From: Garza, Jeff [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 8:09 AM To: CF-Talk Subject: RE: Syntax error or sharing violation I don't think that you need single quotes around the Date, additionally try formatting the date using CreateODBCDate(date). Jeff Garza Web Developer/Webmaster Spectrum Astro, Inc. 480.892.8200 [EMAIL PROTECTED] http://www.spectrumastro.com -Original Message- From: Darren Adams [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 26, 2001 6:52 AM To: CF-Talk Subject: Syntax error or sharing violation can anyone here see what the problem with this statement is ? cfquery name=news datasource=cosmos1 dbtype=ODBC UPDATE waiting_room_news set headline = '#headline#', subtitle = '#subtitle#', body = '#body#', author = '#author#', expdate = '#expdate#', cfif #audience# EQ 'Keep old value' audience = '#oldaudience#' cfelse audience = '#audience#' /cfif, datestamp = '#date#', !---Set the approval field back to 0 --- live = 0, where id = #id# /cfquery This is the error message I am getting: Error Occurred While Processing Request Error Diagnostic Information ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:56). Date/Time: 04/26/01 14:50:44 Browser: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt) Remote Address: 129.153.207.20 HTTP Referer: http://uk1web2:8080/menutest/authorise/edit_news.cfm?id=5country=World Widecontent=news Template: E:\Inetpub\publicsite\menutest\authorise\edit_news_action.cfm Darren Adams Web Developer Marketing Department Systems Union Office: 01252 55 6220 Mobile: 07714 817 038 Email: [EMAIL PROTECTED] ~~ 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: Syntax error in INSERT INTO statement
date is a reserved word and you should only have '' around text/string data.
if you are trying to put date info in your database you should use 'now()'
as the default data in access and 'getdate()' in mssql7 and not actually
insert the value or the column in your query. go to www.swynk.com and
search for related info on automatic date insertions, there is an alternate
to getdate() in mssql7 that uses a different method of getting the actual
date.
good luck,
alex
-Original Message-
From: Robert Orlini [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 09, 2001 12:05 PM
To: CF-Talk
Subject: Syntax error in INSERT INTO statement
Hello,
This gets me all the time. I created a simple survey form; checked and
double-checked all fields and I still get a "Syntax error in INSERT INTO
statement" error. All the fields correspond in the form CF page, and Access
table.
Is there some kind of bug or trick I'm missing here? Any help would be
appreciated.
Robert O.
Web Admin
CFQUERY name="getsurvey" datasource="survey"
INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email)
VALUES
('#DateFormat(Now())#','#FORM.Info#','#FORM.infosuggestions#','#FORM.Bio#',
'#FORM.Quote#','#FORM.Quotes#','#FORM.Otherquotes#','#FORM.Futurepage#',
'#FORM.Onlinecatalog#','#FORM.Catalogfunction#','#FORM.Othercatalog#',
'#FORM.Content#','#FORM.Contentsuggestions#','#FORM.Rate#','#FORM.Rateexplan
ation#','#FORM.Category#','#FORM.Lastcomments#','#FORM.Institution#','#FORM.
Name#','#FORM.Title#','#FORM.email#')
/cfquery
-
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.
SQL = "INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email) VALUES
('09-Feb-01','','','','','','','','','','','','','','','Select','','vnbvnvbn
b','','','')"
Data Source = "survey"
SQL = "INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email) VALUES
('09-Feb-01','','','','','','','','','','','','','','','Select','','vnbvnvbn
b','','','')"
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (1:1) to (1:46).
~~
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
Syntax error in INSERT INTO statement
Hello,
This gets me all the time. I created a simple survey form; checked and
double-checked all fields and I still get a "Syntax error in INSERT INTO
statement error. All the fields correspond in the form CF page, and Access
table.
Is there some kind of bug or trick Im missing here? Any help would be
appreciated.
Robert O.
Web Admin
CFQUERY name="getsurvey" datasource="survey"
INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email)
VALUES
('#DateFormat(Now())#','#FORM.Info#','#FORM.infosuggestions#','#FORM.Bio#',
'#FORM.Quote#','#FORM.Quotes#','#FORM.Otherquotes#','#FORM.Futurepage#',
'#FORM.Onlinecatalog#','#FORM.Catalogfunction#','#FORM.Othercatalog#',
'#FORM.Content#','#FORM.Contentsuggestions#','#FORM.Rate#','#FORM.Rateexplan
ation#','#FORM.Category#','#FORM.Lastcomments#','#FORM.Institution#','#FORM.
Name#','#FORM.Title#','#FORM.email#')
/cfquery
-
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.
SQL = "INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email) VALUES
('09-Feb-01','','','','','','','','','','','','','','','Select','','vnbvnvbn
b','','','')"
Data Source = "survey"
SQL = "INSERT INTO survey
(Date,info,infosuggestions,bio,quote,quotes,otherquotes,futurepage,onlinecat
alog,catalogfunction,othercatalog,content,contentsuggestions,rate,rateexplan
ation,category,lastcomments,Institution,Name,Title,Email) VALUES
('09-Feb-01','','','','','','','','','','','','','','','Select','','vnbvnvbn
b','','','')"
The error occurred while processing an element with a general identifier of
(CFQUERY), occupying document position (1:1) to (1:46).
~~
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
