?
Solved

Coldfusion with column names with space and # sign

Posted on 2011-10-09
14
Medium Priority
?
869 Views
Last Modified: 2013-12-24
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
Comment
Question by:sglee
  • 5
  • 3
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:RickEpnet
ID: 36940366
I am pretty sure if you surrounded them by [] for Example

Select [PO #], [Next Field]
0
 
LVL 13

Expert Comment

by:ansudhindra
ID: 36940535
try like

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

or better in coldfusion you specify the column header.

BR-Sudhindra-www.clicksperday.com
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36940620
Hi,

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

- Bhavesh
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 36942534
   >  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
 
LVL 39

Expert Comment

by:gdemaria
ID: 36942604
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
 
LVL 14

Assisted Solution

by:RickEpnet
RickEpnet earned 500 total points
ID: 36942803
_agx_ Makes a good point that would simplify your out put a lot.

Also if possible gdemaria has a good idea if possible.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36965588
@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
 
LVL 14

Expert Comment

by:RickEpnet
ID: 36965842
That is kind of you but I am totally cool with leaving it.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36966177
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
 
LVL 52

Expert Comment

by:_agx_
ID: 36966181
> ... leave it up to your guys

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

Author Comment

by:sglee
ID: 36969137
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
 

Author Closing Comment

by:sglee
ID: 36969264
Thank you all.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36969273
@sglee - Great feedback, btw. Thanks for taking the time to do that.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question