title: ASP Search and Results in a single page
body: <p>I have a single Classic ASP page that I wish to display a search form and the associated results.</p>
  
  <p>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.</p>
  
  <p>So far my code looks like this;</p>
  
  <pre><code>&lt;head&gt;
      &lt;title&gt;Search&lt;/title&gt;
  &lt;/head&gt;
  &lt;body&gt;
  
  &lt;div class="search"&gt;
  &lt;h3&gt;Search form&lt;/h3&gt;
  &lt;form id="form1" name="form1" method="post" action="gist188770.asp"&gt;
  &lt;label&gt;Street: &lt;input type="text" name="searchStreet" value="&lt;%=Server.HtmlEncode(Request("searchStreet") &amp; "") %&gt;" /&gt;&lt;/label&gt;
  &lt;label&gt;Town: &lt;input type="text" name="searchTown" value="&lt;%=Server.HtmlEncode(Request("searchTown") &amp; "") %&gt;" /&gt;&lt;/label&gt;
  &lt;input type="submit" name="Submit" value="Submit" /&gt;
  &lt;/form&gt;
  &lt;/div&gt;
  
  &lt;%
  
  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&amp; "FROM ("
  myRecordSet.Source = myRecordSet.Source&amp; "SELECT id"
  myRecordSet.Source = myRecordSet.Source&amp; "FROM ("
  myRecordSet.Source = myRecordSet.Source&amp; "SELECT id"
  myRecordSet.Source = myRecordSet.Source&amp; "FROM VWTenantPropertiesResults"
  myRecordSet.Source = myRecordSet.Source&amp; "WHERE ContentStreet LIKE '%" &amp; "@ContentStreet" &amp; "%'"
  myRecordSet.Source = myRecordSet.Source&amp; "UNION ALL"
  myRecordSet.Source = myRecordSet.Source&amp; "SELECT id"
  myRecordSet.Source = myRecordSet.Source&amp; "FROM VWTenantPropertiesResults"
  myRecordSet.Source = myRecordSet.Source&amp; "WHERE ContentTown LIKE '%" &amp; "@ContentTown" &amp; "%'"
  myRecordSet.Source = myRecordSet.Source&amp; "UNION ALL"
  myRecordSet.Source = myRecordSet.Source&amp; "SELECT id"
  myRecordSet.Source = myRecordSet.Source&amp; "FROM VWTenantPropertiesResults"
  myRecordSet.Source = myRecordSet.Source&amp; "WHERE ContentPostCode LIKE '%" &amp; "@ContentPostcode" &amp; "%'"
  myRecordSet.Source = myRecordSet.Source&amp; ") qi"
  myRecordSet.Source = myRecordSet.Source&amp; "GROUP BY"
  myRecordSet.Source = myRecordSet.Source&amp; "id"
  myRecordSet.Source = myRecordSet.Source&amp; "HAVING COUNT(*) &gt;= 2"
  myRecordSet.Source = myRecordSet.Source&amp; ") q"
  myRecordSet.Source = myRecordSet.Source&amp; "JOIN VWTenantPropertiesResults r"
  myRecordSet.Source = myRecordSet.Source&amp; "ON r.id = q.id"
  myRecordSet.Source = myRecordSet.Source&amp; "WHERE ContentBedrooms BETWEEN 1 AND 4"
  myRecordSet.Source = myRecordSet.Source&amp; "AND ContentPrice BETWEEN 50 AND 500"
  myRecordSet.Source = myRecordSet.Source&amp; "ORDER BY"
  myRecordSet.Source = myRecordSet.Source&amp; "ContentPrice"
  
  'display the results
  if myRecordSet.BOF then
  response.write("Latest properties:&lt;br&gt;")
  do until myRecordSet.EOF
  %&gt;
  &lt;div class='result'&gt;")
    &lt;dl&gt;&lt;%=myRecordSet("ContentTitle")%&gt;&lt;/dl&gt;
    &lt;dt&gt;&lt;%=myRecordSet("ContentStreet")%&gt;&lt;/dt&gt;
    &lt;dt&gt;&lt;%=myRecordSet("ContentTown")%&gt;&lt;/dt&gt;
    &lt;dt&gt;&lt;%=myRecordSet("ContentPostcode")%&gt;&lt;/dt&gt;
  &lt;/div&gt;&lt;%
  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
      %&gt;
      &lt;div class='result'&gt;")
        &lt;dl&gt;&lt;%=myRecordSet2("ContentTitle")%&gt;&lt;/dl&gt;
        &lt;dt&gt;&lt;%=myRecordSet2("ContentStreet")%&gt;&lt;/dt&gt;
        &lt;dt&gt;&lt;%=myRecordSet2("ContentTown")%&gt;&lt;/dt&gt;
        &lt;dt&gt;&lt;%=myRecordSet2("ContentPostcode")%&gt;&lt;/dt&gt;
      &lt;/div&gt;&lt;%
      myRecordSet2.MoveNext
      loop
  end if
  
  end if
  %&gt;
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>But when I view the page, I get the following error;</p>
  
  <pre><code>ADODB.Recordset error '800a0e78'
  
  Operation is not allowed when the object is closed.
  
  /welcome/gist188770.asp, line 98 
  </code></pre>
  
  <p>Line 98 is as follows;</p>
  
  <pre><code>if myRecordSet2.BOF then
  </code></pre>
  
  <p>I wondered if anyone might be able to help me solve this issue? Thank you for any help offered.</p>
  
  <p>Neil.</p>