[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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?


0
vagg
Asked:
vagg
  • 3
  • 2
  • 2
  • +3
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now