Hi,
i have a script that aims to do search of data.
I select some data , define range of prices (for min price and max price) and then i get the following error :
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'city= '1' and area ='North' and property_type = 'Any' AND Price BETWEEN AND'.
can you look at the ASP code and tell me what i have to fix ?
<% If Request("Submit") = "Submit" then


Response.Expires = -1000

Dim oConn
Dim oRS
Dim sSQL
Dim sColor

Response.Write("<font size=2 face=arial>")
Response.Write("test #1<br>")
Response.Write("hotel search<br><br>")

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\single24\sites\Single24\polishbiz\Database\real_estate.mdb")

MinPrice = Request.Form("min_Price")
MaxPrice = Request.Form("max_Price")

sSQL = "SELECT city, area, property_type, min_price, max_price FROM real_estate where city= '" & Request("city") &"' and area ='"& Request("area") &"' and property_type = '" & Request("property_type") &"'"

If Len(MinPrice) > 0 And Len(MaxPrice) = 0 Then



sSQL = sSQL & " AND Price >= " & MinPrice
Else
sSQL = sSQL & " AND Price BETWEEN " & MinPrice & " AND " & MaxPrice
End If
Set oRS = oConn.Execute(sSQL)

Response.Write("<table border=1 cellpadding=1 cellspacing=1 style='font-family:arial; font-size:10pt;'>")
Response.Write("<tr bgcolor=black style='color:white;'><td>city</td>")
Response.Write("<td>area</td>")
Response.Write("<td>property_type</td>")
Response.Write("<td align=right>min_price</td>")
Response.Write("<td>max_price</td></tr>")

sColor = "white"

Do While NOT oRS.EOF

If sColor = "silver" Then
sColor = "white"
Else
sColor = "silver"
End If

Response.Write("<tr bgcolor='" & sColor & "'>")
Response.Write("<td>" & oRS("city").Value & "</td>")
Response.Write("<td>" & oRS("area").Value & "</td>")
Response.Write("<td>" & oRS("property_type").Value & "</td>")
Response.Write("<td align=right>$" & oRS("min_price").Value & "</td>")
Response.Write("<td align=right>" & oRS("max_price").Value & "</td></tr>")

oRS.MoveNext

Loop

Response.Write("</table><br><br>")

oConn.Close
Set oRS = Nothing
Set oConn = Nothing


else %>
<form name="form1" method="post" action="">
<select name="city" id="city">
<option value="1">Paris</option>
<option value="2">nice</option>
<option value="3">marseille</option>

</select>
<select name="Area" id="Area">
<option value="North">North</option>
<option value="South">South</option>
<option value="East">East</option>
<option value="West">West</option>
<option value="Central">Central</option>
</select>
<select name="property_type" id="property_type">
<option value="Any">Any</option>
<option value="Single family home">Single family home</option>
<option value="Townhome or condo">Townhome or condo</option>
<option value="Investment property">Investment property</option>
<option value="Vacant Land">Vacant Land</option>
<option value="Mobile Home">Mobile Home</option>
<option value="Farm">Farm</option>
</select>
<SELECT NAME="price_min" id="price_min"><OPTION VALUE="">$0
<OPTION VALUE=10000>$10,000
<OPTION VALUE=20000>$20,000
</SELECT>
<SELECT NAME="price_max" id="price_max">
<OPTION VALUE=900000>$900,000
<OPTION VALUE=1000000>$1,000,000
</SELECT>
<input type="submit" name="Submit" value="Submit">
</form>
<% End if %>

Hi there,

Well you have made mistake in the assigning the value in variable, there is not any field in form named min_Price, max_Price which you are trying to assign to Dim MinPrice and Dim MaxPrice


Here you wrote in ASP:



Just change these lines with



Here is the html code that you have wrote for form



I hope this will helpful for you,

Please Let me Know if its works

Thanks

Best regards,
Rahul Dev