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

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
LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

becraigCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
I'll try. Be right back. =)
0
Eric BourlandAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Eric BourlandAuthor Commented:
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
gdemariaCommented:
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
Eric BourlandAuthor Commented:
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
becraigCommented:
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
Eric BourlandAuthor Commented:
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
becraigCommented:
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
Eric BourlandAuthor Commented:
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
Eric BourlandAuthor Commented:
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
Eric BourlandAuthor Commented:
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
Eric BourlandAuthor Commented:
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
Eric BourlandAuthor Commented:
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
_agx_Commented:
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
Eric BourlandAuthor Commented:
_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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.