dynamic query with x.recordcount output

[bknutz]

I have a query that gets Distinct States listed in a Sponsor table

<cfquery name="statecount" datasource="#db#">
SELECT DISTINCT STATE FROM tbl_sponsors
WHERE SalesRep = "#form.user#" or Manager = "#form.user#"
</cfquery>

I then loop through the results with this:

<cfloop from="1" to="#statecount.RecordCount#" index="i">

The results are: WA and OR

Inside the loop I create an array for:

<cfset getTotals = ArrayNew(1)>

<cfset ArrayAppend(getTotals, "getTotals#statecount.State#")> This sets "getTotalsWA" and "getTotalsOR" correlating to each loop.

I use the "getTotals" variable to name my query:

<cfquery name="#getTotals[1]#" datasource="#mydatasource#>

I run into a problem when trying to call the RecordCount of the "getTotals[1]" result - #getTotals[1].RecordCount#.

I get the error:

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members

My logic makes sense to me, but obviously is wrong.  How can I adjust it to work?

[12Robots]

So I see where yur logic is failing you.

So let's say that getTotals[1] = "getTotalsWA"

That means that getTotals[1] is a string

Now you create a query with that value

<cfquery name="#getTotals[1]#" ... >

You now have a query object names "getTotalsWA"

But what you are trying to output is: #getTotals[1].RecordCount#

getTotals[1] is still just the string "getTotalsWA" and therefore does not have the attribute RECORDCOUNT. You are still referencing the string in the array get totals, you are not referencing the newly created variable named "getTotalsWA"

To access that value you need to go at it a little differently. 

Try this:  #variables[getTotals[1]].RecordCount#

I think this will work. Here, I am telling CF to get the variable named #getTotals[1]# (which translates to "getTotalsWA" from the VARIABLES scope.

[bknutz]

Yes, that worked.  I appreciate the explanation. Makes sense when you put it that way. Thank you so much.