• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 990
  • Last Modified:

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.
0
sglee
Asked:
sglee
  • 5
  • 3
  • 2
  • +3
2 Solutions
 
RickEpnetCommented:
I am pretty sure if you surrounded them by [] for Example

Select [PO #], [Next Field]
0
 
ansudhindraCommented:
try like

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

or better in coldfusion you specify the column header.

BR-Sudhindra-www.clicksperday.com
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

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

- Bhavesh
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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

Technically ... yes.  But it'll make things more complex because you'll have to escape the reserved characters everywhere.   A simpler solution is to give the column an alias (like "PONum" and "OrderDate") in your sql.  Then you can output those columns the same as usual.

<cfquery name="yourQuery" datasource="YourDSN">
	SELECT [PO ##] AS PoNum,  [ORDER DATE] OrderDate
	FROM   tableName
</cfquery>
<cfoutput query="yourQuery">
<!--- use the alias names in your output --->
#PONum# | #OrderDate#<br>
</cfoutput>

Open in new window


If you absolutely must use the invalid column names, you'll have to escape the # and spaces in your query as others mentioned  AND use a special syntax in your cfoutput  ie queryName["columnName"][row]

<cfquery name="yourQuery" datasource="AccessUnicode">
	SELECT [PO ##], [ORDER DATE] 
	FROM   someTable
</cfquery>
<cfoutput query="yourQuery">
	#yourQuery["PO ##"][currentRow]# | 
	#yourQuery["ORDER DATE"][currentRow]#<br>
</cfoutput>

Open in new window


 
0
 
gdemariaCommented:
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...

0
 
RickEpnetCommented:
_agx_ Makes a good point that would simplify your out put a lot.

Also if possible gdemaria has a good idea if possible.
0
 
_agx_Commented:
@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.
0
 
RickEpnetCommented:
That is kind of you but I am totally cool with leaving it.
0
 
_agx_Commented:
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
0
 
_agx_Commented:
> ... leave it up to your guys

Gah ... "you guys".  I hate it when my fingers try and make up new slang ...
0
 
sgleeAuthor Commented:
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.
0
 
sgleeAuthor Commented:
Thank you all.
0
 
_agx_Commented:
@sglee - Great feedback, btw. Thanks for taking the time to do that.
0
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now