Multiple Queries and CFOUTPUT 2

[powahusr]

I post my question in here, because you folks are the best!

Anyway,

When I create a query that retrieves a Recordset based off of how I build my SQL statement, I would like to accomplish the following.

When the results of a query are displayed to screen, I would like to update a particular item in the retrieved Recordset. It’s rather easy to do this with a simple Textbox form control where I specify the initial value of the textbox to that field in the Recordset (e.g., <input type=&quot;text&quot; name=&quot;Team&quot; value=#QueryName.FieldName#>). Ok, now the tricky part, well at least to me. Instead of a TextBox, I would rather use a “Select Box”. However, the data in the Select Box would be pulled from another table, irrelevant to the current Recordset.

To bring more light to this problem, let me give a simple example:

I have a table that contains the following fields:

Name
Favorite_Color
Favorite_Number

Say I created a query that displayed all names of people who’s favorite color is “Green” and the following record displayed:

Joe Smith Green 7

I want to be able to update both fields “Favorite_Color(Green) ” AND “Favorite_Number(7) ” with items listed in a Select Box which are populated with data from other tables “Colors” & “Numbers”.

This is the best I could do to break down the problem into its simplest form.

If that’s not asking for enough, I would also like to have the original value “7” and “Green” selected by default.

How do I do this, any suggestions?

Thanks in Advance!!!

 

[GunJack]

If you want to use a select box, I think this is all you need.

<cfquery name=&quot;colors&quot; ...
select * from colorTable
</cfquery>

<cfquery name=&quot;numbers&quot; ...
select * from numbersTable
</cfquery>

<cfoutput query=&quot;myResults&quot;>
#name#

<select name=&quot;newColor&quot;>
<cfloop query=&quot;colors&quot;>
<option<cfif colors.color is myResults.color> selected=&quot;yes&quot;</cfif>#colors.#color#
</cfloop>
</select>

...Repeat for numbers query ...

</cfoutput>

Hope this helps,
GJ

 

[powahusr]

Thanks for your response GunJack, but I'm getting an error when I attempt to display the page.

The following expression is causing an error:

#colors.#color#

 

[GunJack]

Ooops, that should be #colors.color# and you'll need to change the fieldname to match your database.

Let me know if you have any more trouble,
GJ

 

[powahusr]

I figured it out, works like a charm. Thanks alot GunJack, You were a great help!!!

 

[GunJack]

Glad I could help

GJ