am 29.09.2005 14:00:01 von KiwiNETukgeocitiescomharoonnet2002
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:
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")
<% Do While NOT Recordset.Eof 'i.e. carry on looping through while there
are records
If Recordset("Availability")=True Then %>
<% Response.Write Recordset("Name") %>
<% Response.Write Recordset("Price")
%>
<% Response.Write
Recordset("Description") %>
<% End If
Recordset.MoveNext 'move on to the next record
Loop %>
<%
Recordset.Close
Set Recordset=Nothing
Connection.Close
Set Connection=Nothing
%>
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.
RE: Form data to SQL statement
am 29.09.2005 15:34:03 von KiwiNETukgeocitiescomharoonnet2002
Here's what keeps being passed to the OnLineShop for some weird reason:
Select Name,Description,Price,Availability From Menu Where Primary_Dish_Type
= 'Accompaniments' And Type = '' Order By Name
when I just select 'Accompaniments' on the form and leave the other two as
'ANY'. Please tell me where I have made the typo, in the code from my last
post in the other thread. Thanks.
Re: Form data to SQL statement
am 29.09.2005 16:03:28 von McKirahan
"KiwiNET uk.geocities.com/haroonnet2002/" wrote in
message news:3DCB7177-D749-477B-8859-EF1214D26093@microsoft.com...
> Here's what keeps being passed to the OnLineShop for some weird reason:
>
> Select Name,Description,Price,Availability From Menu Where
Primary_Dish_Type
> = 'Accompaniments' And Type = '' Order By Name
>
> when I just select 'Accompaniments' on the form and leave the other two as
> 'ANY'. Please tell me where I have made the typo, in the code from my
last
> post in the other thread. Thanks.
1) "Name" and "Type" may be reserved words and should be enclosed in
brackets.
2) "Type" is being tested for spaces -- is that what you want?
3) Try the following; watch for word-wrap.
Dim NAM(2)
NAM(0) = "Primary_Dish_Type"
NAM(1) = "Spice"
NAM(2) = "Type"
Dim VAL(2)
VAL(0) = Request.Form("primary")
VAL(1) = Request.Form("spice")
VAL(2) = Request.Form("dish_content")
Dim SQL(2)
SQL(0)="SELECT [Name],Description,Price,Availability FROM Menu WHERE "
Dim i
For i = 0 To UBound(NAM)
If Len(Trim(VAL(i))) <> 0 Then
If SQL(1) <> "" Then SQL(1) = SQL(1) & " AND "
SQL(1) = "[" & NAM(i) & "] = '" & VAL(i) & "'"
End If
Next
SQL(2)=" Order By Name"
This includes in the WHERE test those fields that have a value.
Note 1: I "Dim" all my variables as I always use "Option Explicit".
Note 2: I capitalized the SQL words SELECT, FROM, WHERE, AND.
The above is untested; let me know if there's a problem.
Re: Form data to SQL statement
am 29.09.2005 23:59:03 von SteveB
Perhaps this may hopefully give you some ideas:
Classic ASP Design Tips - Search Criteria on Multiple Fields
http://www.bullschmidt.com/devtip-searchcriteria.asp
Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Classic ASP Design Tips, ASP Web Database Sample
*** Sent via Developersdex http://www.developersdex.com ***
Re: Form data to SQL statement
am 30.09.2005 11:52:01 von KiwiNETukgeocitiescomharoonnet2002
McKirahan, I'll get back to you in a bit...
> The above is untested; let me know if there's a problem.
>
>
>
Re: Form data to SQL statement
am 30.09.2005 11:55:03 von KiwiNETukgeocitiescomharoonnet2002
It's about checking for non-null data really - the part where it checks for
null data, when one ANY is selected works fine. Where more than one ANY is
selected, I don't know where that rogue quotation mark comes from in the SQL
statement being passed to the OnLineShop.asp file. I'll try what you
suggested, thanks...
"Bullschmidt" wrote:
> Perhaps this may hopefully give you some ideas:
>
> Classic ASP Design Tips - Search Criteria on Multiple Fields
> http://www.bullschmidt.com/devtip-searchcriteria.asp
>
> Best regards,
> J. Paul Schmidt, Freelance Web and Database Developer
> http://www.Bullschmidt.com
> Classic ASP Design Tips, ASP Web Database Sample
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
RE: Form data to SQL statement
am 02.10.2005 22:17:02 von KiwiNETukgeocitiescomharoonnet2002
OK folks, thanks for all the help. I have now solved the problem. 'If'
conditioning didn't work for me, but I used Functions embedded within SQL
statements:
SQL="Select Name,Description,Price,Availability From Menu " &
PrimaryNotNull(primary) & SecondaryNotNull(secondary) & SpiceNotNull(spice) &
DishContentNotNull(dish_content)
Function PrimaryNotNull(primary)
If Len(Trim(primary))=0 Then
appropriate=""
Else
appropriate="Where Primary_Dish_Type = '" & primary & "'"
End If
PrimaryNotNull = appropriate
End Function
Function SpiceNotNull(spice)
If Len(Trim(spice))=0 Then
appropriate=""
Else
appropriate=" And Spice = '" & spice & "'"
End If
SpiceNotNull = appropriate
End Function
Function DishContentNotNull(dish_content)
If Len(Trim(dish_content))=0 Then
appropriate=""
Else
appropriate=" And Content = '" & dish_content & "'"
End If
DishContentNotNull = appropriate
End Function
The correct statements are being passed to the SQL now. The records are
being returned without error.