Using ASP to process drop-down box data into SQL statement

[KiwiNET uk.geocities.com/haroonnet2002/]

Hello folks, I don't know what they've done to the ordinary ASP newsgroup, I
hope you can answer this question. I am trying to make this piece of code:

If Len(Trim(primary))=0 Then
SQL(1)="Spice = '" & spice & "' And Type = '" & dish_content & "'"
ElseIf Len(Trim(spice))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Type = '" & dish_content
& "'"
ElseIf Len(Trim(dish_content))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '" & spice & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(spice)))=0 Then
SQL(1)="Type = '" & dish_content & "'"
ElseIf (Len(Trim(spice)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Spice = '" & spice & "'"
Else SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '" & spice &
"' And Type = '" & dish_content & "'"
End If

Return records when I select more than one ANY on the drop-down boxes from
this form:

<form method="post" action="OnLineShop.asp">
<table>
<td>Primary Dish Type?
<select name="primary">
<option value="" selected>ANY</option>
<option value="Starter">Starter</option>
<option value="Main Course">Main Course</option>
<option value="Accompaniments">Accompaniments</option>
</select></td>
<td>Spice?
<select name="spice">
<option value="" selected>ANY</option>
<option value="None">None</option>
<option value="Mild">Mild</option>
<option value="Medium">Medium</option>
<option value="Medium+">Medium+</option>
<option value="Medium or Hot">Medium or Hot</option>
<option value="Hot">Hot</option>
<option value="Very Hot">Very Hot</option>
</select>
<!-- <input name="txtData" type="text" id="txtData"> -->
</td>
<td>Dish Content?
<select name="dish_content">
<option value="" selected>ANY</option>
<option value="Vegetarian">Vegetarian</option>
<option value="Vegetable">Containing Vegetables</option>
<option value="Mushroom">Mushroom</option>
<option value="Prawn">Prawn</option>
<option value="King Prawn">King Prawn</option>
<option value="Chicken">Chicken</option>
<option value="Lamb">Lamb (general)</option>
<option value="Keema">Keema</option>
<option value="Mixed">Mixed</option>
<option value="Fish">Fish</option>
<option value="Dairy">Dairy</option>
<option value="Chicken Tikka">Chicken Tikka</option>
<option value="Lamb Tikka">Lamb Tikka</option>
<option value="Tandoori King Prawn">Tandoori King Prawn</option>
</select></td>
<td><input type="submit" name="Submit" value="Go"></td>
</p>
</table>
</form>

Have I missed something from that code?

[cbDevelopment]

Your IF statement is only processing one potential ANY. A better way to
process this situation (with much less code and future flexibility) is to
build your WHERE clause as you go:

if primary<>"" then sql=sql & " primary_dish_type='" & primary & "' AND"
if spice<>"" then sql=sql & " spice='" & spice & "' AND"
if dish_content<>"" then sql=sql & " type='" & dish_content & "' AND"
if right(sql,3)="AND" then sql=left(sql,len(sql)-3)

The last IF will trim off the trailing AND. I left the AND in the third
IF to make it easier if you added more conditions in your filter later.
You could just as easily remove it.

So the basic logic is that if the user picks one of more ANY values, they
are not included in the WHERE clause, otherwise the values are included.

Hope this helps.

=?Utf-8?B?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9vbm5ldD IwMDIv?=
<KiwiNET uk.geocities.com/haroonnet2002/@discussions.microsoft.com>
wrote in news:6F**********************************@microsof t.com:

Hello folks, I don't know what they've done to the ordinary ASP
newsgroup, I hope you can answer this question. I am trying to make
this piece of code:

If Len(Trim(primary))=0 Then
SQL(1)="Spice = '" & spice & "' And Type = '" & dish_content
& "'"
ElseIf Len(Trim(spice))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Type = '"
& dish_content
& "'"
ElseIf Len(Trim(dish_content))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '"
& spice & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(spice)))=0 Then
SQL(1)="Type = '" & dish_content & "'"
ElseIf (Len(Trim(spice)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Primary_Dish_Type = '" & primary & "'"
ElseIf (Len(Trim(primary)) And Len(Trim(dish_content)))=0 Then
SQL(1)="Spice = '" & spice & "'"
Else SQL(1)="Primary_Dish_Type = '" & primary & "' And Spice = '"
& spice &
"' And Type = '" & dish_content & "'"
End If

Return records when I select more than one ANY on the drop-down boxes
from this form:

<form method="post" action="OnLineShop.asp">
<table>
<td>Primary Dish Type?
<select name="primary">
<option value="" selected>ANY</option>
<option value="Starter">Starter</option>
<option value="Main Course">Main Course</option>
<option value="Accompaniments">Accompaniments</option>
</select></td>
<td>Spice?
<select name="spice">
<option value="" selected>ANY</option>
<option value="None">None</option>
<option value="Mild">Mild</option>
<option value="Medium">Medium</option>
<option value="Medium+">Medium+</option>
<option value="Medium or Hot">Medium or Hot</option>
<option value="Hot">Hot</option>
<option value="Very Hot">Very Hot</option>
</select>
<!-- <input name="txtData" type="text" id="txtData"> -->
</td>
<td>Dish Content?
<select name="dish_content">
<option value="" selected>ANY</option>
<option value="Vegetarian">Vegetarian</option>
<option value="Vegetable">Containing Vegetables</option>
<option value="Mushroom">Mushroom</option>
<option value="Prawn">Prawn</option>
<option value="King Prawn">King Prawn</option>
<option value="Chicken">Chicken</option>
<option value="Lamb">Lamb (general)</option>
<option value="Keema">Keema</option>
<option value="Mixed">Mixed</option>
<option value="Fish">Fish</option>
<option value="Dairy">Dairy</option>
<option value="Chicken Tikka">Chicken Tikka</option>
<option value="Lamb Tikka">Lamb Tikka</option>
<option value="Tandoori King Prawn">Tandoori King
Prawn</option>
</select></td>
<td><input type="submit" name="Submit" value="Go"></td>
</p>
</table>
</form>

Have I missed something from that code?