i'm confused about so many things... 1) you're using Access, right? does Access even support a CLOB datatype? what are the datatypes in the database? 2) i don't think 'State' is a reserved word in Access (again, i'm assuming this is Access as I seem to recall somewhere in this thread that it was mentioned... please confirm). Try without the double quotes. 3) was this ever working before you put the 'location' conditional? 4) wasting way too much time shooting in the dark. whittle the statement down to a single statement using as few variables as possible. get rid of the cfqueryparam. just make it work. then add the other columns/conditionals 1 at a time. then add the cfqueryparams 1 at a time.
On Fri, May 23, 2008 at 7:06 PM, <[EMAIL PROTECTED]> wrote: > I would assume it is because it is a keyword used in SQL or Database > syntax. Again Dream Weaver wrote the code. > > > -----Original Message----- > From: Charlie Griefer [mailto:[EMAIL PROTECTED] > Sent: Friday, May 23, 2008 5:30 PM > To: CF-Talk > Subject: Re: Syntax Error In Update Statement > > why is the column State in double quotes? > > On Fri, May 23, 2008 at 2:18 PM, <[EMAIL PROTECTED]> wrote: >> It is a number field in an Access DB. Below is the entire query. >> >> <cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ >> "form1"> >> <cfquery datasource="#DSN#" name="UpdateCompany"> >> UPDATE HomeCareCompany >> SET Active=<cfif IsDefined("FORM.Active")> >> 1 >> <cfelse> >> 0 >> </cfif> >> , Location=<cfif IsDefined("FORM.Location") AND #FORM.Location# NEQ > ""> >> <cfqueryparam value="#FORM.Location#" cfsqltype="CF_SQL_NUMERIC" >> maxlength="15"> >> <cfelse> >> 0 >> </cfif> >> , CompanyName=<cfif IsDefined("FORM.CompanyName") AND > #FORM.CompanyName# >> NEQ ""> >> <cfqueryparam value="#FORM.CompanyName#" cfsqltype="cf_sql_clob" >> maxlength="85"> >> <cfelse> >> '' >> </cfif> >> , Email=<cfif IsDefined("FORM.Email") AND #FORM.Email# NEQ ""> >> <cfqueryparam value="#FORM.Email#" cfsqltype="cf_sql_clob" >> maxlength="75"> >> <cfelse> >> '' >> </cfif> >> , Password=<cfif IsDefined("FORM.Password") AND #FORM.Password# NEQ > ""> >> <cfqueryparam value="#FORM.Password#" cfsqltype="cf_sql_clob" >> maxlength="15"> >> <cfelse> >> '' >> </cfif> >> , FirstName=<cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ >> ""> >> <cfqueryparam value="#FORM.FirstName#" cfsqltype="cf_sql_clob" >> maxlength="40"> >> <cfelse> >> '' >> </cfif> >> , LastName=<cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ > ""> >> <cfqueryparam value="#FORM.LastName#" cfsqltype="cf_sql_clob" >> maxlength="40"> >> <cfelse> >> '' >> </cfif> >> , Address=<cfif IsDefined("FORM.Address") AND #FORM.Address# NEQ ""> >> <cfqueryparam value="#FORM.Address#" cfsqltype="cf_sql_clob" >> maxlength="200"> >> <cfelse> >> '' >> </cfif> >> , City=<cfif IsDefined("FORM.City") AND #FORM.City# NEQ ""> >> <cfqueryparam value="#FORM.City#" cfsqltype="cf_sql_clob" >> maxlength="85"> >> <cfelse> >> '' >> </cfif> >> , "State"=<cfif IsDefined("FORM.State") AND #FORM.State# NEQ ""> >> <cfqueryparam value="#FORM.State#" cfsqltype="cf_sql_clob" >> maxlength="2"> >> <cfelse> >> '' >> </cfif> >> , Zip=<cfif IsDefined("FORM.Zip") AND #FORM.Zip# NEQ ""> >> <cfqueryparam value="#FORM.Zip#" cfsqltype="cf_sql_clob" > maxlength="10"> >> <cfelse> >> '' >> </cfif> >> , Phone=<cfif IsDefined("FORM.Phone") AND #FORM.Phone# NEQ ""> >> <cfqueryparam value="#FORM.Phone#" cfsqltype="cf_sql_clob" >> maxlength="15"> >> <cfelse> >> '' >> </cfif> >> , Fax=<cfif IsDefined("FORM.Fax") AND #FORM.Fax# NEQ ""> >> <cfqueryparam value="#FORM.Fax#" cfsqltype="cf_sql_clob" > maxlength="15"> >> <cfelse> >> '' >> </cfif> >> WHERE ID=<cfqueryparam value="#FORM.ID#" cfsqltype="cf_sql_numeric"> >> </cfquery> >> >> >> >> -----Original Message----- >> From: Charlie Griefer [mailto:[EMAIL PROTECTED] >> Sent: Friday, May 23, 2008 5:06 PM >> To: CF-Talk >> Subject: Re: Syntax Error In Update Statement >> >> On Fri, May 23, 2008 at 1:58 PM, <[EMAIL PROTECTED]> wrote: >>> When I change it to CF_SQL_NUMERIC the error persists. Even if I >> remove >>> the entire statement >>> >>> , Location=<cfif IsDefined("FORM.Location") AND #FORM.Location# NEQ >> ""> >>> <cfqueryparam value="#FORM.Location#" cfsqltype="CF_SQL_NUMERIC" >>> maxlength="15"> >>> <cfelse> >>> 0 >>> </cfif> >>> >>> The error still persists. >> >> is it numeric or is it integer? >> >> if the error persists without that line, can you paste the query as >> you currently have it? >> >> -- >> A byte walks into a bar and orders a pint. Bartender asks him "What's >> wrong?" Byte says "Parity error." Bartender nods and says "Yeah, I >> thought you looked a bit off." >> >> >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305999 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
