ColdFusion: How to insert numbers( having a 'comma'(,)/ currently iserted as 0) from form fields into the database.

Question

A form field value like 45,234 is to be inserted into the DB as 45234. When I try to do insert for 45,234, all it gets into the DB is 0.Note:- I am able to insert 45.234as 45.234 in the SQL DB.

The form field is named costOfShares, which is to be inserted into the table ShareVales (SQL Server 2005). I am using CF8.

SQL

table:-ShareVales; field:-costOfShares; DataType:-float

ColdFusion (form page)

<li>
  <cfinput size="8" maxlength="20" name="costOfShares" 
    id="costOfShares" value="#share.GetcostOfShares()#">
</li>

Share.cfc:-

<cfcomponent> 
    <cfscript> Variables.CostOfShare </cfscript> 
<cffunction name="GetCostOfShare" returntype="numeric" access="public" output="false">         
   <cfscript> variables.CostOfShare; </cfscript>
</cffunction>

<cffunction name="SetCostOfShare" retuntype="void" access="public" output="false">    
   <cfargument name="costOfShare" type="string"  required="true">
      <cfscript> variables. costOfShare = arguments. costOfShare; </cfscript>
  </cffunction>
</cfcomponent> 

ColdFusion (query page)

  <cffunction>
    <cfargumnet>
    <cfquery …>
      INSERT INTO ShareVales(shareUnitId, costOfShares) 
      VALUES (
        <cfqueryparam cfsqltype="cf_sql_integer"
                      value="#arguments.share.GetshareUnitId()#">,
        <cfqueryparam cfsqltype="cf_sql_float"
                      value="#arguments.share.GetcostOfShares()#">);
    </cfquery>
</cffunction>

When I use the following directly in the Query Editor:

INSERT into share(setCostOfShare)
values(Cast(replace('5,322,444',',','') as float))

it succeeds. The error is while using cfquery/cfqueryparam in the ColdFusion template.

So, how do insert the 45234 when the form field costOfShares contains a value with commas?

Answer 1

LSParseNumber("45,234", "French (standard)")

Or if "French (standard)" is already your system standard system default locale, just use:

LSParseNumber("45,234")

Or...

<cfset setLocale("French (standard)")>
<cfset value = LSParseNumber("45,234")>

They'll return 45.234 (if you want 45234 instead, use US locale, see doc!)

I'd suggest you to store costOfShares as a valid ColdFusion numeric value (aka, use '.' instead of ','), and only use LSNumberFormat() or LSLSCurrencyFormat() to display the value on the view. That way you don't even have to LSParse the number, unless the number is created by the user, and he uses ','.

Answer 2

presumably the datatype is a float or something. comma's are not valid characters in a float (unless for European locales). You probably want instead to get the value out of the DB with a comma (but not necessarily store it with one). That would probably mean using the COVERT function during a SELECT:

http://technet.microsoft.com/en-us/library/ms174450.aspx

Answer 3

If they type a comma, strip it out first before trying to insert.

Answer 4

Not sure if this will help or not, but just put the replace inside of cfqueryparam. That way it hands SQL Server the "already cleaned up version".

CAST(<cfqueryparam cfsqltype="cf_sql_float"                                    
                   value="#REPLACE(arguments.share.GetcostOfShares()',',','')#"> AS FLOAT)

Or, have CF run a replace on the variable and clean it up before you even try the query.

Or, have CF run a replace on the variable and clean it up before you even pass it in as an argument.