adodb recordset problem

I am trying to access a table that I know exists and has data. But, when I create a recordset and check for RecordCount, I get a result -1 (no records). When I access the same table (using the same program), it reports (and I can view in a dbgrid) 752580 records exist.

Here's some of the code:

The table is originally copied from another database; I use the following code to be sure the previous connection is closed before proceeding.

If Not adoRS Is Nothing Then
If adoRS.State = adStateOpen Then adoRS.Close
Set adoRS = Nothing
End If
If Not DbConn Is Nothing Then
If DbConn.State = adStateOpen Then DbConn.Close
Set DbConn = Nothing
End If

Then a new connection (it works) is opened to access the database with the copied table:

strDbConn = "Provider=SQLNCLI;Integrated Security=SSPI;" & _
"Persist Security Info=False;Database=" & strDbName & ";" & _
"AttachDBFileName=" & DbPath & ";Data Source=.\sqlexpress;" & _
"User Instance=True"

Next I tried to create the recordset:

Set adoNewRS = New ADODB.Recordset 'Set OHLC recordset
Set adoNewRS.ActiveConnection = DestDbConn
adoNewRS.Open TableName, DestDbConn, adOpenDynamic, adLockOptimistic

Next I try to get the RecordCount:

NumRecords = adoNewRS.RecordCount

At this point, NumRecords (and adoNewRS.RecordCount) = -1 (even tho I know there are 752580 records in the table).

In the adoNewRS.Open statement, I also tried using the following sql statement:

sSQL = "SELECT * FROM TableName ORDER BY [DateTime];"

It also returns a recordcount = -1.

Anybody have clue?

While debugging, I noticed that adoNewRS.Open was taking about as long as the SQL query in ssms. So, I added adoNewRS.MoveLast & msgbox adoNewRS!XHigh and it popped up with the correct value. Apparently, the recordset was created correctly but the RecordCount value was wrong (as noted in previous post).

Using this new info, I discovered a PRB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;194973

Apparently, in some cases, CursorLocation = adUseServer returns -1 instead of the correct RecordCount. Changing the value to adUseClient resolved the issue.

ADO recordcount returns -1

Hi All
Upgraded Vb6 program from Access 2K to MSDE 2K RelA
Now all my checks for rs.recordcount return -1, except if I use Client side
cursor OR
if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
Why is this?
I don't want to use client side cursors and I need to save data so
(adLockReadOnly) is no good
I have found that that checking for rs.eof seems reliable as a substitute
for recordcount but would like to understand the reasoning
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> Upgraded Vb6 program from Access 2K to MSDE 2K RelA
> Now all my checks for rs.recordcount return -1, except if I use
> Client side cursor OR
> if I use... rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> Why is this?
> I don't want to use client side cursors and I need to save data so
> (adLockReadOnly) is no good
> I have found that that checking for rs.eof seems reliable as a
> substitute for recordcount but would like to understand the reasoning
this is quiet normal using ADO recorset as the recordcount will be available
only when the entire rowset has been populated, ant this is true only when a
..movelast operation ins performed and all the data has been transferred..
please be warned that performing a rs.movelast + rs.movefirst can be a
timeconsuming operation for big results...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||> please be warned that performing a rs.movelast + rs.movefirst can be a
> timeconsuming operation for big results...
In addition to that, if you DO need to know how many rows you have
(which OP did not in this case), I would first run a select
count(afield) " and the same from/where clause that you use in the
other query. Actually, in those cases you can sometimes eliminate some
of the INNER JOINS that you might have to use when getting the full
field list, and some of the WHERE clause, too -- which will give you
your rowcount faster.
I do this a lot when I want to display a progress bar.
In addition, when looping through a recordset, I ALWAYS do until
rs.eof...I never use the .rowcount property for anything but advising
the user. Not sure WHY I do this, but I've been doing it for years...
Matt
Matt