Query of Queries failing in Coldfusion 10

Question

I'm getting and error when I tried to do a query of query.

Table named allData was not found in memory. The name is misspelled or the table is not defined.

I have an excel document and I'm outputting to a coldfusion var called allData, then I'm doing a query on that var. but I'm getting an error:

What am I doing wrong? The first dump shows the table appropriately.

function name="validateExcel" access="public" output="yes" returnType="void" 
hint="search for dogs">

<cfspreadsheet
 action="read"
 src="#SESSION.theExcelFile#"
 headerrow= "1"
 excludeHeaderRow = "true"
 query = "allData"
 rows = "1-25"/>


  <cfdump var = "#allData#"/>

  <cfset rotCheck = new Query(
        sql =  "SELECT * FROM allData where dogType like '%rot'",
        dbtype = "query"

  ) />

  <cfset dogResult = rotCheck.execute().getResult() />

  <cfdump 
  var = "#dogResult#" />

 </cffunction>

I have to run, but short answer - the query variable from the spreadsheet is not in scope within the [Query.cfc](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ffb.html). Either [pass in the query object as a parameter ie `new Query(...., allData=allData)`](http://www.isummation.com/blog/how-to-use-query-of-query-in-cfscript/) or use `cfquery` instead. (The documentation on Query.cfc is somewhat lacking IMO. ) — Leigh, Feb 15 '14 at 21:59
@Leigh, you should've made that an "answer" (it being a good description what the problem is). — Adam Cameron, Feb 15 '14 at 23:32
Over and above Leigh's answer... also make sure you VAR your variables inside a function. — Adam Cameron, Feb 15 '14 at 23:34
@Leigh. I got everything working with your answer and the provided link. Please submit you short answer as an answer, so I can accept. — ConfusedDeer, Feb 16 '14 at 20:12

score 2 · Accepted Answer · answered Feb 17 '14 at 01:48

(From comments ...)

I have to run, but short answer - the query variable from the spreadsheet is not in scope within the Query.cfc. (The documentation on Query.cfc is somewhat lacking IMO. ) Either pass in the query object as a parameter ie new Query(...., allData=allData) or use a <cfquery> instead.

score 1 · Answer 2 · answered Feb 15 '14 at 23:24

1

Given that the dump works, the allData variable exists. A cfquery tag with the appropriate attributes will solve your problem for you.

answered Feb 15 '14 at 23:24

Dan Bracuk

20,699
4
26
43

Query of Queries failing in Coldfusion 10

2 Answers2

Linked