Link to home
Start Free TrialLog in
Avatar of JohnMac328
JohnMac328Flag for United States of America

asked on

CF - submit two values with select box

I need a select box to enter two values - it is a re-write from a asp program.  For example I can get the value of 10 to go into the correct field in the table but I also need the description to go into the description field.  I am testing with one select box but will have three total that can be chosen from.

 <cfselect name="gift_preset10">
              <option value="">Pick One</option>
              <option value="10">$10 Regal Movie Card</option>
              <option value="10">$10 Santikos Movie Card</option>
              <option value="10">$10 Target Gift Card</option>
              <option value="10">$10 Walmart Gift Card</option>
              <option value="10">$10 HEB Gift Card</option>

Avatar of gdemaria
Flag of United States of America image

I woudl say you have to change your value="" to something distinct so you can distinguish the values from one another.  

Since only the VALUE comes over in the form field, each option has to have a different value for you to be able to know which one was selected.

The ideal way to do this is to pull those values from a database and put the Primary key ID from teh record into the options' value.

If you don't have them in the database, you can append a code like this...


... then just parse out the result
You can use bind to update the form field.

Look at the example below. Passing the same value for different options does not make sense. Hence make a description a part of value.

Once form is submitted, process the field (it is matter of using ListToArray function)

<cfform name="f1">
<cfselect name="gift_preset10">
              <option value="" selected>Pick One</option>
              <option value="10:$10 Regal Movie Card">$10 Regal Movie Card</option>
              <option value="10:$10 Santikos Movie Card">$10 Santikos Movie Card</option>
              <option value="10:$10 Target Gift Card">$10 Target Gift Card</option>
              <option value="10:$10 Walmart Gift Card">$10 Walmart Gift Card</option>
              <option value="10:$10 HEB Gift Card">$10 HEB Gift Card</option>
<cfinput type="text" bind="{f1:gift_preset10}" name="value1" >
Avatar of JohnMac328


I guess that was the main point of my question - how to parse out the two values I need in the parameter value for the insert statement

<cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">,
      <cfqueryparam value="#form.gift_preset10#" cfsqltype="CF_SQL_VARCHAR">
As I wrote in previous post , use ListToArray


<cfset values=ListToArray("10:$10 Regal Movie Card",":")>
I went a head and made the table - I am getting a strange message even though I am using this format in another program

<cfquery name="10dollar" datasource="data">
SELECT     ID, award_desc, award_cost
FROM         gift_present10

and the select

     <cfselect name="gift_present10"><cfoutput query="10dollar">
            <option value="#ID#">#award_desc#</option>

and the error

Invalid tag nesting configuration.  
A query driven cfoutput tag is nested inside a cfoutput tag that also has a query attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.  
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 72
70 :            <cfoutput>#strError#</cfoutput>
71 :               <cfelse>
72 :             <cfselect name="gift_present10"><cfoutput query="10dollar">
Wait - had to remove an upper query - how does this now get the award_cost into the table

   <cfquery name="10dollar" datasource="IntranetDB_Coldfusion">
SELECT     ID, award_desc, award_cost
FROM         gift_present10

 <cfselect name="gift_present10"><cfoutput query="10dollar">
            <option value="#ID#">#award_desc#</option>

  INSERT INTO prize_records (purchaser, award_desc,award_cost)
      <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">,
      <cfqueryparam value="#form.gift_present10#" cfsqltype="CF_SQL_VARCHAR">

with your example in ID: 37999389 - how would the insert statement look with the   <cfqueryparam value="#form.gift_present10#" cfsqltype="CF_SQL_VARCHAR">  etc?
Avatar of _agx_
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
agx - I did not know a select statement could go with a <cfqueryparam value - I am getting a syntax error - I tried moving things around and playing with the commas but it still displays the error

Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'SELECT'.  
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 36
34 :               award_desc, award_cost
35 :  FROM    gift_present10
36 : WHERE ID = <cfqueryparam value="#form.gift_present10#" cfsqltype="cf_sql_integer">

INSERT INTO prize_records (purchaser, award_desc,award_cost)
 SELECT  <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">
              , award_desc
              , award_cost
 FROM    gift_present10
WHERE ID = <cfqueryparam value="#form.gift_present10#" cfsqltype="cf_sql_integer">
Wait - got it
Looks like I will need 3 forms total - can I assume that this will work

<cfif structKeyExists(FORM, "submitButtonForm1")>
<cfif structKeyExists(FORM, "submitButtonForm2")>
<cfif structKeyExists(FORM, "submitButtonForm3")>

and of course each form button is named as above?
To agx's point, all you need to do is save the ID from your new table.  No need to copy the award description and amount into the prize_records table.

 INSERT INTO prize_records (purchaser, award_id)
 Values (

However, if you want a historic snapshot, you may choose to denormalize your prize records table and store the amount and description.   An example of this would be a shopping cart.  A person adds a product on Jan 1 at $10.  On Feb 1, the price changes to $12, you don't want the price to change on all the old orders, so you have to store the $10 price in the orders along with the product name and qty.

This may be true for your situation if the values may change.

The alternative is that if the $10 price will change to a $20 prize, then just disable the $10 prize in the gift_present10 table so it is no longer seen and create a new $20 record in that table to use.

I think this last approach would be the one you may want.   So, I think storing only the ID is the way to go.

One more tip:    It's easy to get lost trying to figure out what IDs go to what table.  I feel it's important to use a convention.  This is how most people do it.   When you create an ID in a table, call the ID the same as the table name with _ID after it.    So, your table gift_present10's ID would be something like   giftPresent10_ID, then use this same name whenever you have a foreign key that points to it.   So in prize_records, you would use the same name giftPresent10_ID.
You can certainly have this type of set up on your action page.  You can have three different forms, or you can even have three different buttons (with these names) in one form to do different actions depending on what the user clicked.

Looks like I will need 3 forms total - can I assume that this will work

<cfif structKeyExists(FORM, "submitButtonForm1")>
<cfif structKeyExists(FORM, "submitButtonForm2")>
<cfif structKeyExists(FORM, "submitButtonForm3")>

and of course each form button is named as above? 

Open in new window

However, I don't see how this fits in your situation.   It seems to me you simply want your user to select one value from your SELECT tag and store it in your table.
First I am recording the purchaser id, award_desc and award cost into the prize records table - that works for the acct dept.

The way it works now is there is a form displayed with $10, 15, 25 and 50

If you only have 5 then a message displays you don't have enough and no form displays

If you have 15 then you will see only the $10 prize box and so on.  I needed to have the different forms to restrict what to display based on how much they have
Or if there is a better way I am open to suggestions
Thanks again