We help IT Professionals succeed at work.

problem with query

pigmentarts
pigmentarts asked
on
229 Views
Last Modified: 2013-12-24
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>
      
Comment
Watch Question

Commented:
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>
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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!

Author

Commented:
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'.

Commented:
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.

Commented:
If you can use it, the cfqueryparam is a much more robust way of passing variables to sql.

Author

Commented:
could you give me or a link to how to use cfqueryparam as the example i am finding are not too good.

Commented:
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


Author

Commented:
thank you

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.