Solved

How to QuerySetCell when ColumnName has spaces

Posted on 2004-04-21
8
602 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
ID: 10884677
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
ID: 10884711
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
ID: 10884802
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 17

Expert Comment

by:Tacobell777
ID: 10884821
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
 
LVL 18

Expert Comment

by:Plucka
ID: 10884852
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
ID: 10885226
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
ID: 10885305
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
ID: 10889400
" 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 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