SQL Select statement in asp help needed

cindy_cutz

07-20-2006, 01:49 PM

Hello,i was wondering if someone could help.
I have a search page on my website with 3 drop down boxes:
list_beds
min_p and
max_p

I have a database behind it with a table called Houses and field names in the table called Beds and Price.
So i want it to bring back results by number of beds and price range.
i got this but it doesnt work:
sql_srch = "SELECT * FROM Houses WHERE Beds like '%"& request("list_beds")&"%' AND Price BETWEEN '%"&request("min_p")&"%' AND '%"&request("max_p")&"%'"

i get this error: Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

I'd be so greatfull if anyone could help.
Thank you
Cindy :)

degsy

07-20-2006, 03:03 PM

If the prices are numeric fields then you don't need the quotes around them. Also remove the Wild Card character.

sql_srch = "SELECT * FROM Houses WHERE Beds like '%" & request("list_beds") & "%' AND Price BETWEEN " & request("min_p") & " AND " & request("max_p")

cindy_cutz

07-20-2006, 03:29 PM

ok thank you for your help i tried changing it to this:

sql_srch = "SELECT * FROM Houses WHERE Beds like '"& request(list_beds)&"' AND Price BETWEEN '"&request(min_p)&"' AND '"&request(max_p)&"'"

and now i get:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

Brandoe85

07-21-2006, 02:37 AM

As suggested here, if they are numeric, don't use quotes. Like here:

If the prices are numeric fields then you don't need the quotes around them. Also remove the Wild Card character.

sql_srch = "SELECT * FROM Houses WHERE Beds like '%" & request("list_beds") & "%' AND Price BETWEEN " & request("min_p") & " AND " & request("max_p")

Good luck;

cindy_cutz

07-21-2006, 09:28 AM

As suggested here, if they are numeric, don't use quotes. Like here:

Good luck;

Hey thanks for help but i still get the same error.

Brandoe85

07-21-2006, 03:43 PM

Try casting them:

sql_srch = "SELECT * FROM Houses WHERE Beds like '%" & request("list_beds") & "%' AND Price BETWEEN " & CDbl(request("min_p")) & " AND " & CDbl(request("max_p"))

Good luck;

cindy_cutz

07-24-2006, 01:21 PM

Try casting them:

sql_srch = "SELECT * FROM Houses WHERE Beds like '%" & request("list_beds") & "%' AND Price BETWEEN " & CDbl(request("min_p")) & " AND " & CDbl(request("max_p"))

Good luck;

Hm, i think that works but not sure cos i get an error further down my code. what does CDbl do?

Spudhead

07-24-2006, 01:31 PM

cDbl (http://www.devguru.com/technologies/vbscript/13905.asp)

It can often help if you response.write your SQL string instead of firing it at the database. At least you can then see exactly what you're sending through - the problem might be with the data rather with anything inherent in your syntax.

cindy_cutz

07-24-2006, 02:37 PM