Link to home
Start Free TrialLog in
Avatar of pigmentarts
pigmentartsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

problem with query

hi have a little problem with this one could any one help?



 The value "UPDATE manufacturers SET name = '" cannot be converted to a number



            <cfif FORM.image IS "">
                  <cfset sql = "UPDATE manufacturers SET name = '" + #FORM.name# + "', contactName = '" + #FORM.contactName# + "', address = '" + #HTMLCodeFormat(FORM.address)# + "', postCode = '" + #FORM.postCode# + "', tel = '" + #FORM.tel# + "', fax = '" + #FORM.fax# + "', email = '" + #FORM.email# + "', website = '" + #FORM.website# + "', description = '" + #FORM.description# + "' WHERE manId = '" + #FORM.manId#  + "'">
            

            <cfelse>                  
                  another query here
            </cfif>
            <cfquery name="updateManufacturer" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
                  #sql#
            </cfquery>
            <cfset success = TRUE>
            <cfelse>
            <cfoutput> Please Reduce you input</cfoutput>
      </cfif>
      
Avatar of 73Spyder
73Spyder

First off.  I would write the query this way .  Much easier to read and trouble shoot.   I think the error is with the phone or fax number.  What are the data types for these feilds?  and are you padding hyphens in the form.  ie..   212-555-1212   Or just a number  2125551212

<cfif FORM.image IS "">
    <cfquery name="updateManufacturer" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
    UPDATE manufacturers
    SET name = '#FORM.name#',
    contactName = '#FORM.contactName#',
    address = '#HTMLCodeFormat(FORM.address)#',
    postCode = '#FORM.postCode#',
    tel = '#FORM.tel#',
    fax = '#FORM.fax#',
    email = '#FORM.email#',
    website = '#FORM.website#',
    description = '#FORM.description#'
    WHERE manId = '#FORM.manId#'
    </cfquery>
  <cfelse>              
    <cfquery name="updateManufacturer" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
    another query here
    </cfquery>      
</cfif>

<cfset success = TRUE>
<cfelse>
<cfoutput> Please Reduce you input</cfoutput>
</cfif>
ASKER CERTIFIED SOLUTION
Avatar of 73Spyder
73Spyder

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dgrafx
not posting for points as these guys have your answer but I wanted to point out that:
since you are setting "sql" as a coldfusion var, the + sign is not going to concatenate, but tries to add.
you may have this confused with a query you've seen where you create a sql server (or whatever db) var like
sql = "UPDATE manufacturers SET name = '" + #FORM.name# + et cetera ...
coldfusion and sql server have different syntax for this type of thing
Avatar of pigmentarts

ASKER

the reason i am trying to put it in a string first is becuase the sql statment can be one or another so i dont want it set ie:

<cfif FORM.image IS "">
a sql string
<else>
a sql string
</cfif>

query with the the string here

i see what you are staying, i will try with the & and get back to you guys. thanks for your help!
ok thanks, i have this

UPDATE manufacturers SET name = '#FORM.name#', contactName = '#FORM.contactName#', address = '#HTMLCodeFormat(FORM.address)#', postCode = '#FORM.postCode#', tel = '#FORM.tel#', fax = '#FORM.fax#', email = '#FORM.email#', website = '#FORM.website#', description = '#FORM.description#' WHERE manId = '#FORM.manId#'

UPDATE manufacturers SET name = ''Vitra'', contactName = ''test'', address = ''<PRE>dsasfdsf</PRE>'', postCode = ''sdfsdafs'', tel = ''13231231321'', fax = ''32312321312'', email = ''sdfsadf@yahoo.com'', website = ''fsdfdsfs'', description = ''Vitra is one of Europe''s leading bathroom manufacturers and sells products in over 50 countries worldwide.'' WHERE manId = ''1''

i still get this error:  [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near 'Vitra'.

The issue you are now getting is dealing with quotation marks.

use this tag  #PreserveSingleQuotes(mystring)#   ie.

UPDATE manufacturers SET name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.name)#">,
contactName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.contactName)#">,
address = <cfqueryparam cfsqltype="cf_sql_varchar" value="#HTMLCodeFormat(FORM.address)#">,
postCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.postCode)#">,
tel = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.tel)#">,
fax = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.fax)#">,
email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.email)#">,
website = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.website)#">,
description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.description)#">
WHERE manId = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuotes(FORM.manId)#">

Probably a little bit of over kill here, but try this out.
If you can use it, the cfqueryparam is a much more robust way of passing variables to sql.
could you give me or a link to how to use cfqueryparam as the example i am finding are not too good.
Here are list of the possible cfsqltype 's that you can bind the values to
    * CF_SQL_BIGINT
    * CF_SQL_BIT
    * CF_SQL_CHAR
    * CF_SQL_BLOB
    * CF_SQL_CLOB
    * CF_SQL_DATE
    * CF_SQL_DECIMAL
    * CF_SQL_DOUBLE
    * CF_SQL_FLOAT
    * CF_SQL_IDSTAMP
    * CF_SQL_INTEGER
    * CF_SQL_LONGVARCHAR
    * CF_SQL_MONEY
    * CF_SQL_MONEY4
    * CF_SQL_NUMERIC
    * CF_SQL_REAL
    * CF_SQL_REFCURSOR
    * CF_SQL_SMALLINT
    * CF_SQL_TIME
    * CF_SQL_TIMESTAMP
    * CF_SQL_TINYINT
    * CF_SQL_VARCHAR


thank you