Solved

Columns W Spaces in QueryAddColumn?

Posted on 2004-08-09
4
551 Views
Last Modified: 2013-12-20
I currently have the following code, which generates an error:

<cfset qNew = QueryNew("Col1") />
<cfset myArray = ArrayNew(1) />
<cfset userInput = "Col 2" />
<cfset QueryAddColumn(qNew,userInput,myArray) />
<cfdump var="#qNew#" />

The error is:
The column name "Col 2" is invalid.  
Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.  


In the code userInput is actually a parameter specified by the user and needs to support variables defined by the user which have spaces. ColdFusion supports field names that have spaces and returned from a database just fine. ColdFusion supports it when a query is returned from a database where the fieldnames have had Alias names with spaces applied to them.  Is there an easy way to 'inject' a variable name with a space into the query structure?
0
Comment
Question by:libel_vox
4 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 11757308
you can use regular sql
insert into tablename ( column name, name2) values(value1, value2)
it should do the work.
0
 
LVL 5

Accepted Solution

by:
Seth_Bienek earned 250 total points
ID: 11757394
Some databases allow spaces in column names, but ColdFusion does not allow spaces in variable names.  Once data comes back from a query, it is placed in a variable, so the variable rules apply as opposed to the database rules.

The fact that CF allows you to work with columns that have invalid characters (i.e. spaces) in them is a compatibility feature only, and doesn't stretch forward into the rest of the CFML language.  Good coding practice suggests that no variable (even database columns) should have spaces if it can be helped.

That being said, the solution to your problem is to add the column with a valid name (maybe an underscore in place of the space), and work with it that way.  If the data eventually needs to go back into the table, yuo CAN use spaces in queries, depending on your database (using pinaldave's suggestion above).

I hope this sheds some light on the issue and gets you headed in the right direction.  More questions, just ask.

Seth
0
 
LVL 2

Expert Comment

by:russmichaels
ID: 11757565
As seth says, you should replace the space with an underscore.
use the coldfusion Replace() function to do this.

Also you can have column name swith spaces in the database, it is not a good idea, just like using reserved words, you can do it though.
The method you have to us ein a query when select columns that are basically invalid is

select [columnName]

the [] allow you to implicity specify the name regardless of the spaces or the fact it may be a reserved word.

Regards

Russ Michaels
0
 

Author Comment

by:libel_vox
ID: 11764915
Most knowledgable, thoughtful answer. Points to Seth.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Fix A 403 error 13 107
Why addon domain redirects to the original domain? 8 66
PHP in Apache server 20 92
setup wamp server for first time 2 67
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now