8 Replies Latest reply on Apr 12, 2007 4:13 PM by slegendre

    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
        • 1. Re: CONVERT varchar to money
          cf_dev2 Level 1
          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?

          • 2. Re: CONVERT varchar to money
            slegendre Level 1
            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.
            • 3. Re: CONVERT varchar to money
              cf_dev2 Level 1
              What is the code and values throwing this error?
              • 4. Re: CONVERT varchar to money
                slegendre Level 1
                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.
                • 5. Re: CONVERT varchar to money
                  cf_dev2 Level 1
                  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
                  • 6. Re: CONVERT varchar to money
                    slegendre Level 1
                    Taking away the single quotes did it! Thank you so much! It feels sooo good!

                    Problem solved.
                    SLL
                    • 7. Re: CONVERT varchar to money
                      cf_dev2 Level 1
                      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
                      • 8. Re: CONVERT varchar to money
                        slegendre Level 1
                        Gotcha! Thanks a lot again. I'm sure i'll be posting much more, so look out for me! haha!