Move Next and RecordCount

Pls see the following program
      rsNO.Open "select * from detaillist where no='" + mvarNO + "'", classcn

      If Not rsNO.EOF Then
        rsNO.MoveNext
     End If
 
    dim i as integer
     i=rsno.RecordCount


this recordset has one record,
Why in the run time,rsNo.EOF is false,
so execute rsNO.MoveNext,the error will be raised.
Why always the rsNo.RecordCount is -1?


vaggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

richtsteigCommented:
If rdNo is a recordset you should use the OpenRecordset-method to fetch your record

sql = "SELECT * FROM DETAILLIST......."
Set rsNo = db.OpenRecordset(sql)
rs.MoveFirst

If Not rsNo.EOF should be TRUE now
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vaggAuthor Commented:
I'm using ADO, not DAO.
0
SharmiCommented:
TRy this out Vagg!

rsNO.Open "select * from detaillist where no='" + mvarNO + "'", classcn

If Not rsNO.EOF Then  rsNo.MoveLast
dim i as integer
i=rsno.RecordCount

Thanks
Sharmi
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

vaggAuthor Commented:
What is the diffrence with my code?
0
SharmiCommented:
The RecordCount property doesn't indicate how many records are contained in a dynaset-, snapshot-, or forward-only-type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object.


You have used MoveNext where as microsoft suggests to use movelast!

Sharmi
0
mark2150Commented:
Sharmi is correct.

The record set property is only valid after all records have been accessed. So you usually wind up "jiggleing" the records with:


RS.MoveLast
RS.MoveFirst
Debug.Print RS.RecordCount

This is faster than iterating thru a loop looking for then end.

Also .EOF and .BOF are only true *PAST* the ends and NOT on the last/first record.

M
0
ca_hecklerCommented:
Just to add one more possibility, try this:

RS.CursorLocation = adUseClient
RS.Open " ..... "

Now 'RS.RecordCount' contains the actual record count.

[]s
ca_heckler

0
ClunietpCommented:
as mentioned previously, use a client-side cursor (adUseClient) and your CursorType should be adOpenKeyset or adOpenStatic.  

If you want a forward only cursor, you can use a counter and loop until EOF while counting:

'declare db objects, i as integer
'connect to db, open rs as adOpenForwardOnly

i = 0

do until rs.EOF = True
  i = i + 1
  rs.movenext
loop

msgbox i

i will be the number of records

Tom
0
ClunietpCommented:
additional note:  you only need to do a RS.MOVELAST to get the recordcount when using DAO, not ADO
0
mark2150Commented:
Certainly Clunietp's solution will work, but it will be very slow as compared to:

RS.MoveLast
msgbox RS.RecordCount

M
0
ClunietpCommented:
Clarification:  Vagg is using ADO, and moving to the last record will not accomplish anything.  That is only applicable in DAO.  

I mentioned that if you were to use a client side, keyset or static cursor, that you can use the recordcount property of the ado recordset without any more code necessary.  I was only demonstrating my counter code IF it was necessary to get the recordcount AND use a forward-only cursor
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.