ADO recordset RecordCount property question

ADO recordset RecordCount property question

Post by everym » Fri, 26 Jan 2007 10:05:06


Is there some reason why the ADODB.RecordSet.RecordCount property
wouldn't function in VBScript? In both cases the RecordSet is being
populated as I can retrieve the values, but in the case of the script
it simply won't tell me the number of records.

This code works fine in VB6, but returns -1 when run in VB script

strSQL = "select * from ado_test"
strConn = ' valid connect string
oConnection.ConnectionString = strConn
oConnection.CursorLocation = adUseClient

oRecset.Open strSQL, strConn, adOpenStatic, _
adLockBatchOptimistic, adCmdText
MsgBox oRecset.RecordCount

ADO recordset RecordCount property question

Post by joseomj » Fri, 26 Jan 2007 12:10:32

Are you defining the values?

Const adUseClient = 3
Const adOpenStatic = 3
Const adLockBatchOptimistic = 4


ADO recordset RecordCount property question

Post by Bob Barrow » Fri, 26 Jan 2007 22:49:18

Where do you initialize your recordset object? I.E., where is the
Set oRecset = Createobject("ADODB.Recordset")
line? Actually, I assume you have done that, otherwise you would be
getting an error on the following line

The problem is that you have created a connection object (oConnection),
set its cursorloaction porperty to a non-default setting, opened it, and
then _failed to use that connection when opening the recordset!_. By
using the connection string in the oRecset.Open statement, you are
forcing ADO to open a NEW connection, with default properties: not a
good thing. If you are in the habit of using connection strings in your
recordset open statements instead of explicit connection objects, you
should make every effort to get out of that habit: you are in essence
disabling connection pooling when you do that. Replace "strConn" in the
above statement with "oConnection".

Having said that, I am still puzzled: your Open statement should be
opening a server-side, static cursor which should support RecordCount.
What do you see when you check the CursorType property after opening the
recordset (msgbox oRecset.CursorType)? Perhaps whatever provider you are
using does not support a server-side static cursor. What provider are
you using in strConn?

With a client-side cursor, the only type of cursor you should be able to
open is static (adOpenStatic), so the following technique should work:

oConnection.ConnectionString = strConn
oConnection.CursorLocation = adUseClient
dim rs
Set rs=oConnection.Execute(strSQL,,adCmdText)
msgbox rs.RecordCount

Lastly, why do you need the recordcount? Are you just checking to see if
the recordset contains records? If so, merely check its EOF property: if
a recordset contains records, it will be pointing at the first record
immediately after being opened, so EOF will never be true for a
just-opened recordset that contains records. Without the need for
RecordCount, there is no need for an expensive client-side cursor: a
default server-side forward-only cursor should be sufficient for most
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

ADO recordset RecordCount property question

Post by everym » Sat, 27 Jan 2007 17:26:24

Thanks for all this info. I had omited the object initialize
statements for brevity.

D'oh. I see what you mean. I didn't realize the undesirable effects
on connection pooling. I'll have to watch that.

Correct you are. The provider I'm using currently only supports
adForwardOnly, and I'm guessing that RecordCount isn't available in
that case, correct?

I'm writing a set of ADO conformance tests, or rather re-writing the
set of tests in the MDAC SDK to make sure our ODBC drivers are working
properly. Those tests are not very complete, and won't compile so I'm
porting them over and need to test as much as possible.