sql returning no records when search field left blank
-
building a real-estate search page and one field is "schools" when the scools field is left blank my query returns no records. I want it to return all the records, as if the field did not exist. here is my code:
SELECT * FROM dbo.AGSVC_Idx_Res
WHERE neighborhood = 'MM_neighborhood' AND city = 'MM_city' AND PRICE BETWEEN 'MM_price_start' AND 'MM_price_finish' AND BEDROOMS >= 'MM_bed_start' AND HIGH_SCHOOL LIKE 'MM_school'
ORDER BY price DESC
-
Try this:
SELECT * FROM dbo.AGSVC_Idx_Res
WHERE neighborhood = 'MM_neighborhood' AND city = 'MM_city' AND PRICE BETWEEN 'MM_price_start' AND 'MM_price_finish' AND BEDROOMS >= 'MM_bed_start' AND (HIGH_SCHOOL LIKE 'MM_school' or high_school is null)
ORDER BY price DESC
-
Phil's solution will return you records when the school matches or the school is null in the database, but I think you want a solution that will return records when no school is provided as part of the selection criteria.
Try this:
SELECT * FROM dbo.AGSVC_Idx_Res
WHERE neighborhood = 'MM_neighborhood' AND city = 'MM_city' AND PRICE BETWEEN 'MM_price_start' AND 'MM_price_finish' AND BEDROOMS >= 'MM_bed_start' AND (HIGH_SCHOOL LIKE 'MM_school' + '%') ORDER BY price DESC
This should give you any school if MM_school is blank.
Kemp
-
Thanks Kemp ... yet another requirement that I didn't quite understand
Here's another version of your query that does not require wildcards:
SELECT * FROM dbo.AGSVC_Idx_Res
WHERE
neighborhood = 'MM_neighborhood' AND
city = 'MM_city' AND
PRICE BETWEEN 'MM_price_start' AND 'MM_price_finish' AND
BEDROOMS >= 'MM_bed_start' AND
(HIGH_SCHOOL = isnull(nullif('mm_school',''),high_school))
ORDER BY price DESC
-
I tried all these and none worked. If it helps, im using "get" on the form and then request.querystring in the sql. I cut and pasted each of the above suggestions and all return no records when the school parameter is blank. http://www.thebrickhousetestsite.com/search_residential.asp The code is simple:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/rmls.asp" -->
<%
Dim Recordset1__MM_neighborhood
Recordset1__MM_neighborhood = "none"
If (Request.QueryString("neighborhood") <> "") Then
Recordset1__MM_neighborhood = Request.QueryString("neighborhood")
End If
%>
<%
Dim Recordset1__MM_city
Recordset1__MM_city = "none"
If (Request.QueryString("city") <> "") Then
Recordset1__MM_city = Request.QueryString("city")
End If
%>
<%
Dim Recordset1__MM_price_start
Recordset1__MM_price_start = "0"
If (Request.QueryString("price_start") <> "") Then
Recordset1__MM_price_start = Request.QueryString("price_start")
End If
%>
<%
Dim Recordset1__MM_price_finish
Recordset1__MM_price_finish = "0"
If (Request.QueryString("price_finish") <> "") Then
Recordset1__MM_price_finish = Request.QueryString("price_finish")
End If
%>
<%
Dim Recordset1__MM_bed_start
Recordset1__MM_bed_start = "10"
If (Request.QueryString("bed_start") <> "") Then
Recordset1__MM_bed_start = Request.QueryString("bed_start")
End If
%>
<%
Dim Recordset1__MM_school
Recordset1__MM_school = "none"
If (Request.QueryString("schools") <> "") Then
Recordset1__MM_school = Request.QueryString("schools")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_rmls_STRING
Recordset1.Source = "SELECT * FROM dbo.AGSVC_Idx_Res WHERE neighborhood = '" + Replace(Recordset1__MM_neighborhood, "'", "''") + "' AND city = '" + Replace(Recordset1__MM_city, "'", "''") + "' AND PRICE BETWEEN '" + Replace(Recordset1__MM_price_start, "'", "''") + "' AND '" + Replace(Recordset1__MM_price_finish, "'", "''") + "' AND BEDROOMS >= '" + Replace(Recordset1__MM_bed_start, "'", "''") + "' AND (HIGH_SCHOOL = isnull(nullif('" + Replace(Recordset1__MM_school, "'", "''") + "',''),high_school)) ORDER BY price DESC"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = 25
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table width="980" border="0" cellspacing="3" cellpadding="0">
<tr>
<td width="150"> </td>
<td width="142">id</td>
<td width="110">area</td>
<td width="128">price</td>
<td width="103">city</td>
<td width="126">school</td>
<td width="423">Neighborhood</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr valign="top">
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr valign="top">
<td><font size="2"><img src="<A href='file://///Brickhouse-web/rmls/property_fotos/-1.jpg'>file://///Brickhouse-web/rmls/property_fotos/<%=(Recordset1.Fields.Item("LISTING_ID").Value)%>-1.jpg" width="150"><br>
</font></td>
<td><font size="2"><a href="property_detail.asp?listing_id=<%=(Recordset1.Fields.Item("LISTING_ID").Value)%>"><%=(Recordset1.Fields.Item("LISTING_ID").Value)%></a></font></td>
<td><font size="2"><%=(Recordset1.Fields.Item("AREA").Value)%></font></td>
<td><font size="2"><%=(Recordset1.Fields.Item("PRICE").Value)%></font></td>
<td><font size="2"><%=(Recordset1.Fields.Item("CITY").Value)%></font></td>
<td><font size="2"><%=(Recordset1.Fields.Item("HIGH_SCHOOL").Value)%></font></td>
<td><font size="2"><%=(Recordset1.Fields.Item("NEIGHBORHOOD").Value)%></font></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
<p> </p>
<p><font color="#990033">gjkhk</font></p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
-
I like Kemps answer (reads easier). Is your problem not maybe somewhere else, like the city field is blank? Try excluding portions of your query to prove it.
5ilverFox
Consulting DBA / Developer
South Africa -
http://www.thebrickhousetestsite.com/search_1.asp
I simplified the code dramatically and it still does not work, it returns no records when the field is left blank
-
Hi Travis, this must be turning into a real pain in the you-know-what
Just had a quick read through your code and noticed this section:Recordset1__MM_school = "none"
If (Request.QueryString("schools") "") Then
Recordset1__MM_school = Request.QueryString("schools")
End If
Doesn't this mean that 'Recordset1__MM_school' will be set to "none" if 'schools' is blank, and therefore that you will be searching for a school called "none" if the schools field is left blank? Maybe changing the first line to
Recordset1__MM_school = ""
would fix things up?
Regards
Phil
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply