Entry

Why does 'rsname.RecordCount' always return -1 even when looping through all records?

Apr 4th, 2003 08:47
Joe Roulette, Wayne Feltham,

I had the same problem and a VB buddy suggested opening the recordset 
with a cursor type other than the default. This solved the problem. 
Here's an example:

	set conn = server.createobject("ADODB.CONNECTION")
	set rs = server.createobject("ADODB.RECORDSET")
	conn.Open strSQLconnection
			
	sqlString = "SELECT * FROM authors"

	rs.Open sqlString, conn, adOpenStatic

The default cursor type is "adOpenForwardOnly" There are 3 or 4 other 
cursor types available, I'm not sure which ones, other than the static 
one, will solve the problem.

I will leave it to the real experts to explain why one cursor type 
works and another doesn't...

- JR

04-APR-2003: Here's another method that doesn't require defining cursor 
types; it works with the default cursor type and uses the ADO GetRows 
method:

'OPEN ODBC 'DSNLESS' CONNECTION TO SQL-SERVER...
Dim strConnSQL, oConnSQL, strSQL, rs, rsAR, rsRowCount

Set oConnSQL = Server.CreateObject("ADODB.Connection")
strConnSQL = "Driver={SQL Server};Server=SERVER-NAME;Database=DB-
NAME;UID=USER-NAME;PWD=PASSWORD"
oConnSQL.open(strConnSQL)
strSQL = "SELECT * FROM AUTHORS"

set rs = oConnSQL.Execute(strSQL)

If Not(rs.eof and rs.bof) Then
  rsAR = rs.Getrows                'rsAR is a 2-dimensional array
  rsRowCount = ubound(rsAR,2) + 1  'This is your record count
  rs.movefirst                     'This is important

'do your normal stuff here, like writing out records...

End If

rs.close
set rs = nothing
set oConnSQL = nothing

-- JR