cfselect onchange or onselect to populate a field via Access dbase query

I'm creating a form that has a dropdown of items to order and I need the price associated with this item to populate the price field.  The dropdown is a cfselect populated with options from an Access database (the prices are here too).  I want the onchange or onselect to query the same database and populate the price field with the price associated with the hardware selected.  There are four dropdowns on the same page that need the same functionality.

Here a portion of the code that I have so far; I removed my poor attempts at adding in the onchange or onselect.  I think that these events need to hit a second page with the query, passing through the hardware selected as the condition in the query and pass that price back through to the main page (or I'm just babbling).

<html>
<head>
<cfquery name="hardware" datasource="mydatabase">
SELECT *
FROM options
WHERE type IN ('hardware', 'none')
ORDER BY description
</cfquery>
</head>

<body>
<cfform action="form.cfm" method="post" name="proposal" id="proposal">
<table>
  <tr>
    <td><cfselect name="hardware" size="1" query="hardware" display="description" value="part_number" selected="none"></cfselect></td>
    <td><input type="text" name="hardware_qty" size="3" value="1"></td>
    <td><input type="hidden" name="hardware_price" value="#hardware_price#"><font face="arial" size="2"><b>&nbsp;$#hardware_price#</b></font></td>
</tr>
</table>
</cfform>
</body>
</html>

Thank you in advance for any help provided,
Brian
chignoliAsked:
Who is Participating?
 
mrichmonCommented:
You most likely need to escape the hardware on the way back as well or put in single quotes (or both) since you have letters, numbers, and dashes

<body onLoad="window.parent.HardwareLookupComplete('#URL.hardware#', #Found#, '#hardware_price#');">

0
 
mrichmonCommented:
It sounds like you need a background lookup and related select boxes

See these threads and let me know if you have further questions

Thread for related select boxes:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21292451.html

Thread for doing the lookup in the backgroud:
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20936125.html



It should be very easy to combine
0
 
trailblazzyr55Commented:
here's a good tutorial on dynamic drop downs and pretty easy to follow if the links above don't help...

http://tutorial166.easycfm.com/

this may also simplify what you're trying to do, this will dynamically populate your drop-downs without refreshing your page, it's a custom tag on macromedia.com..
here's the link:

http://www.macromedia.com/cfusion/exchange/index.cfm?view=sn131&extID=1000288#loc=en_us&view=sn131&extID=1000288&viewName=ColdFusion%20Extension&avm=1

it's free and may reduce some hassle.. I think this is what you're looking to do :o)

Regards,
~trail
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
trailblazzyr55Commented:
you can also use the custom tag on macromedia as a cf_tag or cfmodule.. depends on the use :o)

0
 
chignoliAuthor Commented:
mrichmon - I think that I almost have your background lookup working, but it is getting caught on passing the text portion of the variable back through to the function on the main page.  My code is below:

I used your iFrameRPC.js code...

------Main Page------
<html>
<head>
<script type="text/javascript" language="JavaScript" src="iframeRPC.js"></script>
<script type="text/javascript" language="JavaScript">
function LookupHardware()
{
     var hardware = eval("document.proposal.hardware").value;
         
     if (hardware != "none")
     {     // If a Hardware was entered then look it up in our DB
          callToServer("HardwareLookup.cfm?hardware=" + escape(hardware))
     }
}
function HardwareLookupComplete(hardware, Found, hardware_price)
{    
     if(Found)
     {     // Hardware in DB so fill in data
          eval("document.proposal.hardware_price").value = hardware_price
     }
     return;
}
</script>
</head>

<body>
<cfquery name="hardware" datasource="MyDatabase">
SELECT *
FROM options
WHERE type IN ('hardware', 'none')
ORDER BY description
</cfquery>

<cfform action="verify.cfm" method="post" name="proposal" id="proposal">
<table>
  <tr>
    <td><cfselect name="hardware" size="1" query="hardware" display="description" value="part_number" selected="none" onchange="LookupHardware()"></cfselect></td>
    <td><input type="text" name="hardware_qty" size="3" value="1"></td>
    <td><input type="text" name="hardware_price" id="hardware_price" size="10" disabled></td>
  </tr>
</table>
</body>
</html>

------Lookup Page------
<cfquery datasource="MyDatabase" name="LookupHardware">
     SELECT *
     FROM options
     WHERE part_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#hardware#">
</cfquery>

<cfif LookupHardware.RecordCount EQ 0>
     <cfset Found = false><!--- Did not find Hardware; set other fields to blank --->
     <cfset hardware_price = "">
<cfelse>
     <cfset Found = true><!--- Found Hardware; set other fields to DB values --->
     <cfset hardware_price = "LookupHardware.price">
</cfif>

<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<cfoutput>
<body onLoad="window.parent.HardwareLookupComplete(#URL.hardware#, #Found#, '#hardware_price#');">
</body>
</cfoutput>
</html>

The script error is:
Line: 16
Char: 1
Error: 'M197' is undefined
Code: 0
URL: http://www.xxx.com/form/HardwareLookup.cfm?hardware=M197-570-14-US1

trail - Thanks for the TwoSelectsRelated.cfm link, that will help me on a different site :)

Thank you for your help,
Brian
0
 
chignoliAuthor Commented:
Thank you for your help and the code!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.