Form to SQL statement
am 29.09.2005 13:58:03 von KiwiNETukgeocitiescomharoonnet2002Hello 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:
and here is the code page that's meant to deal with the form page:
<%
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
%>
<% Response.Write("Primary Dish Type: " & primary)
%>
<% Response.Write("Spice: " & spice)
%>
<% Response.Write("Dish Content: " & dish_content)
%>
| <% Response.Write Recordset("Name") %> | <% Response.Write Recordset("Price") %> |
| <% Response.Write Recordset("Description") %> |
<%
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>
Look at the If conditioning in the code above. What am I missing? Thanks.