0

I have a CFQUERY pulling three columns. The following CFSELECT allows the user to make a selection from various results based on the 'display' parameter, and sets the value to the 'value' parameter.

I would like to pass the third unused value of that record to a variable to be used in a later query. I cannot set the 'value' field to the column I need since that value is needed in a query following this one (queries populate based on previous drop down selections).

Is there a way to do this? To somehow have the CFSELECT grab 2 separate values?

SubRegionName is Displayed.

State is the value to pass.

SubRegionCD for this selection made is needed later.

Code example below:

    <cfquery name="qrySubTurf"
 DATASOURCESTUFF>
 SELECT SubRegionName, SubRegionCd, State
 From dbo.tblRegions 
 where Region='#form.getRegion#'  <!---Previous CFSELCT value--->
 order by SubRegionName
     </cfquery>

     <cfselect name="getSubTurf"
 style="width:220px"
 size=1
 multiple="no"
 query="qrySubTurf"
 value="state"                   <!---Value passed to the next CFQUERY--->
 display="SubRegionName"         <!---Value displayed to user--->
 queryPosition="below"
 onChange="AddForm.submit();">
        <option value=""></option>
     </cfselect>

Now I need to grab the SubRegionCD associated with the users selection of State and SubRegionName and assign it to a variable that can be used in the final query. I cannot use State alone to determine the SubRegionCD, but I CAN use SubRegionName to make a 1-1 match. Help?

FrankDev
  • 15
  • 2
  • 5

2 Answers2

1

Simplest (in terms of littlest-possible code change) would be to do:

<cfquery name="qrySubTurf"
 DATASOURCESTUFF>
 SELECT SubRegionName, SubRegionCd + ',' + State AS Key
 From dbo.tblRegions 
 where Region=<cfqueryparam value="#form.getRegion#" cfsqltype="CF_SQL_VARCHAR">
 order by SubRegionName
</cfquery>

<cfselect name="getSubTurf"
 style="width:220px"
 size=1
 multiple="no"
 query="qrySubTurf"
 value="Key"
 display="SubRegionName"
 queryPosition="below"
 onChange="AddForm.submit();">
  <option value=""></option>
</cfselect>

And then use ListFirst(FORM.getSubTurf) and ListLast(FORM.getSubTurf). Also, don't forget to use <cfqueryparam>.

Tomalak
  • 332,285
  • 67
  • 532
  • 628
  • Thanks for replying so quickly! I'm going to try this first thing in the morning and reply back. – FrankDev Jan 20 '11 at 23:18
  • Unfortunately, the above code is not working as intended. The query is correctly grabbing the two values, and using ListLast/First is also grabbing the correct value, but now my form is no longer maintaining its selections in between page submissions (the user's selection of Region and SubRegion are resetting to blank). Is this what you meant by reminding me to use ? If so, what would be the syntax to ensure those values stay put? Thanks! – FrankDev Jan 21 '11 at 15:42
0

To maintain the query result across pages you have to split the value returned to the form's action page ... this is was the answer suggested ... <cfqueryparam value ="#ListFirst(form.***)#>

Tinu8805
  • 11
  • 4