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

[PackinDaMAC]

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

[Newsgroup_User]

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

[Newsgroup_User]

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

[Guest]

Ignore.

[PackinDaMAC]

<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

[Newsgroup_User]

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

[MikerRoo]

"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.

[PackinDaMAC · Accepted solution]

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

[Newsgroup_User]

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