ADO: Recordset.RecordCount not working in SQL Server

  • I am using VBScript with ADO (Active Data Objects), to connect to SQl Server 2000. For some reason the "RecordCount" property of "RecordSet" is not working with SQL Server. It returns -1 or something. It works perfectly fine with the same table in Microsoft Access.

    I had to do a work around by actually looping through all records and counting (i.e. do until Recordset.EOF).

    But am I missing something important here?

    Do I need to do something to the table in SQL Server to get the "recordcount" property to work? I.e. build some kind of index or something?

    Just want to make sure I am using the database in the most efficient way, thanks.

    rst2.MoveFirst

    lentrycount =0

    Do Until rst2.EOF

    lEntryCount = lEntryCount + 1

    rst2.MoveNext

    Loop

  • Recordcount Ado:

    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.

    source:

    http://www.w3schools.com/ADO/prop_rs_recordcount.asp

    http://support.microsoft.com/kb/194973

  • Even in MSAccess you could not rely on the recordcount unless you had accessed all records in the recordset by doing a .movelast and then a .movefirst after opening the recordset. For a large dataset this can take some time

    To get a count without looping through the recordset, fire of a stored procedure that does a

    SELECT Count(*) as RecordCount FROM tblSourceTable

    and then check the value returned

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply