Hi there,
I have a single Classic ASP page that I wish to display a search form and the associated results.
When a user first comes to this page, I want to display a search form and the 10 latest properties. If a user decides to use the search form to retrieve more relevant properties, then i want the default 10 latest properties to be replaced with the users' paged search results.
So far my code looks like this;
<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>
But when I view the page, I get the following error;
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/welcome/gist188770.asp, line 98
Line 98 is as follows;
if myRecordSet2.BOF then
I wondered if anyone might be able to help me solve this issue? Thank you for any help offered.
Neil.