CFSET var Rereplace and wont insert in Database

New Here ,
Mar 29, 2017 Mar 29, 2017

Copy link to clipboard

Copied

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

TOPICS
Database access

Views

347

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , Apr 03, 2017 Apr 03, 2017
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 valu...

Likes

Translate

Translate
New Here ,
Mar 31, 2017 Mar 31, 2017

Copy link to clipboard

Copied

Really no one

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Apr 03, 2017 Apr 03, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 04, 2017 Apr 04, 2017

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines