8 Replies Latest reply on Jul 7, 2006 12:44 PM by PackinDaMAC

    The value "115,223.39" cannot be converted to a number

    PackinDaMAC Level 1
      I have this value "115,223.39" in my database when it tries to pull this data i get this message.

      The value "115,223.39" cannot be converted to a number....Any help on this


        • 1. Re: The value "115,223.39" cannot be converted to a number
          Level 7
          It's not a number, it is a string, it has a comma in it.

          You will need to do something to remove the comma if you want it to be a
          number. A large hammer way to do this is with replace.

          #replace('115,223.60',',','','ALL')#

          Boy that is a bit confusing isn't it. Make sure you get all the quotes
          and commas matched up correctly.

          PackinDaMAC wrote:
          > I have this value "115,223.39" in my database when it tries to pull this data i
          > get this message.
          >
          > The value "115,223.39" cannot be converted to a number....Any help on this
          >
          >
          >
          >
          > <cfset res.endingar = Replace(res.endingar, ",", "", "all")>
          >
          > <cfchart format="jpg" chartheight="400" chartwidth="800">
          > <cfchartseries type="line" colorlist="008800,00cc00, 000088, 0000cc">
          >
          > <cfloop query="res">
          > <cfchartdata item="#res.themonth#" value="#Int(res.endingar)#">
          > </cfloop>
          >
          > </cfchartseries>
          >
          > </cfchart>
          > </cfoutput>
          >
          • 2. Re: The value &quot;115,223.39&quot; cannot be converted to a number
            Level 7
            Ok, actually looking at your code you are replacing comma's. You may
            need to add a val() function around that to force the conversion from
            string to number.


            Ian Skinner wrote:
            > It's not a number, it is a string, it has a comma in it.
            >
            > You will need to do something to remove the comma if you want it to be a
            > number. A large hammer way to do this is with replace.
            >
            > #replace('115,223.60',',','','ALL')#
            >
            > Boy that is a bit confusing isn't it. Make sure you get all the quotes
            > and commas matched up correctly.
            >
            > PackinDaMAC wrote:
            >> I have this value "115,223.39" in my database when it tries to pull
            >> this data i get this message.
            >>
            >> The value "115,223.39" cannot be converted to a number....Any help on
            >> this
            >>
            >>
            >>
            >>
            >> <cfset res.endingar = Replace(res.endingar, ",", "", "all")>
            >>
            >> <cfchart format="jpg" chartheight="400" chartwidth="800">
            >> <cfchartseries type="line" colorlist="008800,00cc00, 000088,
            >> 0000cc">
            >>
            >> <cfloop query="res">
            >> <cfchartdata item="#res.themonth#" value="#Int(res.endingar)#">
            >> </cfloop>
            >>
            >> </cfchartseries>
            >>
            >> </cfchart>
            >> </cfoutput>
            >>
            • 4. Re: The value &quot;115,223.39&quot; cannot be converted to a number
              PackinDaMAC Level 1
              <cfset res.charges = Val(Round(Replace(res.charges, ",", "", "all")))>

              I still get the same error...that number is just a value it pulls from the sql server.....im lost
              • 5. Re: The value &quot;115,223.39&quot; cannot be converted to a   number
                Level 7
                Try putting the val before the round, since the round function would
                also be expecting a number.

                Are there any null values in the records returned by the query. These
                can cause this kind of problem since an "" [empty string] is not a
                number. Putting a val() around that will return a zero '0' for
                null|empty string values in the recordset.

                PackinDaMAC wrote:
                > <cfset res.charges = Val(Round(Replace(res.charges, ",", "", "all")))>
                >
                > I still get the same error...that number is just a value it pulls from the sql server.....im lost
                • 6. Re: The value &quot;115,223.39&quot; cannot be converted to a number
                  MikerRoo Level 1
                  "res" is a query. So that approach will not work unless the query contains exactly one row.

                  The BEST thing to do is to fix the data in your original query, like so:
                  <CFQUERY name="res" ... ...>
                  SELECT
                  themonth,
                  Replace (endingar, ",", "") AS endingar
                  ... ...
                  </CFQUERY>

                  The above syntax works in many RDBMS. Tell us which RDMBS you are using if it doesn't work for you.
                  • 7. Re: The value &quot;115,223.39&quot; cannot be converted to a   number
                    Level 7
                    Actually since it is in a query loop it will work just fine to output
                    all the records in the set.

                    Test code that can be run if you have the CF sample databases installed.

                    <cfquery datasource="cfartgallery" name="test">
                    SELECT ARTISTID
                    FROM ARTISTS
                    </cfquery>

                    <cfoutput>
                    <cfloop query="test">
                    #test.artistid#<br/>
                    </cfloop>
                    </cfoutput>

                    MikerRoo wrote:
                    > "res" is a query. So that approach will not work unless the query contains
                    > exactly one row.
                    >
                    > The BEST thing to do is to fix the data in your original query, like so:
                    > <CFQUERY name="res" ... ...>
                    > SELECT
                    > themonth,
                    > Replace (endingar, ",", "") AS endingar
                    > ... ...
                    > </CFQUERY>
                    >

                    > The above syntax works in many RDBMS. Tell us which RDMBS you are using if
                    > it doesn't work for you.
                    >
                    >
                    • 8. Re: The value &quot;115,223.39&quot; cannot be converted to a number
                      PackinDaMAC Level 1
                      Thanks 4 the help guys but all i needed to do was add that replace statement inside my loop...It was not being converted beacause it was outside the loop...dang.....thanks 4 the help...thats the correct code snippet.....