Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to QuerySetCell when ColumnName has spaces

Posted on 2004-04-21
8
Medium Priority
?
616 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

971 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