By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,053 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,053 IT Pros & Developers. It's quick & easy.

SQL to filter by entry "Status"

P: 1
Hi all,

I have the following code which displayes properties to let when searched for by "City/Town", "Suburb", "Type" and then between two "Rental" amounts. I would like to also include filtering by the "Status" field so that only approved entries will be displayed in a search result. Any help will be much appreciated.

Here is the code:
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim Recordset1__varRental1
  3. Recordset1__varRental1 = "0"
  4. If (Request.Querystring("Rental1") <> "") Then 
  5. Recordset1__varRental1 = Request.Querystring("Rental1")
  6. End If
  7. %>
  8. <%
  9. Dim Recordset1__varRental2
  10. Recordset1__varRental2 = "0"
  11. If (Request.Querystring("Rental2") <> "") Then 
  12. Recordset1__varRental2 = Request.Querystring("Rental2")
  13. End If
  14. %>
  15. <%
  16. Dim Recordset1__varSuburb
  17. Recordset1__varSuburb = "%"
  18. If (Request.Querystring("Suburb") <> "") Then 
  19. Recordset1__varSuburb = Request.Querystring("Suburb")
  20. End If
  21. %>
  22. <%
  23. Dim Recordset1__varType
  24. Recordset1__varType = "%"
  25. If (Request.Querystring("Type") <> "") Then 
  26. Recordset1__varType = Request.Querystring("Type")
  27. End If
  28. %>
  29. <%
  30. Dim Recordset1__varCityTown
  31. Recordset1__varCityTown = "%"
  32. If (Request.Querystring("CityTown") <> "") Then 
  33. Recordset1__varCityTown = Request.Querystring("CityTown")
  34. End If
  35. %>
  36.  
  37. <%
  38. Dim Recordset1
  39. Dim Recordset1_numRows
  40.  
  41. Set Recordset1 = Server.CreateObject("ADODB.Recordset")
  42. Recordset1.ActiveConnection = MM_Tolet_STRING
  43. Recordset1.Source = "SELECT UserID, Photo, Status, When, Owner, CityTown, Suburb, Type, Bedrooms, Bathrooms, Available, Ownerphone, Rental, Description FROM Table1 WHERE Rental BETWEEN " + Replace(Recordset1__varRental1, "'", "''") + " AND " + Replace(Recordset1__varRental2, "'", "''") + " AND Suburb LIKE '" + Replace(Recordset1__varSuburb, "'", "''") + "' AND Type = '" + Replace(Recordset1__varType, "'", "''") + "' AND CityTown = '" + Replace(Recordset1__varCityTown, "'", "''") + "'"
  44. Recordset1.CursorType = 0
  45. Recordset1.CursorLocation = 2
  46. Recordset1.LockType = 1
  47. Recordset1.Open()
  48.  
  49. Recordset1_numRows = 0
  50. %>
Aug 20 '07 #1
Share this Question
Share on Google+
1 Reply


jhardman
Expert 2.5K+
P: 3,405
The important part here is the line which says Recordset1.source = ...
This line is sometimes called the database query, it just says which fields you are interested in, and which records apply to the current search. Notice your line is already pretty long and complicated. We can simplify it a bit especially in the first line like this, "SELECT * FROM Table1 ..." You originally had listed every field which you wanted the db to give you, my version just says "Give me every field from the records I want."
The last part of your query is the "WHERE clause" which lists the conditions for the records you want. The syntax for this clause should be
Expand|Select|Wrap|Line Numbers
  1. "WHERE rental BETWEEN 300 AND 500 AND suburb LIKE 'sleazy' AND type = 'studio' AND status = 0"
Notice that each condition is separated by the keyword "AND", words or frases are included in single quotes, but numbers are not. I am guessing that you have a numeric status, in which case you need to add
Expand|Select|Wrap|Line Numbers
  1. "AND status = " & theStatus
to the end of the where clause. If your status is a text, then it needs to go like this:
Expand|Select|Wrap|Line Numbers
  1. "AND status = '" & theStatus & "'"
Let me know if this helps, and if so, give me a break next time I need to look for a house.

Jared
Aug 21 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.