Solved

How to QuerySetCell when ColumnName has spaces

Posted on 2004-04-21
8
595 Views
Last Modified: 2013-12-24
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
Comment
Question by:libel_vox
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
I think you will have to refer to the actual column name instead of the Alias.
So "myDateField" instead of "My date column".
0
 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
 
LVL 17

Expert Comment

by:Tacobell777
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 18

Expert Comment

by:Plucka
Comment Utility
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
 
LVL 5

Expert Comment

by:heathprovost
Comment Utility
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
 
LVL 5

Accepted Solution

by:
heathprovost earned 500 total points
Comment Utility
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
 

Author Comment

by:libel_vox
Comment Utility
" 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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

14 Experts available now in Live!

Get 1:1 Help Now