I am trying to update a Visual FoxPro table with an ADO recordset object. When I change the value of a field in one of the rows, however, the fields is changed in ALL of the rows after the update is complete. Here is sample code:

Dim con As New ADODB.Connection
Dim rs As ADODB.Recordset

con.Open "Driver={Microsoft Visual FoxPro Driver}; SourceType=DBF; SourceDB=c:\db; Exclusive=No;"

rs.Open "select * from items where ponumber = 111", con, adOpenForwardOnly, adLockOptimistic

rs!quantity = 42
rs.Update
rs.Close
set rs = Nothing

If the select returned two rows, both rows will have a quantity of 42. When I do the same thing with MS SQL Server, it only updates the first row.

I'd really appreciate any help!

  - Eric

I just noticed something peculiar. This only seems to happen if both records in the record set are completely identical. If there is any difference at all, only the first record is updated.

Again, any help would be great.

  - Eric

try this way:
SQL="Update items Set quantity=42 Where ponumber=111"
con.Exuecute(SQL)

That is because you dont loop through the recordset and update each. Afei's solution should help you achieve that in one step.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Thanks for the reply. The problem is that with the statement "Update items Set quantity=42 Where ponumber=111" it will update everything with a ponumber of 111. I only want to update the first record with a ponumber of 111 even if multiple are qualified. The update that I'm performing is actually much more complex, I just simplified it to make the illustration easier.

Essentially I do a select on a table and multiple rows qualify because every field in both rows is identical. When I update only the first row in the recordset, it updates both rows. The odd part is that even if both rows qualify my selection if any field is different, I don't have the problem--its only when every field is exactly the same.

  - Eric

Looks like you need to have a look at the table design. I don't understand why 2 rows should be identical in any case. If that was allowed, then obviously, it would update both the rows that are identical.

_________________________
- Vijay G
Strive for Perfection