asked on

VB- ADO Returns a recordcount of -1

This should be a really easy one. I have the following code:

SQLConnect = "Provider=SQLOLEDB;Data Source=CLUSTERNAME\CLUSTERINSTANCE;Initial Catalog=MyTable;Integrated Security=SSPI;"
Conn1.ConnectionString = SQLConnect
Conn1.Open
Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "Select MyColumn from MyTable where Need2Publish = 1"
Set Rs1 = Cmd1.Execute
Rs1.MoveFirst
i = 1
x = 1
intCount = Rs1.RecordCount
MsgBox intCount

This always produces a RecordCount of -1, even though there are 2 records? I know for a fact there are two records because if I do this:

intCount = 2
If intCount > 0 Then
ReDim strVarName(intCount)
Do Until Rs1.EOF
strVarName(i) = Rs1.Fields(0).Value
MsgBox (strVarName(i))
Rs1.MoveNext
i = i + 1
Loop
End If

It returns my 2 values in the message box...

Does anyone have an explanation to this?

TIA,

Owen

Sethi

This is becuase you are not specifying the type of recordset that you want to open. ADO by default will open a Forward only recordset and it will always return -1. Open a dynamic or keyset recordset and it will give you a recordcount. Another way of getting the total no of records is by opening another recordset:
strSQL="Select Count(*) from tblTableName"
rsGetData.Open

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eojhan

ASKER

Why does aduseclient work and aduseserver not? Why is this the only way to get at the recordcount property?