title: Classic ASP - Converting a script to use parameters causing issues
body: <p>I have a very large search script which was not parameterized until now (although semi-protected by a regular expression function and wasn't open to public), but having just converted it, I've run in to some issues with the way I used to get the number of records.</p>
  
  <p>This is an example of my script, before being parameterized:</p>
  
  <pre><code>selectClause = "SELECT column FROM table "
  whereClause = "WHERE something = 'something' "
  
  If something = "someCondition" Then
  whereClause = whereClause &amp; "AND something = '"&amp;input&amp;"' "
  End If
  
  If somethingElse = "someOtherCondition" Then
  whereClause = whereClause &amp; "AND somethingElse = '"&amp;input&amp;"' "
  End If
  
  ' ... plus loads more conditional statements...
  
  orderClause = "ORDER BY something DESC "
  
  ' get the total number of records, which is now what I am stuck on ////
  
  SQL = " SELECT COUNT(*) AS total FROM ("&amp;selectClause &amp; whereClause&amp;") AS Q1; "
  Set rs = Conn.Execute(SQL)
  If NOT rs.EOF Then
  Session("record-count") = rs.Fields("totalRecords")
  End If
  rs.Close
  
  ' calculate paging and limitClause
  
  ... some code
  limitClause = limit 0, 20 ' example
  
  ' build query
  
  SQL = selectClause &amp; whereClause &amp; orderClause &amp; limitClause
  
  Conn.Execute(SQL)
  </code></pre>
  
  <p>Apart from being open to an attack, this worked fine. So I started to convert it, using parameters, but now I can't work out how to get the total count without having to write the whole search query again, which is 30 times the length of my example (above). Hopefully my code below will explain my situation better.</p>
  
  <pre><code>selectClause = "SELECT column FROM table "
  whereClause = "WHERE something = 'something' "
  
  If something = "someCondition" Then
  whereClause = whereClause &amp; "AND something = ? "
  Set newParameter = cmdConn.CreateParameter("@blah1", ad_varChar, ad_ParamInput, Len(input), Replace(input,"'","\'"))
  cmdConn.Parameters.Append newParameter
  End If
  
  If somethingElse = "someOtherCondition" Then
  whereClause = whereClause &amp; "AND somethingElse = ? "
  Set newParameter = cmdConn.CreateParameter("@blah2", ad_varChar, ad_ParamInput, Len(input), Replace(input,"'","\'"))
  cmdConn.Parameters.Append newParameter
  End If
  
  orderClause = "ORDER BY something DESC "
  
  ' but now the whereClause contains ?'s which cannot be used here, which has confused me on how I should be getting the new count.
  ' /////////////////////////////
  SQL = " SELECT COUNT(*) AS total FROM ("&amp;selectClause &amp; whereClause&amp;") AS Q1; "
  Set rs = Conn.Execute(SQL)
  If NOT rs.EOF Then
  Session("record-count") = rs.Fields("totalRecords")
  End If
  rs.Close
  ' /////////////////////////////
  
  ' calculate paging and limitClause
  
  ... some code
  limitClause = "limit x, y;"
  
  ' build query
  
  SQL = selectClause &amp; whereClause &amp; orderClause &amp; limitClause
  
  cmdConn.CommandText = SQL
  </code></pre>
  
  <p>As you can see, I cannot use the count method I used before because the where clause contains ?'s which means that particular count query needs its own parameter collection. Can anybody suggest how I can now get my count without having to duplicate this huge query?</p>
  
  <p>At a guess, I would probably create another command object <code>cmdConn2</code> and where I've set a  parameter, set another one the same but as <code>cmdConn2</code>, which could then be used for the count query, but my guesses aren't always the best method.</p>
  
  <p>Any help always greatly appreciated.</p>