Hello folks, I wonder if you could solve this problem for me. I have some
code here that's meant to take data from drop-down boxes, each containing the
ANY value, enabling users to select non-particular clauses for database
search. I manage to get records returned with one ANY selected, but with
more than one none are returned. Here is the form code:
<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>
and here is the code page that's meant to deal with the form page:
<head>
<link rel="stylesheet" type="text/css" href="monsoon.css">
</head>
<html>
<body>
<%
Dim Connection 'Here we declare our variable that will hold our new object
Set Connection=Server.CreateObject("ADODB.Connection")
Dim sConnString 'Here we declare our variable that will hold the
connection string
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
Server.MapPath("MENU TABLE_NEW.MDB")
Connection.Open sConnString
Dim Recordset 'Here we declare our variable that will hold our new object
Set Recordset=Server.CreateObject("ADODB.Recordset")
primary=Request.Form("primary")
spice=Request.Form("spice")
dish_content=Request.Form("dish_content")
Dim SQL(3) 'Here we declare our variable that will hold the SQL statement
Rem SQL=Request.Form("txtData")
SQL(0)="Select Name,Description,Price,Availability From Menu Where "
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
SQL(2)=" Order By Name"
Rem Response.Write SQL
Rem Response.Flush
Recordset.Open SQL(0) & SQL(1) & SQL(2), Connection
Rem Recordset.Open SQL, Connection
%><br><% Response.Write("Primary Dish Type: " & primary)
%><br><% Response.Write("Spice: " & spice)
%><br><% Response.Write("Dish Content: " & dish_content)
%>
<table cols="5" border="0">
<% Do While NOT Recordset.Eof 'i.e. carry on looping through while there
are records
If Recordset("Availability")=True Then %>
<tr>
<td class="name_and_price"><% Response.Write Recordset("Name") %></td>
<td class="name_and_price"><% Response.Write Recordset("Price")
%></td>
</tr>
<tr>
<td class="description" width="50%"><% Response.Write
Recordset("Description") %></td>
</tr>
<% End If
Recordset.MoveNext 'move on to the next record
Loop %>
</table>
<%
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>
</body>
</html>
Look at the If conditioning in the code above. What am I missing? Thanks.
I am using IIS version 5.0 and am on local testing server.
