Dave McConnell
asked on
Variable evaluation and/or substitution
The following code produces an error when run on ColdFusion 9.0.2. The error occurs on the #QueryName# evaluation, the #Component# seems to work fine. I am not sure if the name attribute for CFQUERY is having trouble with dynamic names or ???
<CFQUERY name="GetCOMP" datasource="brsdb">
select *
from LU_component
where component not like '%SUMMARY%'
order by SortOrder
</CFQUERY>
<CFOUTPUT query="GetCOMP">
<CFQUERY name="#QueryName#" datasource="brsdb">
exec SP_BRS_REPORTING_DETAIL '#form.year#','#form.month #','#form. fund#','Di rect','#Co mponent#', '#form.bbf y#'
</CFQUERY>
</CFOUTPUT>
The error produced can be found in the attached PDF representing a captured screen shot.
<CFQUERY name="GetCOMP" datasource="brsdb">
select *
from LU_component
where component not like '%SUMMARY%'
order by SortOrder
</CFQUERY>
<CFOUTPUT query="GetCOMP">
<CFQUERY name="#QueryName#" datasource="brsdb">
exec SP_BRS_REPORTING_DETAIL '#form.year#','#form.month
</CFQUERY>
</CFOUTPUT>
The error produced can be found in the attached PDF representing a captured screen shot.
better for this:
<CFOUTPUT query="GetCOMP">
<CFQUERY name="#QueryName#" datasource="brsdb">
exec SP_BRS_REPORTING_DETAIL '#form.year#','#form.month#','#form.fund#','Direct','#Component#','#form.bbfy#'
</CFQUERY>
</CFOUTPUT>
better to do the cfloop list of the query so you can refer them with scope and use it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have attached the error from my screen shot. It is possible that #QueryName# should work, but it does not in my case. If I replace this with text it will process all 30+ query records and process the #Component# without a problem.
Btw, the app is for internal use on our internal network, absolutely no public access, most of our security is at network level. Thanks for the heads up though.
1-9-2014-9-29-38-AM.jpg
Btw, the app is for internal use on our internal network, absolutely no public access, most of our security is at network level. Thanks for the heads up though.
1-9-2014-9-29-38-AM.jpg
ASKER
Everyone,
Please be aware that "QueryName" and "Component" are actual columns within the SQL Server 2008 R2 table "LU_COMPONENT".
Please be aware that "QueryName" and "Component" are actual columns within the SQL Server 2008 R2 table "LU_COMPONENT".
As long as #QueryName# is a valid variable name (starts with a letter, no special characters, ... ) it should be fine.
We need to see the error message to be certain, but from what you described its likely an issue w/the values used for #QueryName#. They must conform to the rules for variable names (start with a letter, no spaces, etc... The example below works fine with valid names like "QueryA", etc...
<cfset myDSN = "Some DSN Name">
<!--- dummy variables for testing --->
<cfset form.year = 2014>
<cfset form.month = 4>
<!--- simulate query --->
<cfset GetCOMP = queryNew("")>
<cfset queryAddColumn(GetCOMP, "QueryName", ["QueryA","QueryB","QueryC
<!--- test query name--->
<CFOUTPUT query="GetCOMP">
<CFQUERY name="#QueryName#" datasource="#myDSN#">
<!--- Be sure to use cfqueryparm in real code ! --->
SELECT '#form.year#' AS YearNunber,'#form.month#' AS MonthNumber
</CFQUERY>
<cfdump var="#variables[QueryName]
</CFOUTPUT>
That is not coldfusion associated error, probably keep the cfdump after your cfquery to see what you are getting and do not generate any pdf or flashapaper unless you see the results whatever you need to
Oops, our posts clashed. Didn't see your responses before posting..
just like @agx said above, both our comments posted almost same time, so i did not saw
Btw, the app is for internal use on our internal network, absolutely no public access, most of our security is at network level. Thanks for the heads up though.
Better safe than sorry IMO. Also cfqueryparam/cfstored is recommended to improve performance. By using bind variables, it enables db's like sql server to cache and reuse execution plans, so it doesn't have to recompile them, boosting performance.
(EDIT) After reading your responses, I think my comments about variable names may still apply. But I think you accidentally posted the wrong image. That error is about VS setup, not CF. Can you post the full CF error message?
ASKER
agx,
Thank you for all your help, the solution was right in front of me. I had inserted dashes in some of the query name column values, and of course this was causing CFQUERY to produce an error when attempting to process this as the name attribute.
And I would also like to thank you for providing additional tips on how best to code with ColdFusion for a variety of reasons.
Others may have provided a similar solution but agx was the first to respond with a viable solution.
Thanks everyone,
Thank you for all your help, the solution was right in front of me. I had inserted dashes in some of the query name column values, and of course this was causing CFQUERY to produce an error when attempting to process this as the name attribute.
And I would also like to thank you for providing additional tips on how best to code with ColdFusion for a variety of reasons.
Others may have provided a similar solution but agx was the first to respond with a viable solution.
Thanks everyone,
You're welcome. Glad I could help.
ASKER
agx,
Well it was working and now I am seeing that damn 500 error again. I have provided a list of columns and values from the GetCOMP query to show what I am using for the QueryName value. It runs fine unless I add a simply CFOUTPUT tag to use the query, then I error out again.
ReportTitle = "OIG SUMMARY" - QueryName = "OIGSUMMARY" - Component = "OIG SUMMARY"
ReportTitle = "JA Summary" - QueryName = "JASUMMARY" - Component = "JA SUMMARY"
ReportTitle = "JI Summary" - QueryName = "JISUMMARY" - Component = "JI SUMMARY"
ReportTitle = "JP Summary" - QueryName = "JPSUMMARY" - Component = "JP SUMMARY"
ReportTitle = "OFFICE J" - QueryName = "JQUERY" - Component = "J"
ReportTitle = "OFFICE JC" - QueryName = "JCQUERY" - Component = "JC"
ReportTitle = "OFFICE JE" - QueryName = "JEQUERY" - Component = "JE"
ReportTitle = "OFFICE JA" - QueryName = "JAQUERY" - Component = "JA"
ReportTitle = "OFFICE JA-2" - QueryName = "JA2QUERY" - Component = "JA-2"
ReportTitle = "OFFICE JA-3" - QueryName = "JA3QUERY" - Component = "JA-3"
ReportTitle = "OFFICE JA-4" - QueryName = "JA4QUERY" - Component = "JA-4"
ReportTitle = "OFFICE JA-5" - QueryName = "JA5QUERY" - Component = "JA-5"
ReportTitle = "OFFICE JA-6" - QueryName = "JA6QUERY" - Component = "JA-6"
ReportTitle = "OFFICE JA-7" - QueryName = "JA7QUERY" - Component = "JA-7"
ReportTitle = "OFFICE JA-9" - QueryName = "JA9QUERY" - Component = "JA-9"
ReportTitle = "OFFICE JA-A" - QueryName = "JAAQUERY" - Component = "JA-A"
ReportTitle = "OFFICE JA-F" - QueryName = "JAFQUERY" - Component = "JA-F"
ReportTitle = "OFFICE JA-P" - QueryName = "JAOQUERY" - Component = "JA-P"
ReportTitle = "OFFICE JA-R" - QueryName = "JAPQUERY" - Component = "JA-R"
ReportTitle = "OFFICE JAO" - QueryName = "JARQUERY" - Component = "JAO"
ReportTitle = "OFFICE JAS" - QueryName = "JASQUERY" - Component = "JAS"
ReportTitle = "OFFICE JI" - QueryName = "JIQUERY" - Component = "JI"
ReportTitle = "OFFICE JI-1" - QueryName = "JI1QUERY" - Component = "JI-1"
ReportTitle = "OFFICE JI-2" - QueryName = "JI3QUERY" - Component = "JI-2"
ReportTitle = "OFFICE JI-3" - QueryName = "JI4AQUERY" - Component = "JI-3"
ReportTitle = "OFFICE JI-4" - QueryName = "JI4QUERY" - Component = "JI-4"
ReportTitle = "OFFICE JI-4-A" - QueryName = "JI5QUERY" - Component = "JI-4-A"
ReportTitle = "OFFICE JI-5" - QueryName = "JI6QUERY" - Component = "JI-5"
ReportTitle = "OFFICE JI-6" - QueryName = "JI7QUERY" - Component = "JI-6"
ReportTitle = "OFFICE JI-7" - QueryName = "JI8QUERY" - Component = "JI-7"
ReportTitle = "OFFICE JI-8" - QueryName = "JI9AQUERY" - Component = "JI-8"
ReportTitle = "OFFICE JI-9" - QueryName = "JI9BQUERY" - Component = "JI-9"
ReportTitle = "OFFICE JI-9-A" - QueryName = "JI9QUERY" - Component = "JI-9-A"
ReportTitle = "OFFICE JI-9-B" - QueryName = "JI10QUERY" - Component = "JI-9-B"
ReportTitle = "OFFICE JI-10" - QueryName = "JI2QUERY" - Component = "JI-10"
ReportTitle = "OFFICE JI-W" - QueryName = "JIBQUERY" - Component = "JI-W"
ReportTitle = "OFFICE JIB" - QueryName = "JIIQUERY" - Component = "JIB"
ReportTitle = "OFFICE JII" - QueryName = "JIWQUERY" - Component = "JII"
ReportTitle = "OFFICE JP" - QueryName = "JPQUERY" - Component = "JP"
ReportTitle = "OFFICE JPB" - QueryName = "JPBQUERY" - Component = "JPB"
ReportTitle = "OFFICE JPC" - QueryName = "JPCQUERY" - Component = "JPC"
ReportTitle = "OFFICE JPE" - QueryName = "JPEQUERY" - Component = "JPE"
ReportTitle = "OFFICE JPF" - QueryName = "JPFQUERY" - Component = "JPF"
ReportTitle = "OFFICE JPH" - QueryName = "JPHQUERY" - Component = "JPH"
ReportTitle = "OFFICE JPM" - QueryName = "JPMQUERY" - Component = "JPM"
ReportTitle = "OFFICE NOT DEFINED" - QueryName = "UNDQUERY" - Component = "NOT DEFINED"
Any suggestions,
Well it was working and now I am seeing that damn 500 error again. I have provided a list of columns and values from the GetCOMP query to show what I am using for the QueryName value. It runs fine unless I add a simply CFOUTPUT tag to use the query, then I error out again.
ReportTitle = "OIG SUMMARY" - QueryName = "OIGSUMMARY" - Component = "OIG SUMMARY"
ReportTitle = "JA Summary" - QueryName = "JASUMMARY" - Component = "JA SUMMARY"
ReportTitle = "JI Summary" - QueryName = "JISUMMARY" - Component = "JI SUMMARY"
ReportTitle = "JP Summary" - QueryName = "JPSUMMARY" - Component = "JP SUMMARY"
ReportTitle = "OFFICE J" - QueryName = "JQUERY" - Component = "J"
ReportTitle = "OFFICE JC" - QueryName = "JCQUERY" - Component = "JC"
ReportTitle = "OFFICE JE" - QueryName = "JEQUERY" - Component = "JE"
ReportTitle = "OFFICE JA" - QueryName = "JAQUERY" - Component = "JA"
ReportTitle = "OFFICE JA-2" - QueryName = "JA2QUERY" - Component = "JA-2"
ReportTitle = "OFFICE JA-3" - QueryName = "JA3QUERY" - Component = "JA-3"
ReportTitle = "OFFICE JA-4" - QueryName = "JA4QUERY" - Component = "JA-4"
ReportTitle = "OFFICE JA-5" - QueryName = "JA5QUERY" - Component = "JA-5"
ReportTitle = "OFFICE JA-6" - QueryName = "JA6QUERY" - Component = "JA-6"
ReportTitle = "OFFICE JA-7" - QueryName = "JA7QUERY" - Component = "JA-7"
ReportTitle = "OFFICE JA-9" - QueryName = "JA9QUERY" - Component = "JA-9"
ReportTitle = "OFFICE JA-A" - QueryName = "JAAQUERY" - Component = "JA-A"
ReportTitle = "OFFICE JA-F" - QueryName = "JAFQUERY" - Component = "JA-F"
ReportTitle = "OFFICE JA-P" - QueryName = "JAOQUERY" - Component = "JA-P"
ReportTitle = "OFFICE JA-R" - QueryName = "JAPQUERY" - Component = "JA-R"
ReportTitle = "OFFICE JAO" - QueryName = "JARQUERY" - Component = "JAO"
ReportTitle = "OFFICE JAS" - QueryName = "JASQUERY" - Component = "JAS"
ReportTitle = "OFFICE JI" - QueryName = "JIQUERY" - Component = "JI"
ReportTitle = "OFFICE JI-1" - QueryName = "JI1QUERY" - Component = "JI-1"
ReportTitle = "OFFICE JI-2" - QueryName = "JI3QUERY" - Component = "JI-2"
ReportTitle = "OFFICE JI-3" - QueryName = "JI4AQUERY" - Component = "JI-3"
ReportTitle = "OFFICE JI-4" - QueryName = "JI4QUERY" - Component = "JI-4"
ReportTitle = "OFFICE JI-4-A" - QueryName = "JI5QUERY" - Component = "JI-4-A"
ReportTitle = "OFFICE JI-5" - QueryName = "JI6QUERY" - Component = "JI-5"
ReportTitle = "OFFICE JI-6" - QueryName = "JI7QUERY" - Component = "JI-6"
ReportTitle = "OFFICE JI-7" - QueryName = "JI8QUERY" - Component = "JI-7"
ReportTitle = "OFFICE JI-8" - QueryName = "JI9AQUERY" - Component = "JI-8"
ReportTitle = "OFFICE JI-9" - QueryName = "JI9BQUERY" - Component = "JI-9"
ReportTitle = "OFFICE JI-9-A" - QueryName = "JI9QUERY" - Component = "JI-9-A"
ReportTitle = "OFFICE JI-9-B" - QueryName = "JI10QUERY" - Component = "JI-9-B"
ReportTitle = "OFFICE JI-10" - QueryName = "JI2QUERY" - Component = "JI-10"
ReportTitle = "OFFICE JI-W" - QueryName = "JIBQUERY" - Component = "JI-W"
ReportTitle = "OFFICE JIB" - QueryName = "JIIQUERY" - Component = "JIB"
ReportTitle = "OFFICE JII" - QueryName = "JIWQUERY" - Component = "JII"
ReportTitle = "OFFICE JP" - QueryName = "JPQUERY" - Component = "JP"
ReportTitle = "OFFICE JPB" - QueryName = "JPBQUERY" - Component = "JPB"
ReportTitle = "OFFICE JPC" - QueryName = "JPCQUERY" - Component = "JPC"
ReportTitle = "OFFICE JPE" - QueryName = "JPEQUERY" - Component = "JPE"
ReportTitle = "OFFICE JPF" - QueryName = "JPFQUERY" - Component = "JPF"
ReportTitle = "OFFICE JPH" - QueryName = "JPHQUERY" - Component = "JPH"
ReportTitle = "OFFICE JPM" - QueryName = "JPMQUERY" - Component = "JPM"
ReportTitle = "OFFICE NOT DEFINED" - QueryName = "UNDQUERY" - Component = "NOT DEFINED"
<CFOUTPUT QUERY="GETCOMP">
<H4 align="left">
<font color="AB562F" size='5' face="Calibri">
ReportTitle = "#getcomp.ReportTitle#" - QueryName = "#getcomp.QueryName#" - Component = "#getcomp.Component#"
</font>
</H4>
<CFOUTPUT query="#getcomp.QueryName#">
</CFOUTPUT>
</CFOUTPUT>
Any suggestions,
> 500 error
Well, as you probably know 500 is just a generic error. What is the actual detail message you're getting? Either
a) check the log files, usually {cfroot}\logs\exception.lo g OR
b) if you're on a dev environment, disable error handling so you can see the error OR
c) wrap the offending code in a cftry/cfcatch and dump the error in the cfcatch clause.
Then post the error here.
<cftry>
... code causing the error
<cfcatch>
<cfdump var="#cfcatch#">
<cfcatch>
</cftry>
Well, as you probably know 500 is just a generic error. What is the actual detail message you're getting? Either
a) check the log files, usually {cfroot}\logs\exception.lo
b) if you're on a dev environment, disable error handling so you can see the error OR
c) wrap the offending code in a cftry/cfcatch and dump the error in the cfcatch clause.
Then post the error here.
<cftry>
... code causing the error
<cfcatch>
<cfdump var="#cfcatch#">
<cfcatch>
</cftry>
Oh, wait... I see at least one problem. You cannot nest <cfoutput> tags if they both use the "query" attribute. Instead, replace the inner <cfoutput> with a <cfloop>
<CFOUTPUT QUERY="GETCOMP">
<H4 align="left">....</H4>
<CFLOOP query="#getcomp.QueryName# ">
</CFLOOP>
</CFOUTPUT>
If you run into other errors, post the error messages from the logs or cftry/cfcatch
<CFOUTPUT QUERY="GETCOMP">
<H4 align="left">....</H4>
<CFLOOP query="#getcomp.QueryName#
</CFLOOP>
</CFOUTPUT>
If you run into other errors, post the error messages from the logs or cftry/cfcatch
please provide the attachment