libel_vox
asked on
Columns W Spaces in QueryAddColumn?
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,userIn put,myArra y) />
<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?
<cfset qNew = QueryNew("Col1") />
<cfset myArray = ArrayNew(1) />
<cfset userInput = "Col 2" />
<cfset QueryAddColumn(qNew,userIn
<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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Most knowledgable, thoughtful answer. Points to Seth.
insert into tablename ( column name, name2) values(value1, value2)
it should do the work.