asked on

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

Eric Bourland

ASKER

I am guessing "x-amount" is a reserved column name ... doing more reading on this. Interesting topic.

SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

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>

Gary

Don't use it in variable names, only in the actual select

Eric Bourland

ASKER

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#

SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

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

SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eric Bourland

ASKER

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

Gary

In that price range use smallmoney.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

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

Eric Bourland

ASKER

Thank you both! This clarified some things for me.

Hope your day is great.

Eric

error with column name &quot;x-amount&quot;

error with column name "x-amount"