Using form values in query statement

[kmurphy3]

Hello~

I am building a form where a user should choose a category from a
dropdown menu populated from the "categories" table, and then a second
dropdown menu should appear if there are any subcategories available
for the selected category. I have joined the tables in Access so that
there is a 1-to-many relationship between the categories.id field and
the subcategories.categoryID field. This is the query that Access
generated, and I then added the WHERE statement to include the form
value that the user generates.

I am getting the following error:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Too few parameters. Expected 1.

If anyone has any suggestions, they would be very welcome! Thanks.

KC

<code>
<cfquery name="getSubcategories" datasource="corpGov">
SELECT
categories.id
AS
categories_id, subcategories.id
AS
subcategories_id, subcategories.categoryID
FROM
categories
INNER JOIN
subcategories
ON
categories.id = subcategories.categoryID
WHERE
subcategories.categoryID = form.category.id
</cfquery>

<cfselect name="category" message="Please choose a category!"
required="yes">

<cfloop query="getCategories">

<cfoutput>

<option value="#getCategories.id#">#getCategories.name#</
option>

</cfoutput>

</cfloop>
</cfselect>
</div></td>
</tr>

<!--- If Category defined above has
Subcategories available,
display them in a dropdown menu --->
<cfif
isDefined("form.category")>
<tr>
<td>
<div align="right">Subcategory :</div>
</td>
<td>
<div align="left">
<cfselect name="subcategories"
message="Please choose a subcategory!" required="yes">

<option>None</option>

<cfloop query="getSubcategories">

<cfoutput>

<option value="#getSubcategories#">#getSubcategories#</
option>

</cfoutput>

</cfloop>
</cfselect>
</div></td>
</tr>
</cfif>
</code>

 

[imstillatwork]

use the standard ## to tell CF to evaluate that variable

....
subcategories.categoryID = #form.category.id#
....

I would go a step further and use cfqueryparam also

...
subcategories.categoryID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.category.id#">
...

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!