CONVERT varchar to money

[slegendre]

Can someone help me with the syntax on how to convert data type varchar to data type money. I'm receiving the following error message:

[Macromedia][SQLServer JDBC Driver][SQLServer]Disallowed implicit conversion from data type varchar to data type money, table 'tableName', column 'columnName'. Use the CONVERT function to run this query.

Any help would be much appreciated! Thanks.
SLL

[cf_dev2]

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

convert(money, '10.00') ... or
cast('10.00' as money)

Just curious.. if you're treating the values as 'money' is there a reason why the column type is varchar?

[slegendre]

Well the database table stores them as money. I've tried to change my code format in CF to "currency" but it still gives me the same error. So I figured i'd have to cast or convert it to make it work. Still learning i'm afraid. I'll take any advice you got to offer =).

I'll try the convert here in a bit and post if it works, Thanks.

[cf_dev2]

What is the code and values throwing this error?

[slegendre]

Within my main page I call all the data from a particular table along with a link to edit that particular record. Clicking edit takes you to the "edit.cfm page".

The edit.cfm page displays the data from the particular record you clicked.

When you make changes (or dont make changes) you can update the record by clicking a button that takes you to a processor page. That runs the following code:



<!-- Edit or Update -->
<cfif IsDefined("form.ID")>
<!--Update-->
<cfquery datasource="db_name">
UPDATE dbo.tblName
SET Type='#form.Type#',
Price='#form.Price#',
Category='#form.Category#'
WHERE ID=#form.ID#
</cfquery>
</cfif>



The error that I receive is:
[Macromedia][SQLServer JDBC Driver][SQLServer]Disallowed implicit conversion from data type varchar to data type money, table 'tblName', column 'Price'. Use the CONVERT function to run this query.

[cf_dev2 · Accepted solution]

The error is caused by using single quotes around #form.price#. The single quotes tell sql server to treat #form.price# as a string value (varchar). While sql server can implicitly convert some values from one data type to another it doesn't automatically convert type varchar to type money.

Assuming you've already validated the #form.price# value, either get rid of the single quotes or better use cfqueryparam with the correct cfsqltype. (I think its cf_sql_decimal)

-- this works
update @tblName
set price = 10.00
where id = 1

-- this works
update @tblName
set price = cast('10.00' as money)
where id = 1

-- this doesn't
update @tblName
set price = '10.00'
where id = 1

-- using cfqueryparam
update @tblName
set price = <cfqueryparam value="10.00" cfsqltype="cf_sql_decimal">
where id = 1

[slegendre]

Taking away the single quotes did it! Thank you so much! It feels sooo good!

Problem solved.
SLL

[cf_dev2]

As an aside.. don't forget about cfqueryparam. One of its benefits is helping prevent sql injection. Statements like this can result in bigger problems than data type conversions ;-)

update @tblName
set price = #form.price#
where id = 1

[slegendre]

Gotcha! Thanks a lot again. I'm sure i'll be posting much more, so look out for me! haha!