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

G

Guest

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?
 
C

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?S2l3aU5FVCB1ay5nZW9jaXRpZXMuY29tL2hhcm9vbm5ldDIwMDIv?=
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top