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
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 = value="#form.postalCode#"> > ) AS deliveryCost > FROM products p > WHERE p.productid IN ( value="#form.productIDList#" list="Yes"> ) > > > #product_name# [#weight# lbs]: $#deliveryCost# to ship > > > 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? > > > > > > > > > > > > > au_costpk)+otherqryname.au_basic> > > > > > > > > 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
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 = ) AS deliveryCost FROM products p WHERE p.productid IN ( ) #product_name# [#weight# lbs]: $#deliveryCost# to ship 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? > > > > > > au_costpk)+otherqryname.au_basic> > > > > 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
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? 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
Code Check...
Here's my code. 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"; 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... 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
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
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: > > update con_list set note = 'dr' where userid = #session.userid# and skill = > #form.skill# > > 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 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: > > update con_list set note = 'dr' where userid = #session.userid# and skill = > #form.skill# > > 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 ? 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... 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 ?
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 ?
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 ?
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: > > update con_list set note = 'dr' where userid = #session.userid# and skill = > #form.skill# > > 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 ?
Maybe that's why he's now out of a job 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 ? FIXED
> Is note a reserved word in Access? Try [note] = 'dr'. ** Genius ** You win... 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 ?
> 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--- select * from con_list where userid = #session.userid# and skill=#form.skill# insert into con_list values (#session.userid#,#form.skill#,#form.level#,'#form.notes#') update con_list set skill_level=#form.level# where userid = #session.userid# and skill=#form.skill# update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# Updated OK. ---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
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 ?
> 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 ?
> 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 ?
> 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 ?
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 ?
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: update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# 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 ?
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: > > update con_list set note = 'dr' where userid = #session.userid# and skill = > #form.skill# > > 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'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 ?
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 ?
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 ?
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: update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# 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 ?
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 ?
> 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 ?
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: update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# 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 ?
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 ?
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 ?
> 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 ?
> 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 -- 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: update con_list set note = 'dr' where userid = #session.userid# and skill = '#form.skill#' 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 variables then? Like so: update con_list set note = 'dr' where userid = #session.userid# and skill = '#form.skill#' 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 ?
> 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 ?
On 5/1/01, Thomas Chiverton penned: >I have: > >update con_list set note = 'dr' where userid = #session.userid# and skill = >#form.skill# > >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
Update in CFquery is failing, somone code check this ?
I have: update con_list set note = 'dr' where userid = #session.userid# and skill = #form.skill# 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
