pigmentarts
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.addre ss)# + "', 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>
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.addre
<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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!
<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!
ASKER
ok thanks, i have this
UPDATE manufacturers SET name = '#FORM.name#', contactName = '#FORM.contactName#', address = '#HTMLCodeFormat(FORM.addr ess)#', 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'.
UPDATE manufacturers SET name = '#FORM.name#', contactName = '#FORM.contactName#', address = '#HTMLCodeFormat(FORM.addr
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(myst ring)# ie.
UPDATE manufacturers SET name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.na me)#">,
contactName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.co ntactName) #">,
address = <cfqueryparam cfsqltype="cf_sql_varchar" value="#HTMLCodeFormat(FOR M.address) #">,
postCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.po stCode)#"> ,
tel = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.te l)#">,
fax = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.fa x)#">,
email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.em ail)#">,
website = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.we bsite)#">,
description = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.de scription) #">
WHERE manId = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PreserveSingleQuot es(FORM.ma nId)#">
Probably a little bit of over kill here, but try this out.
use this tag #PreserveSingleQuotes(myst
UPDATE manufacturers SET name = <cfqueryparam cfsqltype="cf_sql_varchar"
contactName = <cfqueryparam cfsqltype="cf_sql_varchar"
address = <cfqueryparam cfsqltype="cf_sql_varchar"
postCode = <cfqueryparam cfsqltype="cf_sql_varchar"
tel = <cfqueryparam cfsqltype="cf_sql_varchar"
fax = <cfqueryparam cfsqltype="cf_sql_varchar"
email = <cfqueryparam cfsqltype="cf_sql_varchar"
website = <cfqueryparam cfsqltype="cf_sql_varchar"
description = <cfqueryparam cfsqltype="cf_sql_varchar"
WHERE manId = <cfqueryparam cfsqltype="cf_sql_varchar"
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.
ASKER
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
* 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
ASKER
thank you
<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.addr
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>