Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

How to QuerySetCell when ColumnName has spaces

With a QuerySetCell statement, ColdFusion does not allow you to enter a column name to change that contains spaces. However, I am currently generating queries that have Alias values for column names and these Alias column names sometimes contain spaces. After the query is made sometimes I need to take the data out of a query cell, performan an operation on it, and then put the new value back into the cell (converting retrieved database datetime values into UTC).

For example:

<cfset tempAlias = "My Date Column" />

The following isn't possible because the columname, tempAlias, has spaces and is an invalid column name (even though that is the Column name that was created after querying the database with "SELECT myDateField AS [My Date Column]" statement:
<cfset QuerySetCell(myQuery,tempAlias,newValue,CurrentRow) />

Any ideas on how to get around this?
0
libel_vox
Asked:
libel_vox
  • 3
  • 2
  • 2
  • +1
1 Solution
 
PluckaCommented:
Hi libel_vox,

Have you tried.

<cfset tempAlias = "[My Date Column]" />

Other than that, I think it's bad pratice to use spaces in aliases, just makes everything more complicated. This is much better.

SELECT myDateField AS MyDateColumn

or

SELECT myDateField AS My_Date_Column

or

SELECT myDateField AS myDateColumn

which is what most coders use today, this is a standard variable naming convention.

Regards
Plucka
0
 
Tacobell777Commented:
I think you will have to refer to the actual column name instead of the Alias.
So "myDateField" instead of "My date column".
0
 
PluckaCommented:
Tacobell777,

That wouldn't work if the field is referenced more than once, which is the most common reason AS is used.

Regards
Plucka
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Tacobell777Commented:
I never tried what he is trying to do, can anyone of you actually confirm that when you create aliases you can do a setCell and insert a value by referencing to the Alias? Test it and see if it works for

SELECT column AS test1, column AS test2

I personally think it won't work, by the way, what is the difference anyway, no matter if you alias your columns as
columnName AS test1
columnName AS test2
the insert will still be in the same column so why reference to it via the ALIAS?
0
 
PluckaCommented:
Sounds right. Haven't tested it, by the question I assumed that it worked for aliases just not aliases with spaces.

From your other post Taco, you have more experience with newQuery stuff than me.
0
 
heathprovostCommented:
I just did some test code, and I dont see the problem you are describing... Here is the test code Im using:

<cfquery name="test" datasource="test">
  SELECT somefield AS [Some Field]
  FROM sometable
</cfquery>
<cfset tempalias = "Some Field">
<cfloop query="test">
  <cfset void = querysetcell(test, tempalias, "New Value", test.currentrow)>
</cfloop>
<!---The following dump works correctly and displays the changed value--->
<cfdump var="#test#">

What am I missing?

Heath
0
 
heathprovostCommented:
Ok... I was using CF5. I switched to MX and the same test code fails, so I figured you are using CFMX. Anyway, I played around with it a bit and found a way to do this that works, but to be honest it is VERY scary looking. I had no idea you could do this. Anyway, this is the above example modified to work on CFMX:

<cfquery name="test" datasource="test">
  SELECT somefield AS [Some Field]
  FROM sometable
</cfquery>
<cfset tempalias = "Some Field">
<cfloop query="test">
  <cfset test[tempalias][test.currentrow] = "New Value">
</cfloop>
<!---The following dump works correctly and displays the changed value--->
<cfdump var="#test#">

And yes, thats right... Im directly setting the value in a query field using only a cfset statement. I always thought that was not possible, but it sure seems to work just fine. I went back and tested this method on CF5 and it works there as well. I knew it was possible to address querycols using array notation, but I never would have guessed that they were assignable that way. Learn something new everyday...

Heath
0
 
libel_voxAuthor Commented:
" but to be honest it is VERY scary looking"

It's Not scary if it works. :D Yes, Heath, as you deducted I was using ColdFusion MX. I tried your solution and it works great (I briefly tried changing values with array notation but didn't go far enough). You get the points.

As a programmer, Pluka, I understand the extra complexity that comes with using Alias names with spaces. However, because I'm allowing a user of a system name the fields what they want, it is important to support names that are the most readable. Hence, support for spaces.
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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