Updating ADODB recordset but NOT database 2

[sagn]

I have been trying to reset values in my recordset using syntax such as

rs.fields("colname").value = "value"

this seems to be fine.
But when I then try to do a MoveNext (or Move Previous)
I get a complaint that a column does not exist.
(This column is not necessarily the column I tried to update)

I don't want to update the database yet, only the recordset. Why might this be happening?

Thanks

sagn

 

[MikeBronner]

What you should do in this case is read the results from your SQL query into an array, instead of a recordset. Now you can update as you wish without it affecting the database or causing problems as described above.

Take Care,
Mike

 

[zemp]

before the .movenext call the .cancelupdate method.

zemp

 

[Golom]

Or use a disconnected recordset

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open SQL, CN, adOpenKeyset, adLockBatchOptimistic
set rs.ActiveConnection = Nothing

[COLOR=black cyan]' Commands to Edit, etc.[/color black cyan]

 

[kaeld]

Whenever you move between records in a recordset, update() is automatically called unless the lock type is adLockBatchOptimistic

 

[sagn]

Thanks a lot, one and all. I figured as much...

I'll try the rs.activeconnection=nothing call to see if that works...
as I'd rather not use the array method...

Thanks again. If I have any more questions about this
I'll be sure to ask!

 

[BobRodes]

<Whenever you move between records in a recordset, update() is automatically called unless the lock type is adLockBatchOptimistic

Not quite precisely true: this is of course only true of updatable recordsets. The default cursortype (firehose, or static on a client side recordset) is not updatable, which is why I'm making a point of this.

Golom's updatable recordsets are currently considered best practice in most cases. One thing, though: this is NOT a keyset recordset. All client-side cursors are static and not updatable. (adLockBatchOptimistic ones are batch updatable.) So, the adOpenKeyset directive in rs.Open is ignored.

Personally, I prefer to use client side recordsets, and do updates with command objects and SQL Server statements.

HTH

Bob