sql returning no records when search field left blank

Question

Post reply

sql returning no records when search field left blank

travis vanstaaveren

Valued Member

Points: 55
More actions
December 22, 2004 at 2:52 pm

#162090

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Kemp Davis Ten Centuries Points: 1067 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

travis vanstaaveren Valued Member Points: 55 More actions · Answer 4

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"%>

<%

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>

</head>

<body>

<tr>

<td width="128">price</td>

<td width="126">school</td>

<td width="423">Neighborhood</td>

</tr>

<%

While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))

%>

</tr>

<td><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">

</td>

</tr>

<%

Repeat1__index=Repeat1__index+1

Repeat1__numRows=Repeat1__numRows-1

Recordset1.MoveNext()

Wend

%>

</table>

gjkhk

</body>

</html>

<%

Recordset1.Close()

Set Recordset1 = Nothing

%>

Japie Botma SSCrazy Points: 2990 More actions · Answer 5

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

travis vanstaaveren Valued Member Points: 55 More actions · Answer 6

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 7

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin