...

View Full Version : 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
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.


Hey thanks so much you guys. it works now! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum