Skip to main content
B
bknutz
Known Participant
January 10, 2011
Question

dynamic query with x.recordcount output

  • January 10, 2011
  • 1 reply
  • 858 views

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?

    This topic has been closed for replies.

    1 reply

    12Robots
    12Robots
    Participating Frequently
    January 10, 2011

    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.

    B
    bknutzAuthor
    Known Participant
    January 10, 2011

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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices