asked on

Coldfusion with column names with space and # sign

Hi,

I have a access database table that a user created column(field) names like following:

PO # : 'PO', followed by a space and the '#' sign
ORDER DATE: 'ORDER' followed by a space and 'DATE'

Is it possible to use these field/column names in CFQUERY SELECT statement without removing # signs and blank spaces?

Thanks you.

RickEpnet

I am pretty sure if you surrounded them by [] for Example

Select [PO #], [Next Field]

Sudhindra A N

try like

select po as [PO %23].....

or better in coldfusion you specify the column header.

BR-Sudhindra-www.clicksperday.com

Bhavesh Shah

Hi,

query will work if its not within CFOUTPUT,
to use query within CFOUTPUT, you need to use [PO ##]

- Bhavesh

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

gdemaria

Since everyone else has provided the solution, I wanted to just ask "the user" created the field? Does your applicaton allow end-users to do some thing that modifies the database? I understand the flexibility of this, but would suggest creating generic field names (perhaps in advance) and allowing a table to define the name and use of those fields.

Table..

, UserCustom1
, UserCustom2
, UserCustom3
...etc...

Then a seperate table defining the use UserCustom1 - label "PO #" - datatype "String" etc...

SOLUTION

RickEpnet

membership

This solution is only available to members.

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

Start Free Trial

_agx_

@sqlee - Thanks for the points. But I think it should be a split as Rick answered the original question of "how to reference the columns in your sql". If you want to reallocate, just use the "Request Attention" link and ask a moderator to re-open the question.

Cheers.

RickEpnet

That is kind of you but I am totally cool with leaving it.

_agx_

Well.. yours was the correct answer to the question asked. I just expanded on it a bit. But I'll leave it up to your guys

_agx_

> ... leave it up to your guys

Gah ... "you guys". I hate it when my fingers try and make up new slang ...

sglee

ASKER

Please accept my sincere apology for not awaring points appropriately.
Since agx_ is ok with the idea of splitting the points, I will request ATTENTION.

Let me take time to give you feedback to each contributor.

RickEpnet: Select [PO #] ... did not work. I had to [PO ##]

agx_: SELECT [PO ##] ... THIS was a Correct Answer.

gdemaria: "the user" created the field? Yes. As an ACCESS/CF Programmer, I always create column/field names like either PO_NO, PartNumber or Customer_No. In this database, my customer who also got a computer degree now CEO of the company creates ACCESS databases for his company and I always endup having to deal with his bad habit as I get involved in either expanding his databse or making in accessible by CF. He is fully aware of his bad habit.

Brichsoft: query will work if its not within CFOUTPUT, ... I did not quite understand it.
Brichsoft: to use query within CFOUTPUT, you need to use [PO ##] ... I see. I simply used SELECT [po ##] as PONo and used PONo for the rest of the coding.

dhindra: [PO %23]..... did not work

I thank all of you tring to help me out and I really appreciate it.

sglee

ASKER

Thank you all.

_agx_

@sglee - Great feedback, btw. Thanks for taking the time to do that.