Saving disconnected recordset

[drbyte]

Using vb6, ado2.5 & sqlserver 7.0, I get the recordset(rst) with adUseClient, trying various combinations of keyset/dynaset/static/unspecified and various locks, populate form textboxes with fields then set the rst.activeconnection = nothing and the textboxes datasource=rst and datafield = "fieldnames". All is good. Then (after simple edit) try to set the activeconnection back to new connection and save the rst (using combinations of update/updatebatch & adAffectCurrent) but keep getting error "-2147217864 Row cannot be located for updating. Some values may have been changed since it was last read." What does this mean? Well of course they've been changed! Why else would I need to save it? Can't find any reference to the err number. Hopefully it's some simple syntax thing, any help?
Thanks!!
-marti

 

[chiph]

Once you disconnect a recordset, that's pretty much it. If you want to save changes to it, don't disconnect it in the first place, or keep track of your changes and batch them to the database by issuing the appropiate Insert/Update/Delete SQL statements.

Chip H.

 

[drbyte]

Ok, thanks. That's unfortunate. Can't leave it open due to WAN limitations and the users tendacy to keep the program open all day, and tooo many fields to track for changes (sounds like an ordeal). Guess I'll try to save off the employee # when they hit the edit button to unlock the textboxes and re-query and then keep the connection open till the save button is clicked.

 

[balves]

I thought you could update an disconnected recordset...no?

- open the recordset with CursorLocation = aduseClient, CursorType = adOpenStatic, LockType = adLockBatchOptimistic

- disconnect the recordset

- make changes to the recordset

- reconnect

- call .UpdateBatch on the recordset

 

[drbyte]

That is also what I thought. But I've tried many syntax combinations for the recordset cursortype, cursorlocation locktype update type (including those you mentioned), modifying rs.fields("field&quot vs bound textbox etc, but to no avail. Maybe in the next ADO version...

 

[Sypher2]

Balves should be correct. You can disconnect a recordset then make the changes at a later time. You use adUseClient for the cursor location, and adLockBatchOptimistic for your lock type. Example:

Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

cn.ConnectionString = "(your connection string here)"
cn.Open
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Employees", cn, adOpenStatic, adLockBatchOptimistic, adCmdText
Set rs.ActiveConnection = Nothing
cn.Close

'Do your work with the recordset here

'Then when ready to update the database:
cn.Open
Set rs.ActiveConnection = cn
rs.UpdateBatch

Is this not working for you?

 

[drbyte]

Yes! That's it! Must've been the static and adLockBatchOptimistic. balves had it rite. Now I'm back in love with the new ado. Thanks folks!