error with column name "x-amount"

ColdFusion 9
MS SQL Server 2012

Hi. In table "ConferenceFeeType" I would like to name a column "x-amount", because that is the name that authorize.net expects me to have for this column.

The datatype of the column is: nvarchar(50)

Strange behavior: MS SQL Server 2012 wraps the column name x-amount in brackets, like this:
[x-amount]

And ColdFusion 9 reads column [x-amount] as just "x" and so reports an invalid column, thus:

 Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'x'.
 
The error occurred in C:/websites/www.nnvawi.org/admin/ManageConferenceFees.cfm: line 20

18 :   <cfquery datasource="#APPLICATION.dataSource#" name="getRecords">
19 : SELECT ConferenceFeeTypeID, ConferenceFeeTitle, x-amount
20 : FROM #request.conferenceFeeTable#
21 :   </cfquery>

Can I fix this either in MS SQL Server 2012, or in my ColdFusion code?

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

Eric BourlandAuthor Commented:
I am guessing "x-amount" is a reserved column name ... doing more reading on this. Interesting topic.
0
GaryCommented:
Any hyphenated name should be in square brackets

SELECT ConferenceFeeTypeID, ConferenceFeeTitle, [x-amount]
FROM #request.conferenceFeeTable#

Open in new window

0
Eric BourlandAuthor Commented:
Gary, thank you. This works:

SELECT ConferenceFeeTypeID, ConferenceFeeTitle, [x-amount]
FROM #request.conferenceFeeTable#

Open in new window


but I get an error when I apply a scope to variable x-amount, as so:

#getRecords.[x-amount]#

Open in new window


Error is:

 A CFML variable name cannot end with a "." character.
The variable getRecords. ends with a "." character. You must either provide an additional structure key or delete the "." character.

The CFML compiler was processing:

    The body of a cfoutput tag beginning on line 74, column 3.

 
The error occurred in C:/websites/www.nnvawi.org/admin/ManageConferenceFees.cfm: line 91

89 :    <td>#getRecords.ConferenceFeeTitle#</td>
90 :
91 :    <td>#getRecords.[x-amount]#</td>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GaryCommented:
Don't use it in variable names, only in the actual select
0
Eric BourlandAuthor Commented:
Understood. I tried this too:

 #getRecords.x-amount#

but then I get this error:

"Element X is undefined in GETRECORDS."

So it seems like ColdFusion is reading:

  #getRecords.x#
0
GaryCommented:
Use

#getRecords[x-amount]#

Hyphens in variables, databases etc are generally considered bad practise
0
Eric BourlandAuthor Commented:
Tried that too. =)

#getRecords[x-amount]#

Now I am getting error: "Variable X is undefined."


>>>Hyphens in variables, databases etc are generally considered bad practise
I agree 100%. This column used to be named, simply, "amount". I need to connect to a payment form on authorize.net, and have found that authorize.net requires columns with names "x-".

Thank you again for your help and ideas.

Eric
0
_agx_Commented:
(no points...)

> #getRecords[x-amount]#

Just don't forget the quotes around the column name. Also, a query row number ie (1,2,3,....)  is required with that syntax.

          #getRecords[ "x-amount" ][ rowNumberHere ]#

If you're looping through a query, use "currentRow" for the row number;

      <cfoutput query="getRecords">
          #getRecords[ "x-amount" ][ getRecords.currentRow ]#
      </cfoutput>

> Hyphens in variables, databases etc are generally considered bad practise

I agree.  If needed, keep the general naming structure, just remove any invalid characters, ie:

ColumnNames:
XLastName
XAmount
XWhatever
...
0
GaryCommented:
I'm not sure then..

authorize.net requires columns with names "x-".
That shouldn't matter what your table column names are.
You can set the variable name however you like when you pass the data to authorize.net.

edit
Ahhh the obvious!
0
Eric BourlandAuthor Commented:
Thanks very much to both of you. =)

I might be making this more complicated than it needs to be.

I will set the column name back to "amount" and try to work around it in my code.

>>>That shouldn't matter what your table column names are.
You can set the variable name however you like when you pass the data to authorize.net.

Normally I agree! But I have had some trouble getting authorize.net to accept other variables. This might be entirely my own fault, however.

I will work on this some more. This has been educational.

Question: the "amount" column handles only data in US dollars --- 300.00, 100.00, and so on. Can I change the datatype to "money" or "smallmoney" in MS SQL Server 2012?

Thank you again.

Eric
0
GaryCommented:
In that price range use smallmoney.
0
_agx_Commented:
I think what Gary's meant is authorize.net doesn't know or care how the values are stored on your end. The db structure is your purview. You could store "Amount" in a column named "Kalamazoo" if you wanted. As long as authorize.net receives the form field names it's expecting, it'll be happy. ie

      <form...>
          <cfoutput>
          <input name="x-amount" value="#someQuery.Kalamazoo#">
          </cfoutput>
      </form>

Yes, it's simpler when it's consistent and everything uses the same name, but ... it doesn't always make sense to do that.

(Edit) Yes, for currency, "money" is the recommended type, rather than an approximate numeric type.  If you'll ever exceed the capacity, "smallmoney" is fine too.
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
_agx_Commented:
> The datatype of the column is: nvarchar(50)

Oh! I just saw that. Never, never, never use "varchar" for storing numbers (or dates). You'll regret it.. take my word for it ;-)  For numbers, always use a numeric data type.
0
Eric BourlandAuthor Commented:
Thank you both! This clarified some things for me.

Hope your day is great.

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.