Why does RecordCount return as -1?

Recordcount is not supported with the default forward-only cursor. If you open a recordset in the following manner, you will have access to recordcount: 
 

<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      set rs = CreateObject("ADODB.Recordset")      sql = "SELECT columns FROM table WHERE [...]"      rs.open sql,conn,1,1      if not rs.eof then          nr = rs.recordcount          response.write "There were " & nr & " matches."          ' ... process real results here ...      else          response.write "No matches."      end if      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 

---

 
However, it is much more efficient to use GetRows() for your recordset (and recordcount is easily extracted): 
 

<%      Dim rsArray      Set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      sql = "SELECT columns FROM table WHERE [...]"      Set rs = conn.execute(sql)      if not rs.eof then          rsArray = rs.GetRows()          nr = UBound(rsArray, 2) + 1          response.write "There were " & nr & " matches."          ' ... process real results here ...      end if      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 

---

 
Another approach, using SQL Server, would be: 
 

CREATE PROCEDURE dbo.getRowsAndCount_v1  AS  BEGIN      SET NOCOUNT ON        -- note that the WHERE clause      -- should be identical here:        SELECT COUNT(*) FROM table WHERE [...]      SELECT columns FROM table WHERE [...]  END  GO

 
Then from ASP: 
 

<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      sql = "EXEC dbo.getRowsAndCount_v1"      Set rs = conn.execute(sql)      response.write "There were " & rs(0) & " matches."      Set rs = rs.NextRecordset()      ' ... process real results here ...      rs.close: set rs = nothing      conn.close: set conn = nothing  %>

 

---

 
And yet another way (to avoid two queries): 
 

CREATE PROCEDURE dbo.getRowsAndCount_v2  AS  BEGIN      SET NOCOUNT ON      SELECT columns FROM table WHERE [...]      SELECT rc = @@ROWCOUNT  END  GO

 
Then from ASP: 
 

<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"      sql = "EXEC dbo.getRowsAndCount_v2"      Set rs = conn.execute(sql)      rsArray = rs.getRows()      Set rs = rs.NextRecordset()      response.write "There were " & rs(0) & " matches."      ' ... process rsArray here ...      rs.close: set rs = nothing      conn.close: set conn = nothing  %>