Get error with linked drop down and textbox

[dunskii]

Hi again,
On my form the user is be able to select a category from a drop down menu....if it is not there the user can select the option "Other..", then enter a new category in the text box...

The new category is added to the db and everything works hunky dory, but if the user selects a category from the drop down menu... the following error appears...

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

The error occurred in E:\CFusionMX\

http://wwwroot\Y_Factor\Business\Administration\Add_Business\Collect_details.cfm:

line 63

61 : <cfoutput query="qGetSubCatID">#Sub_Cat_ID#</cfoutput>
62 : <cfelse>#FORM.sub_category#
63 : </cfif>
64 : )
65 : </cfquery>

SQL INSERT INTO Business ( Market_ID, Zone_ID, Area_ID, Category_ID, Sub_Cat_ID ) VALUES ( '2', '2', '5', '2', )
DATASOURCE Y_Business
VENDORERRORCODE -3502
SQLSTATE 42000

Here my code and i'm running access2000 if it helps...


<cfquery datasource="Y_Business" name="qInsertCategory">
INSERT INTO Business
(
Market_ID,
Zone_ID,
Area_ID,
Category_ID,
Sub_Cat_ID
)
VALUES
(
'#FORM.market#',
'#FORM.zone#',
'#FORM.area#',
'#FORM.category#',
<cfif isDefined("FORM.other_sub_cat")>
<cfoutput query="qGetSubCatID">#Sub_Cat_ID#</cfoutput>
<cfelse>#FORM.sub_category#
</cfif>
)
</cfquery>

 

[TruthInSatire]

What is the code for your form?
the coldfusion and the html generated by coldfusion. It's not getting a value from the dropdown box.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[dunskii]

heres the form...thanks for your help


<form action="Collect_details.cfm" method="post">
<tr><Td>
<table><Tr>
<td>Select Region</td>
<td><select name="Market">
<option value="no" selected>Select Region</option>
<cfoutput query="qGetMarket">
<option value="#qGetMarket.Market_ID#">#qGetMarket.Name#</option>
</cfoutput>

<option value="other">Other...</option>
</select></td>
</tr>
<tr>
<td>Select District</td>
<td><select name="Zone">
<option value="no" selected>Select District</option>
<cfoutput query="qGetZone">
<option value="#qGetZone.Zone_ID#">#qGetZone.Name#</option>
</cfoutput>
<option value="">Other...</option>
</select></td>
</tr>
<tr>
<td>Select Suburb</td>
<td><select name="Area">
<option value="no" selected>Select Suburb</option>
<cfoutput query="qGetArea">
<option value="#qGetArea.Area_ID#">#qGetArea.Name#</option>
</cfoutput>
<option value="">Other...</option>
</select></td>
</tr>
</table>

</td></tr>
<tr><td>
<table width="100%" border="0" cellpadding="5">
<tr>
<td>Select main business type <select name="category">
<option value="no" selected>Select Category</option>
<cfoutput query="qGetCategory">
<option value="#qGetCategory.Category_ID#">#qGetCategory.Name#</option>
</cfoutput>
</select></td>
</tr>
<tr>
<td>Select business specialty <select name="sub_category">
<option value="no" selected>Select Sub Category</option>
<cfoutput query="qGetSubCategory"><option value="#qGetSubCategory.Sub_Cat_ID#">#qGetSubCategory.Name#</option></cfoutput>
<option value="other">Other...</option>
</select>
Other: <input type="text" value="" name="other_sub_cat" size="20" />
</td>
</tr>
<tr><td><input name="sendCategory" type="submit" value="Continue" /></td>

 

[TruthInSatire]

please post the code if you view source after cf generates the HTML for this form.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[dunskii]

<form action="Collect_details.cfm" method="post">
<tr><Td>
<table><Tr>
<td>Select Region</td>
<td><select name="Market">

<option value="no" selected>Select Region</option>

<option value="1">Test345</option>

<option value="2">tyewst</option>

<option value="5">bbbbbbbbbbbbbbbbbbbbbb</option>

<option value="6">test33</option>

<option value="7">thisisity22222</option>



<option value="other">Other...</option>
</select></td>
</tr>
<tr>
<td>Select District</td>
<td><select name="Zone">
<option value="no" selected>Select District</option>


<option value="1">fghjfg333</option>

<option value="2">sdrgsdf</option>

<option value="3">test66666</option>

<option value="4">747567456</option>

<option value="5">trested1234</option>

<option value="">Other...</option>

</select></td>
</tr>
<tr>
<td>Select Suburb</td>
<td><select name="Area">
<option value="no" selected>Select Suburb</option>

<option value="5">ffffffff</option>


<option value="6">test7890</option>

<option value="7">ffffddsss55555</option>

<option value="8">345tresdf</option>

<option value="">Other...</option>
</select></td>
</tr>
</table>


</td></tr>
<tr><td>
<table width="100%" border="0" cellpadding="5">
<tr>
<td>Select main business type <select name="category">
<option value="no" selected>Select Category</option>

<option value="1">dfdfg</option>


<option value="2">testing56789</option>

</select></td>
</tr>
<tr>
<td>Select business specialty <select name="sub_category">
<option value="no" selected>Select Sub Category</option>
<option value="1">jhkgj2222</option><option value="2">lkghhk44</option><option value="3">dude1</option><option value="4">dude2</option><option value="5">dude</option><option value="6">sdfghjkl</option><option value="7">eeeeee</option><option value="8">eeeeee</option><option value="9">aaaaaaa</option><option value="10">rererererere</option><option value="11">34567</option><option value="12">test456789</option><option value="13">ascfde</option><option value="14">asdfghgjh</option><option value="15">asdf</option><option value="16">dddddd</option><option value="17">

http://wwwwwwwww</option><option

value="18">

http://wwwwwwwww</option>

<option value="other">Other...</option>
</select>
Other: <input type="text" value="" name="other_sub_cat" size="20" />
</td>
</tr>
<tr><td><input name="sendCategory" type="submit" value="Continue" /></td>

</tr>

</table>


</td></tr>
</form>

 

[TruthInSatire]

this wont fix your problem but i noticed in your error you use unneeded output tags that create a loop.

61 : <cfoutput query="qGetSubCatID">#Sub_Cat_ID#</cfoutput>
62 : <cfelse>#FORM.sub_category#
63 : </cfif>
64 : )
65 : </cfquery>

you don't need the <cfoutput query = 'qGetSubCatID'> tag.

just #qGetSubCatID.Sub_Cat_ID# will work for you.


I don't know why i didn't see this before...

<cfif isDefined("FORM.other_sub_cat")>
<cfoutput query="qGetSubCatID">#Sub_Cat_ID#</cfoutput>
<cfelse>#FORM.sub_category#
</cfif>

other_sub_cat will allways be defined in form. text boxes are are passed even if the value is ''

change the bolded line to read <cfif len(trim(form.other_sub_cat))>
that will check to make sure there is something in the other_sub_cat field. if not it will use the sub_category field.

sorry about that.


A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[dunskii]

thanks for help bombboy,

i ended up changing

<cfif isDefined("FORM.other_sub_cat")>
<cfoutput query="qGetSubCatID">#Sub_Cat_ID#</cfoutput>
<cfelse>#FORM.sub_category#
</cfif>

to

<cfif FORM.sub_category is "other">
'#form.other_sub_cat#'
<cfelse>
'#FORM.sub_category#'
</cfif>

and seems to work