Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jul 2006
    Location
    UK
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Select statement in asp help needed

    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

  • #2
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If the prices are numeric fields then you don't need the quotes around them. Also remove the Wild Card character.

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

  • #3
    New to the CF scene
    Join Date
    Jul 2006
    Location
    UK
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    As suggested here, if they are numeric, don't use quotes. Like here:

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

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

  • #5
    New to the CF scene
    Join Date
    Jul 2006
    Location
    UK
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Brandoe85
    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.

  • #6
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Try casting them:
    Code:
    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;

  • #7
    New to the CF scene
    Join Date
    Jul 2006
    Location
    UK
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Brandoe85
    Try casting them:
    Code:
    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?

  • #8
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    cDbl

    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.

  • #9
    New to the CF scene
    Join Date
    Jul 2006
    Location
    UK
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spudhead
    cDbl

    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •