CFSET var Rereplace and wont insert in Database

[Gear_Head]

Hi,

I'm kinna new to this i'm trying to create a little system for my son lawn mowing business.

i have field that have services and prices for it

services1 price1

services2 price2

When he enters the data for the price i want to prevent him from using commas and dollar sign so when he submits field i'm trying to use this to modify it on the inserting page

price_f1 is the form field so if he puts in 450,50$

   <cfset price1 = ReReplace(price_f1, "[^\d.]", "","ALL") >

<cfoutput>#price1#</cfoutput>  it comes out 45050 i want 450.50 how can i do that ?

And once that works how can i enter it into the database i have tryed to get it in using

<cfinsert datasource="mydatasource" tablename="thetablename" formfields="ID, firstname, lastname...., services1,  price1"></cfinsert>

All the data enters but not my price1 at 45050 it stays NULL any suggestion why that is and yes i do set my var before the insert.

Thank you

[Gear_Head]

Really no one

[BKBK · Accepted solution]

You could just replace the comma with a dot. Like this:

<cfset price1 = replace(price_f1, ",", ".")>

Also, you could insert selected form fields using the cfquery tag. For example,

<cfquery name="insertData" datasource="mydatasource">

    INSERT INTO thetablename

          (ID, firstname, lastname, services1, price1)

    VALUES (

          <cfqueryparam value="#form.id#" cfsqltype="cf_sql_numeric">,

          <cfqueryparam value="#form.firstname#" cfsqltype="cf_sql_varchar">,

          <cfqueryparam value="#form.lastname#" cfsqltype="cf_sql_varchar">,

          <cfqueryparam value="#form.services1#" cfsqltype="cf_sql_varchar">,

          <cfqueryparam value="#form.price1#" cfsqltype="cf_sql_numeric">

        )

</cfquery>

It is crucial, for security reasons, to use cfqueryparam when inserting form fields in the database.

[Gear_Head]

Thank you so much it worked perfectly, all i had to do is change 1 little thing posting it for future users.

<cfqueryparam value="#form.price1#" cfsqltype="cf_sql_numeric"> FOR <cfqueryparam value="#price1#" cfsqltype="cf_sql_numeric">

And thank you for the cfqueryparam for security i didnt know i'm changing all my code now..