Skip to content

Instantly share code, notes, and snippets.

@neilbradley

neilbradley/search.asp

Created September 17, 2009 22:49
Show Gist options
  • Save neilbradley/188770 to your computer and use it in GitHub Desktop.
Save neilbradley/188770 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
search.asp
<!--#include file="../includes/site-variables.asp" -->
<!--#include file="../Connections/dbconn.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<title>Search</title>
</head>
<body>
<div class="search">
<h3>Search form</h3>
<form id="form1" name="form1" method="post" action="gist188770.asp">
<label>Street: <input type="text" name="searchStreet" value="<%=Server.HtmlEncode(Request("searchStreet") & "") %>" /></label>
<label>Town: <input type="text" name="searchTown" value="<%=Server.HtmlEncode(Request("searchTown") & "") %>" /></label>
<input type="submit" name="Submit" value="Submit" />
</form>
</div>
<%
if (Request.ServerVariables("REQUEST_METHOD") = "POST") then
'arrived via post get form values and do search
Dim myRecordSet
Dim myRecordSet_numRows
Set myRecordSet = Server.CreateObject("ADODB.Recordset")
myRecordSet.ActiveConnection = MM_dbconn_STRING
'collect the form input
set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,100)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@ContentStreet") = Request.QueryString("searchStreet")
set objDBParam = Nothing
set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,100)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@ContentTown") = Request.QueryString("searchTown")
set objDBParam = Nothing
set objDBParam = objDBCommand.CreateParameter("@ContentStreet",200,1,20)
objDBCommand.Parameters.Append objDBParam
objDBCommand.Parameters("@ContentPostcode") = Request.QueryString("searchPostcode")
set objDBParam = Nothing
'check for a match
myRecordSet.Source = "SELECT *"
myRecordSet.Source = myRecordSet.Source& "FROM ("
myRecordSet.Source = myRecordSet.Source& "SELECT id"
myRecordSet.Source = myRecordSet.Source& "FROM ("
myRecordSet.Source = myRecordSet.Source& "SELECT id"
myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults"
myRecordSet.Source = myRecordSet.Source& "WHERE ContentStreet LIKE '%" & "@ContentStreet" & "%'"
myRecordSet.Source = myRecordSet.Source& "UNION ALL"
myRecordSet.Source = myRecordSet.Source& "SELECT id"
myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults"
myRecordSet.Source = myRecordSet.Source& "WHERE ContentTown LIKE '%" & "@ContentTown" & "%'"
myRecordSet.Source = myRecordSet.Source& "UNION ALL"
myRecordSet.Source = myRecordSet.Source& "SELECT id"
myRecordSet.Source = myRecordSet.Source& "FROM VWTenantPropertiesResults"
myRecordSet.Source = myRecordSet.Source& "WHERE ContentPostCode LIKE '%" & "@ContentPostcode" & "%'"
myRecordSet.Source = myRecordSet.Source& ") qi"
myRecordSet.Source = myRecordSet.Source& "GROUP BY"
myRecordSet.Source = myRecordSet.Source& "id"
myRecordSet.Source = myRecordSet.Source& "HAVING COUNT(*) >= 2"
myRecordSet.Source = myRecordSet.Source& ") q"
myRecordSet.Source = myRecordSet.Source& "JOIN VWTenantPropertiesResults r"
myRecordSet.Source = myRecordSet.Source& "ON r.id = q.id"
myRecordSet.Source = myRecordSet.Source& "WHERE ContentBedrooms BETWEEN 1 AND 4"
myRecordSet.Source = myRecordSet.Source& "AND ContentPrice BETWEEN 50 AND 500"
myRecordSet.Source = myRecordSet.Source& "ORDER BY"
myRecordSet.Source = myRecordSet.Source& "ContentPrice"
'display the results
if myRecordSet.BOF then
response.write("Latest properties:<br>")
do until myRecordSet.EOF
%>
<div class='result'>")
<dl><%=myRecordSet("ContentTitle")%></dl>
<dt><%=myRecordSet("ContentStreet")%></dt>
<dt><%=myRecordSet("ContentTown")%></dt>
<dt><%=myRecordSet("ContentPostcode")%></dt>
</div><%
myRecordSet.MoveNext
loop
end if
else
'arrived via get show last 10 results
Dim myRecordSet2
Dim myRecordSet2_numRows
Set myRecordSet2 = Server.CreateObject("ADODB.Recordset")
myRecordSet2.ActiveConnection = MM_dbconn_STRING
myRecordSet2.Source = "SELECT TOP 10 FROM VWTenantPropertiesResults ORDER BY ContentPrice"
'display the results
if myRecordSet2.BOF then
do until myRecordSet2.EOF
%>
<div class='result'>")
<dl><%=myRecordSet2("ContentTitle")%></dl>
<dt><%=myRecordSet2("ContentStreet")%></dt>
<dt><%=myRecordSet2("ContentTown")%></dt>
<dt><%=myRecordSet2("ContentPostcode")%></dt>
</div><%
myRecordSet2.MoveNext
loop
end if
end if
%>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment