Add incrementing numbers into a field.

[dimmslider]

Hey there.

I have a recordset with a bunch of users and passwords. What I wanna do is add an ID to each record... basically nubers from 1 to whatever.

It just goves random errors. Sometimes it does it right, although gives a EOF error on the last record(yet numbers the lot correctly)

Here's what I'm trying:

adoPrimaryRS.MoveFirst
For i = 1 To adoPrimaryRS.RecordCount
    adoPrimaryRS.Fields(0) = i
   If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
Next
adoPrimaryRS.Update

Where am I going wrong?

[Hack]

Is this an Access database?

[si_the_geek]

.Update affects the current record, so that should be inside the loop (before the .MoveNext line).

To avoid issues with EOF (and to eliminate the If statement), I would use a Do loop rather than a For loop, eg:

adoPrimaryRS.MoveFirst
i = 0
Do While Not adoPrimaryRS.EOF
  i = i + 1
  adoPrimaryRS.Fields(0) = i
  adoPrimaryRS.Update
  adoPrimaryRS.MoveNext
Loop

[dee-u]

If it is an Access database then you can just add an Autonumber field and this will be automatically incremented whenever you add a record to the table. It would be Identity for SQL Server.

[brucevde]

The reason you get errors with the For Loop is because the check for EOF occurs before the MoveNext. Always check for EOF/BOF after moving to a new record. When the For Loop exits EOF will be True. Calling the Update method when EOF is true will cause an error because there is no current record. All records will have been successfully modified already.

There is no need to call the Update method directly but it does no harm if you do. ADO automatically calls Update when the record position changes, or the recordset is being closed, and the current record has been updated/inserted.

I would have written this code with a Do Loop.

With adoPrimaryRS
  .MoveFirst
  Do Until .EOF
     .Fields(0) = .AbsolutePosition
     .MoveNext
  Loop
End With
'At this point the recordset is at EOF.

But this code should work just as well assuming the RecordCount property is valid. RecordCount may be equal to -1 depending on how you opened the recordset.

adoPrimaryRS.MoveFirst
For i = 1 To adoPrimaryRS.RecordCount
    adoPrimaryRS.Fields(0) = i
    adoPrimaryRS.MoveNext
Next

[dimmslider]

Thanx everyone!!!