Re: ADO recordset RecordCount property question by Bob
Bob
Thu Jan 25 07:49:18 CST 2007
everymn@yahoo.com wrote:
> Hi,
>
> 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
> oConnection.Open
>
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
> oRecset.Open strSQL, strConn, adOpenStatic, _
> adLockBatchOptimistic, adCmdText
> MsgBox oRecset.RecordCount
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
oConnection.Open
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
tasks.
--
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.