Skip to main content
Gear_Head
Gear_Head
Known Participant
March 29, 2017
Answered

CFSET var Rereplace and wont insert in Database

  • March 29, 2017
  • 2 replies
  • 564 views

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

This topic has been closed for replies.
Correct answer BKBK

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.

2 replies

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
April 3, 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 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
Gear_HeadAuthor
Known Participant
April 4, 2017

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

Gear_Head
Gear_HeadAuthor
Known Participant
March 31, 2017

Really no one

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices