Solved

combine two values to get a single value to submit to authorize.net

Posted on 2014-09-08
16
128 Views
Last Modified: 2014-09-10
ColdFusion 9
MS SQL Server 2012

Greetings. I need some help with a registration form.

In brief: I need to add "getConferenceAmount.amount" and "getPreconferenceAmount.PreconferenceAmount" to obtain a combined value -- "amount" which I submit to authorize.net.

Can I use cfset to do this? For example:

     <!--- obtain a valid amount for variable "amount"; this will confirm that amount entered is correct amount --->
     <cfquery name="getConferenceAmount" datasource="#application.datasource#"> 
         SELECT amount, ConferenceFeeTitle
         FROM #REQUEST.conferenceFeeTable# 
         WHERE ConferenceFeeTypeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.ConferenceFeeTypeID#">
     </cfquery>

     <cfif getConferenceAmount.recordCount eq 0>
           The payment amount is invalid.
     </cfif>
     
     <!--- obtain a valid amount for variable "form.PreconferenceAmount"; this will obtain a value for PreconferenceAmount --->
     <cfquery name="getPreconferenceAmount" datasource="#application.datasource#"> 
         SELECT PreconferenceAmount
         FROM #request.RegisterTable#
         WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
     </cfquery>

     <!--- add getConferenceAmount.amount and getPreconferenceAmount.PreconferenceAmount to obtain combined amount --->
     <cfset amount = getConferenceAmount.amount + getPreconferenceAmount.PreconferenceAmount>

Open in new window


The public form is located here:

http://nnvawi.org/RegisterConference.cfm

"PreconferenceAmount" is a checkbox that the client wants me to add; this is a last-minute change of scope.

Basically, I need to submit one value -- "amount" -- to authorize.net, but I need to add values (Conference and PreConference) to derive "amount".

Am I on the right track? Thank you as always.

Eric
0
Comment
Question by:Eric Bourland
16 Comments
 
LVL 28

Accepted Solution

by:
becraig earned 350 total points
Comment Utility
Yes you are, I would just cast it as an integer to be safe but that works just as well
     <cfset amount = int(getConferenceAmount.amount) + int(getPreconferenceAmount.PreconferenceAmount)>

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I'll try. Be right back. =)
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Getting this error:

The value '' cannot be converted to a number.
 
The error occurred in C:/websites/www.nnvawi.org/confirmationPage.cfm: line 70

68 :
69 :      <!--- add getConferenceAmount.amount and getPreconferenceAmount.PreconferenceAmount to obtain combined amount --->
70 :      <cfset amount = int(getConferenceAmount.amount) + int(getPreconferenceAmount.PreconferenceAmount)>
71 :         
72 : <!---- begin CFTRY; catch errors ---->


It seems like ColdFusion thinks I am submitting value " ...?

I think I know why.

Variable PreconferenceAmount comes from a checkbox in the registration form ... but, in the database, column "PreconferenceAmount" has datatype "money" -- which probably does not understand input from the checkbox.

<input type="checkbox" name="PreconferenceAmount" value="100.00" class="border0" />

So I need to make this checkbox signify a value of 100.00.

I've done this before, a long time ago. =) I am working on this ... but, any suggestions?

Thank you, becraig!

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Though maybe I am thinking about this wrong.

If I have a checkbox like this:

<input type="checkbox" name="PreconferenceAmount" value="100.00" class="border0" />

And if my MS SQL Server database column "PreconferenceAmount" has datatype "money", then the checkbox should populate the column with value 100.00 when it is checked and the form is submitted -- am I correct?

E
0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 150 total points
Comment Utility
Hi Eric -
Regarding this error
The value '' cannot be converted to a number.

You just need to wrap the variable in the val() function.   If  a variable is empty, then you cannot add it.   But if you add val() around it then the empty value will be treated as zero.

So val() instead of int()

<cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)>
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
val() -- of course. That works. =) Thank you, gdemaria. That got rid of the error.

Hmm. I'm not getting the correct value for amount. If I select

Paid member (2015) - Full Conference (Before Feb 1 2015)       350.00

and also select the Preconference checkbox, the total amount should be 350.00 + 100.00 = 450.00

But the amount I see on the confirmation page is only $350.00. The Preconference amount is excluded.

Have I used the right queries to obtain the value for Preconference amount?

     <!--- obtain a valid amount for variable "amount"; this will confirm that amount entered is correct amount --->
     <cfquery name="getConferenceAmount" datasource="#application.datasource#"> 
         SELECT amount, ConferenceFeeTitle
         FROM #REQUEST.conferenceFeeTable# 
         WHERE ConferenceFeeTypeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.ConferenceFeeTypeID#">
     </cfquery>

     <cfif getConferenceAmount.recordCount eq 0>
           The payment amount is invalid.
     </cfif>
     
     <!--- obtain a valid amount for variable "form.PreconferenceAmount"; this will obtain a value for PreconferenceAmount --->
     <cfquery name="getPreconferenceAmount" datasource="#application.datasource#"> 
         SELECT PreconferenceAmount
         FROM #request.RegisterTable#
         WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
     </cfquery>

     <!--- add getConferenceAmount.amount and getPreconferenceAmount.PreconferenceAmount to obtain combined amount --->
     <cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)> 

Open in new window

0
 
LVL 28

Expert Comment

by:becraig
Comment Utility
Hi Eric I see you're getting additional help, with regard to the error I assume it is not actually populating with a value as such it could not be converted to an integer.  Val will take null values, but you should take a look to see why the form field value is not being populated  correctly.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Dear becraig,

>>>I see you're getting additional help
I value your help very much. I have tremendous gratitude for all of the help I get on Experts Exchange.

I do confirm that the form populates the data column with a value -- 100.00. I see this directly in the database.

So something is going wrong here:

<cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)>

So I am going to set up some tests to narrow down the exact cause. I am working on this now.

Thank you again for your help. Hope your evening is going well.

best from Eric
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

Expert Comment

by:becraig
Comment Utility
Can you try just using a cfoutput to see if the value is being read correctly before adding the values:

The problem with using Val is:

Converts numeric characters that occur at the beginning of a string to a number.
Returns A number. If conversion fails, returns zero.


Worst case scenario you can convert the string:
<cfset string = "1,427.75">
<cfset number = LSParseNumber(string) * 100>

Open in new window


     <!--- obtain a valid amount for variable "amount"; this will confirm that amount entered is correct amount --->
     <cfquery name="getConferenceAmount" datasource="#application.datasource#"> 
         SELECT amount, ConferenceFeeTitle
         FROM #REQUEST.conferenceFeeTable# 
         WHERE ConferenceFeeTypeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.ConferenceFeeTypeID#">
     </cfquery>

     <cfif getConferenceAmount.recordCount eq 0>
           The payment amount is invalid.
     </cfif>
     
     <!--- obtain a valid amount for variable "form.PreconferenceAmount"; this will obtain a value for PreconferenceAmount --->
     <cfquery name="getPreconferenceAmount" datasource="#application.datasource#"> 
         SELECT PreconferenceAmount
         FROM #request.RegisterTable#
         WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
     </cfquery>

<cfoutput>#getConferenceAmount.amount#</cfoutput>
<cfoutput>#getPreconferenceAmount.PreconferenceAmount#</cfoutput>
<cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)> 

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Got it. Good idea. I will check this tomorrow. I am gonna get a little sleep now and will be back at my desk early in the morning.

Thank you very much, becraig and gdemaria. This problem is making a lot more sense to me. Have a great evening.

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Good morning, becraig,

I ran this test, per your suggestion:

<p>getConferenceAmount:  <cfoutput>#getConferenceAmount.amount#</cfoutput></p>
<p>getPreconferenceAmount: <cfoutput>#getPreconferenceAmount.PreconferenceAmount#</cfoutput></p>

And, sure enough, on the confirmation page there is no value for #getPreconferenceAmount.PreconferenceAmount#.

I do confirm that the checkbox on the registration page populates column PreconferenceAmount (datatype: money) with value "100.0000".

So, I think we have isolated the problem to #getPreconferenceAmount.PreconferenceAmount# ....

which leads me to think there is a problem with the query from which we attempt to obtain a value for #getPreconferenceAmount.PreconferenceAmount#:

     <!--- obtain a valid amount for variable "form.PreconferenceAmount"; this will obtain a value for PreconferenceAmount --->
     <cfquery name="getPreconferenceAmount" datasource="#application.datasource#"> 
         SELECT PreconferenceAmount
         FROM #request.RegisterTable#
         WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
     </cfquery>
     

Open in new window


But this query looks OK to me. What do you think I am missing?

I'm going to continue to look at variable #getPreconferenceAmount.PreconferenceAmount# and how it is derived.

Thank you again for your help.

best from Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
gdemaria,

>>So val() instead of int()

>><cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)>

Yes. I think val() works and the CFSET above is constructed properly.

When I do this (per becraig's test):

<p>getConferenceAmount:  <cfoutput>#getConferenceAmount.amount#</cfoutput></p>
<p>getPreconferenceAmount: <cfoutput>#getPreconferenceAmount.PreconferenceAmount#</cfoutput></p>


     <!--- add getConferenceAmount.amount and getPreconferenceAmount.PreconferenceAmount to obtain combined amount --->
     <cfset amount = val(getConferenceAmount.amount) + val(getPreconferenceAmount.PreconferenceAmount)> 

Open in new window


... I get no value in the output for #getPreconferenceAmount.PreconferenceAmount#, so at this point, val() is not doing anything to #getPreconferenceAmount.PreconferenceAmount#. Something else is going on to give me no value for #getPreconferenceAmount.PreconferenceAmount#.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I got it to work.

Problem: The query getPreconferenceAmount to obtain a valid amount for variable "form.PreconferenceAmount" was placed before the insert query .... so there was no data, yet, for query getPreconferenceAmount to read.

Seems like it is working now. I am look through the code again for good measure. =)

Thank you both, again.

Eric

 
  <!--- obtain a valid amount for variable "form.PreconferenceAmount"; this will obtain a value for PreconferenceAmount --->
     <cfquery name="getPreconferenceAmount" datasource="#application.datasource#"> 
         SELECT PreconferenceAmount
         FROM #request.RegisterTable#
         WHERE RegisterID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.RegisterID)#">
     </cfquery>

Open in new window

0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
Working like a charm. I needed to rearrange the order of queries.

becraig and gdemaria, thank you as always for your help.

I hope you are both well. Have a great day.

Eric
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Not to open up a can of worms, but since this involves currency ...

>> the checkbox on the registration page populates column
>> PreconferenceAmount (datatype: money) with value "100.0000".

Be careful accepting amounts submitted by the user, as they can easily be changed. If you know the amount should be $100, when the box is checked, validate it before saving it your db. Make sure it has not been changed to something else like 0 ...


>><cfif getConferenceAmount.recordCount eq 0>
>> The payment amount is invalid.
>> </cfif>

Hm... is this on your action page? ie Where you calculate the total charges before redirecting to authorize.net? If so, that CFIF won't halt processing unless you include a cfabort. Otherwise, the transaction will still continue - even if the conference isn't valid. The amount will just be 0$, ie Free
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
_agx_

This is indeed the action page. I really appreciate this observation. Looks like I had better open a new question. I'll do that later today. Thank you!

Eric
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CFdocumnet font issue 5 37
Coldfusion print HTML tables 4 47
Database structure 4 118
multi page pdf 4 69
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now