VbScript ADODB.RecordSet RecordCount returns -1

Question

My question today is a rather simple one. What I have is a VB Module that contains the code to return me an ADODB.RecordSet object with records fetched from a SQL Query that has been executed. It works like this:

sSql_SerCheck = "SELECT DISTINCT Serial FROM dbo.WipReservedSerial WHERE  Serial LIKE '" & serialTempSearch
sSql_SerCheck = sSql_SerCheck & "' ORDER BY Serial DESC "
dbGetRecordSet(sSql_SerCheck)

Then the results sit in object rs that is accessed like the following

 temp = rs(0) 'For the value at the first column for the first record
 rs.MoveNext  'This moves to the next record in the record set

Now what I am trying to do here is to the number of records contained within this recordset object. Now I did some research on the class and found there is a RecordCount att.

So what I want to do is simple:

if( rs.RecordCount > 0) then
    serCheck1 = rs(0)
    MsgBox serCheck1
end if

The problem is my RecordCount returns -1. I found this article http://www.w3schools.com/asp/prop_rs_recordcount.asp that states that record count will return -1 for the following:

Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.

Note: The Recordset object must be open when calling this property. If this property is not supported it will return -1.

How do I get this object to return the correct number of records??

The code for the VB Module is added below:

Public cn, rs


'Specify pSQL as SQL Statement
Function dbGetRecordset(sSql)
dbCloseConnection()

Set cn = CreateObject("ADODB.Connection")
cn.CommandTimeout = 600
cn.Open(Conn & SystemVariables.CodeObject.CompanyDatabaseName)
Set rs = CreateObject("ADODB.Recordset")
rs.Open sSql, cn, 3, 3
End Function

Answer 1

1. As your rs.RecordCount > 0 just checks whether the recordset is not empty, you can avoid .Recordcount (and all it's problems) by testing for Not rs.EOF
2. Don't trust secondary sources; the MS docs contain "... and either -1 or the actual count for a dynamic cursor, depending on the data source". So maybe your provider is to blame. In that case (or when you really need a specific number), a SELECT COUNT() would be a workaround
3. Don't use magic numbers as in rs.Open sSql, cn, 3, 3, but define (and doublecheck) your Consts like adOpenStatic, adLockOptimistic, ...

Answer 2

From Help

Set oRs = New ADODB.Recordset
oRs.CursorLocation = adUseClient
oRs.Open sSQL, sConn, adOpenStatic, adLockBatchOptimistic, adCmdText

Help has a full description of Cursors in What is a Cursor (ADODB Programmers Guide - Windows Software Development Kit).

You'll burn resources either locally or on the server to get a record count. If you are going through the data anyway, just count them.

This is how to go through a recordset one at a time.

    Do While not .EOF
        Outp.writeline .Fields("Txt").Value
        .MoveNext
    Loop