record count

i am opening the rs like this Dim rstemp As New ADODB.Recordset

 rstemp.Open " Some query ", con, adOpenDynamic, adLockOptimistic
 If rstemp.EOF = False Then
 lngAfterInsertCnt = rstemp.RecordCount
 End If

rstemp.RecordCount is giving -1. WHAT COULD BE THE PROBLEM ?

more ▼

asked Apr 24, 2011 at 10:38 PM in Default

avatar image

161 13 16 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi, here is descrption and resolution of the problem in the KB article on MSDN: PRB: ADO: Recordcount May Return -1.

It states:

When you request the RecordCount for a serverside recordset, a -1 may return. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic.

What is exactly your situation.

more ▼

answered Apr 25, 2011 at 02:39 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

@suraj 6 Pavel is spot on, the problem is caused by the cursor location. Sometimes it is possible to "fix" the problem by moving the cursor to last and then back to first record, so the record count is populated, but it is better to simply set the cursor location to the client instead. Just make sure that you set the cursor location before you open the recordset, not after, because setting it after the recordset is already open is too late.

rstemp.CursorLocation = adUseClient
rstemp.CursorType = adOpenDynamic 
rstemp.Open " Some query "
Apr 25, 2011 at 10:31 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 24, 2011 at 10:38 PM

Seen: 596 times

Last Updated: Apr 24, 2011 at 10:38 PM

Copyright 2018 Redgate Software. Privacy Policy