Re: Post Code Check
Glad that worked for you. It's easy to overuse sub-queries (there can be performance issues), but for something like this, where two bits of data are mostly unrelated, but the sample size is always small, they're great. ~| 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:314803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Post Code Check
I am trying to create a postal code and delivery cost app for my company, I have set the tables up as tbl name au_zones: au_zone_id, au_zone(postal zone), au_basic(min cost), au_costpk(cost per kilo) tbl name postCodes: post_code_id, postcode(postal code), pc_zoneid(link to au-zones) tbl name products: productid, product_name, weight. The calculation will come from postcode to zone au_basic+(au_costpk*product_weight) The user will use a form with a text box for post code and a select box for products (multiples should be allowed). When the user fills out and submits the form my products query will have 1 or multiple records, so i relise i will have to loop throught the query to get the individual product weights. This is where my knowledge needs enlightening, how do i loop throught the query and and reference the weight against the au_costpk field from for every product found, and then display it to the screen. Would i be on the right track using below? cfloop query=qryName cfset qryName.pdname = (qryname.weight*otherqryname.au_costpk)+otherqryname.au_basic /cfloop Any ideas? 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:314774 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Post Code Check
Do it all in one query: SELECT p.product_name, p.weight, ( SELECT (z.au_basic + (z.au_costpk * p.weight)) FROM au_zones z INNER JOIN postCodes c ON z.au_zone_id = c.pc_zone_id WHERE c.postcode = cfqueryparam= cfsqltype=CF_SQL_VARCHAR value=#form.postalCode# ) AS deliveryCost FROM products p WHERE p.productid IN ( cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#form.productIDList# list=Yes ) cfoutput query=queryName #product_name# [#weight# lbs]: $#deliveryCost# to ship /cfoutput That will give you a line for every selected product, with the name, weight, and deliveryCost all computed in one query. I am trying to create a postal code and delivery cost app for my company, I have set the tables up as tbl name au_zones: au_zone_id, au_zone(postal zone), au_basic(min cost), au_costpk(cost per kilo) tbl name postCodes: post_code_id, postcode(postal code), pc_zoneid(link to au-zones) tbl name products: productid, product_name, weight. The calculation will come from postcode to zone au_basic+(au_costpk*product_weight) The user will use a form with a text box for post code and a select box for products (multiples should be allowed). When the user fills out and submits the form my products query will have 1 or multiple records, so i relise i will have to loop throught the query to get the individual product weights. This is where my knowledge needs enlightening, how do i loop throught the query and and reference the weight against the au_costpk field from for every product found, and then display it to the screen. Would i be on the right track using below? cfloop query=qryName cfset qryName.pdname = (qryname.weight*otherqryname. au_costpk)+otherqryname.au_basic /cfloop Any ideas? 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:314783 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Post Code Check
excellent, thank you very much. works a treat. Do it all in one query: SELECT p.product_name, p.weight, ( SELECT (z.au_basic + (z.au_costpk * p.weight)) FROM au_zones z INNER JOIN postCodes c ON z.au_zone_id = c.pc_zone_id WHERE c.postcode = cfqueryparam= cfsqltype=CF_SQL_VARCHAR value=#form.postalCode# ) AS deliveryCost FROM products p WHERE p.productid IN ( cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#form.productIDList# list=Yes ) cfoutput query=queryName #product_name# [#weight# lbs]: $#deliveryCost# to ship /cfoutput That will give you a line for every selected product, with the name, weight, and deliveryCost all computed in one query. I am trying to create a postal code and delivery cost app for my company, I have set the tables up as tbl name au_zones: au_zone_id, au_zone(postal zone), au_basic(min cost), au_costpk(cost per kilo) tbl name postCodes: post_code_id, postcode(postal code), pc_zoneid(link to au-zones) tbl name products: productid, product_name, weight. The calculation will come from postcode to zone au_basic+(au_costpk*product_weight) The user will use a form with a text box for post code and a select box for products (multiples should be allowed). When the user fills out and submits the form my products query will have 1 or multiple records, so i relise i will have to loop throught the query to get the individual product weights. This is where my knowledge needs enlightening, how do i loop throught the query and and reference the weight against the au_costpk field from for every product found, and then display it to the screen. Would i be on the right track using below? cfloop query=qryName cfset qryName.pdname = (qryname.weight*otherqryname. au_costpk)+otherqryname.au_basic /cfloop Any ideas? 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:314789 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Code Check...
Here's my code. cfscript if (HTTP_USER_AGENT CONTAINS Mozilla AND HTTP_USER_AGENT DOES NOT CONTAIN Gecko AND HTTP_USER_AGENT DOES NOT CONTAIN MSIE) BROWSERTYPE=NS; else BROWSERTYPE=MSIE; /cfscript I am using this to detect the browser type. I am only worried about NS or MSIE. This basically says that if it's NS6 treat as NS, otherwise if it's MSIE, NS6, or anything else, treat as MSIE. I'm only using it to determine style elements. I've been using CF_Browser in the tag gallery but it is too bloated for my needs. Are there any holes here or obvious blunders that I haven't considered? Thanks, Steve ~~ 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
FW: Update in CFquery is failing, somone code check this ? FIXED
Is note a reserved word in Access? Try [note] = 'dr'. ** Genius ** You win... searches franticly around desk a poster of the current Dell server line. Mail me your address of list :-) Thanks to all the others, hope I didnt annoy you too much. [repost] ~~ 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
Update in CFquery is failing, somone code check this ?
I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
On 5/1/01, Thomas Chiverton penned: I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Off the top of my head it looks like note may be a number field. In which case you will get that error if you try to insert anything but a number. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ 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: Update in CFquery is failing, somone code check this ?
Off the top of my head it looks like note may be a number field. In which case you will get that error if you try to insert anything but a number. Nope, its an Acess ODBC datasource, and the field is marked as text, length 50. Next :-) Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
If the field is marked as text, shouldn't there be ticks around the variables then? Like so: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = '#form.skill#' /cfquery Is uderid a text field or is skill a text field? Erika (with a *K*) Whatever you can do, or dream you can, begin it. Boldness has genius, power, and magic in it. - Goethe -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:08 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Off the top of my head it looks like note may be a number field. In which case you will get that error if you try to insert anything but a number. Nope, its an Acess ODBC datasource, and the field is marked as text, length 50. Next :-) Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
Get the value of the vars and run it directly in the db so you get a meaningfull error message -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Erika L Walker [mailto:[EMAIL PROTECTED]] Sent: 01 May 2001 13:26 To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? If the field is marked as text, shouldn't there be ticks around the variables then? Like so: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = '#form.skill#' /cfquery Is uderid a text field or is skill a text field? Erika (with a *K*) Whatever you can do, or dream you can, begin it. Boldness has genius, power, and magic in it. - Goethe -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:08 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Off the top of my head it looks like note may be a number field. In which case you will get that error if you try to insert anything but a number. Nope, its an Acess ODBC datasource, and the field is marked as text, length 50. Next :-) Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
If the field is marked as text, shouldn't there be ticks around the Only 'note' is a text field, all the others are interger fields, and work fine (see bit in original post about the previous query). ~~ 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: Update in CFquery is failing, somone code check this ?
Get the value of the vars and run it directly in the db so you get a meaningfull error message How do you do that for Access ? ~~ 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: Update in CFquery is failing, somone code check this ?
Create a query and use the SQL view -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: 01 May 2001 13:46 To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Get the value of the vars and run it directly in the db so you get a meaningfull error message How do you do that for Access ? ~~ 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: Update in CFquery is failing, somone code check this ?
access has its own query tool. you can even do it within CF studio. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 5:46 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Get the value of the vars and run it directly in the db so you get a meaningfull error message How do you do that for Access ? ~~ 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: Update in CFquery is failing, somone code check this ?
have you tried outputting those variables to make sure they're what you expect (ie - not null)? -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 3:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
woah...touchy.that'll teach you to offer help Dylan! Can you tell us exactly what datatypes all three fields are in the db. What happened when you ran the query directly in Access? -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: 01 May 2001 15:50 To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, 'The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine.' Technically the critera you want to update doesn't exist any more there for no record therefore an Error. QED Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
Well, your post is not particularly clear. Have you tried running that exact query inside Microsoft Access? Assuming that note is a text field (character) and that userid is numeric and skill is numeric, this query should work just fine. However, if it doesn't work in Access directly it won't work in ColdFusion via CFQUERY. DC - Original Message - From: Thomas Chiverton [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, May 01, 2001 10:49 Subject: RE: Update in CFquery is failing, somone code check this ? have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
no need to get testy :) it's perplexing. i checked the reserved word list for access (i thought NOTE might be) but nothing is in the reserved word list that you're using. what about the code immediately before or after the CFQUERY? anything there that looks suspicious? if you could post more of the code (not just the query, but what's around it) from that file, maybe that will offer some clue. or if you want to send me the code off-list i'd be happy to look at it. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 7:50 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
I've also been caught on more than one occasion passing a userid as a number when it is, in fact, a string in the database. Just a thought. Scott Scott Brader Prairie Software Development LLC 101 East Sadd Street PO Box 235 North Prairie, WI 53153-0235 Phone: 262.392.9173 Fax: 262.392.9174 Toll Free: 888.821.3427 Mobile: 262.490.1376 http://www.prairiesoftdev.com Amateurs practice until they get it right, Experts practice until they can't get it wrong. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 9:50 To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
Is note a reserved word in Access? Try [note] = 'dr'. Craig -Original Message- From: Jason Lees (National Express) [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:01 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, 'The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine.' Technically the critera you want to update doesn't exist any more there for no record therefore an Error. QED Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
that may be, but i don't think that would throw a SYNTAX error. it just wouldn't update the field. -Original Message- From: Jason Lees (National Express) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:01 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, 'The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine.' Technically the critera you want to update doesn't exist any more there for no record therefore an Error. QED Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
heh no doubt. :) -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:00 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? woah...touchy.that'll teach you to offer help Dylan! Can you tell us exactly what datatypes all three fields are in the db. What happened when you ran the query directly in Access? -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 488 9131 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: 01 May 2001 15:50 To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? have you tried outputting those variables to make sure they're what you expect (ie - not null)? Have you tried reading my post ? [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS ~~ 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: Update in CFquery is failing, somone code check this ?
Create a query and use the SQL view The paperclip rather helpfuly says 'Syntax error in update statement' when I try and save update con_list set note = 'dr' where userid = 32715281 and skill =1; ~~ 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: Update in CFquery is failing, somone code check this ?
Can you tell us exactly what datatypes all three fields are in the db. note is 'Text', size 50. userid, skill and skill_level are 'Number', size 'long integer'. userid and skill form the primary key. ~~ 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: Update in CFquery is failing, somone code check this ?
I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, Technically the critera you want to update doesn't exist any more there for no record therefore an Error. Shame that the error is 'syntax error', and not 'no records updated', which iirc wont be reported 'cuase its OK for an update to not update anything. Anyway, its still a bug, now fixed, so thanks for spotting it :-) Dont suppose you've secretly found out where the syntax error is ? ~~ 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
OT: Update in CFquery is failing, somone code check this ?
Well, your post is not particularly clear. Have you tried Sorry, sorry :-/ Been banging this all day, and I've tried the obvious things 8 times already :-) ~~ 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: Update in CFquery is failing, somone code check this ?
it's perplexing. i checked the reserved word list for access (i thought NOTE might be) but nothing is in the reserved word list that you're using. :nods Thought of that too :-/ what about the code immediately before or after the CFQUERY? anything there that looks suspicious? Snipit here - you can see it picks up that were updating on line 1, and looks to see if we should update or insert, the problem query is marked '[here]'. The database has two lines for userid = #session.userid# (i.e. 32715281), neither with a note: userid skill skill_level note 327152811 2 327152812 2 ---snip top of file, just GUI stuff--- cfif IsDefined(form.level) cfquery name=update_ch datasource=ps_skills select * from con_list where userid = #session.userid# and skill=#form.skill# /cfquery cfif update_ch.recordcount eq 0 cfquery name=update datasource=ps_skills insert into con_list values (#session.userid#,#form.skill#,#form.level#,'#form.notes#') /cfquery cfelse cfquery name=update1 datasource=ps_skills update con_list set skill_level=#form.level# where userid = #session.userid# and skill=#form.skill# /cfquery cfquery name=update2 datasource=ps_skills!--- [here] --- update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery /cfif Updated OK. cflocation url=index.cfm /cfif ---never gets this far--- ~~ 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: Update in CFquery is failing, somone code check this ? FIXED
Is note a reserved word in Access? Try [note] = 'dr'. ** Genius ** You win... searches franticly around desk a poster of the current Dell server line. Mail me your address of list :-) Thanks to all the others, hope I didnt annoy you too much. ~~ 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: Update in CFquery is failing, somone code check this ?
Maybe that's why he's now out of a job g Seriously, though - have you tried putting square brackets around the word note? Regards, 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: Thomas Chiverton [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, May 01, 2001 11:16 AM Subject: RE: Update in CFquery is failing, somone code check this ? Create a query and use the SQL view The paperclip rather helpfuly says 'Syntax error in update statement' when I try and save update con_list set note = 'dr' where userid = 32715281 and skill =1; ~~ 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: Update in CFquery is failing, somone code check this ?
i already checked. according to MS, it's not. here's a list of what are reserved: http://support.microsoft.com/support/kb/articles/Q109/3/12.asp -Original Message- From: Howarth, Craig (IBK-NY) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:10 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Is note a reserved word in Access? Try [note] = 'dr'. Craig -Original Message- From: Jason Lees (National Express) [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:01 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, 'The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine.' Technically the critera you want to update doesn't exist any more there for no record therefore an Error. QED Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
Cant see anything wrong with the syntax, have you tried removing the spaces before and after all the ='s in the query string,shouldn't make a difference but worth trying. is the DSN correct? Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 4:22 PM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, Technically the critera you want to update doesn't exist any more there for no record therefore an Error. Shame that the error is 'syntax error', and not 'no records updated', which iirc wont be reported 'cuase its OK for an update to not update anything. Anyway, its still a bug, now fixed, so thanks for spotting it :-) Dont suppose you've secretly found out where the syntax error is ? ~~ 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: Update in CFquery is failing, somone code check this ?
you have skill and skill_level. my guess is *you* mixed them up. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:22 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, Technically the critera you want to update doesn't exist any more there for no record therefore an Error. Shame that the error is 'syntax error', and not 'no records updated', which iirc wont be reported 'cuase its OK for an update to not update anything. Anyway, its still a bug, now fixed, so thanks for spotting it :-) Dont suppose you've secretly found out where the syntax error is ? ~~ 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: Update in CFquery is failing, somone code check this ? FIXED
someone better tell MS to update their list of reserved words for access. ;) it wasn't in the list. maybe that's new to access2000 (if that's what you're using). -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:46 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? FIXED Is note a reserved word in Access? Try [note] = 'dr'. ** Genius ** You win... searches franticly around desk a poster of the current Dell server line. Mail me your address of list :-) Thanks to all the others, hope I didnt annoy you too much. ~~ 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: Update in CFquery is failing, somone code check this ?
I am not sure if it is the way the mail wrapped the text, but is there a carriage return after skill = ? If so, then the query may be trying to pass a carriage return with the #form.skill# (I just ran into this a couple of days ago) Tim P. - Original Message - From: Thomas Chiverton [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, May 01, 2001 6:43 AM Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
i sent this email at 8:56am this morning and got it at 9:55am from the list. zoiks! -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:56 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? i already checked. according to MS, it's not. here's a list of what are reserved: http://support.microsoft.com/support/kb/articles/Q109/3/12.asp -Original Message- From: Howarth, Craig (IBK-NY) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:10 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? Is note a reserved word in Access? Try [note] = 'dr'. Craig -Original Message- From: Jason Lees (National Express) [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:01 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, 'The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine.' Technically the critera you want to update doesn't exist any more there for no record therefore an Error. QED Jason Lees National Express Systems Department. E-Mail : [EMAIL PROTECTED] -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 11:43 AM To: CF-Talk Subject: Update in CFquery is failing, somone code check this ? I have: cfquery name=update2 datasource=ps_skills update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# /cfquery And it is dieing with: ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. SQL = update con_list set note = 'dr' where userid = 32715281 and skill = 1 Data Source = PS_SKILLS and I cant sport the error. The previous query (update con_list set skill_level=2 where userid = 32715281 and skill=1) on the same datasource finishes fine. Regards, Thomas Chiverton Intranet Architect and Desktop Analyst 01565 757 909 It's not a job, it's an adventure ~~ 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: Update in CFquery is failing, somone code check this ?
my guess - now that we all know the answer - is i'm wrong :). darn list speed :). -Original Message- From: Dylan Bromby [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 9:04 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? you have skill and skill_level. my guess is *you* mixed them up. -Original Message- From: Thomas Chiverton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 01, 2001 8:22 AM To: CF-Talk Subject: RE: Update in CFquery is failing, somone code check this ? I think you might find that the Skill field is now 2 and not 1 Re-read your e-mail, Technically the critera you want to update doesn't exist any more there for no record therefore an Error. Shame that the error is 'syntax error', and not 'no records updated', which iirc wont be reported 'cuase its OK for an update to not update anything. Anyway, its still a bug, now fixed, so thanks for spotting it :-) Dont suppose you've secretly found out where the syntax error is ? ~~ 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
