Microsoft KB Archive/181720

= PRB: MOVE 0 Works Only with RDO Server-Side Cursors =

Article ID: 181720

Article Last Modified on 11/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q181720



SYMPTOMS
"MOVE 0" fails to refresh the contents of a record with the current contents of the database after an UPDATE fails.



CAUSE
A client-side cursor is being used.



RESOLUTION
Make sure cursordriver is set to rdUseServer or rdUseIfNeeded. Some databases do not support server-side cursors.



STATUS
This behavior is by design.



MORE INFORMATION
The "MOVE 0" command is used to refresh a record to the current contents of the database after a failed update. The UPDATE failed because the contents of the record changed since the time it was originally retrieved for editing. The cursor must be a server-side cursor for MOVE 0 to work correctly. If the cursor is a client-side cursor, MOVE 0 will not refresh the contents of the record with the current contents of the database.

Steps to Reproduce Behavior
 In SQL Server Enterprise Manager, add two new fields to the Pub_Info table in the Pubs database:

 Open the Enterprise Manager and find the Pub_Info table in the Pubs database. Right-click the Pub_Info table and click Edit. Add two new fields to this table. Name one field Pub_Name and make it char(10) (allow NULL values). Name the second field WhatTime and make it a timestamp field (also allow NULL values).  On the ISQL command window, issue the following query to initialize these new fields: UPDATE Pub_Info SET Pub_Name = 'Name' </li></ol> </li> Start Visual Basic and create a new Standard EXE project. On the Project menu, click References, and then check "Microsoft Remote Data Object 2.0."</li>  Place four command buttons on the form and paste the following code in the form code window. Change dsName:=mymachine (below) to a datasource name on your machine.

Note You must change UID and PWD to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Option Explicit Dim en As rdoEnvironment Dim rs As rdoResultset Dim cn As rdoConnection Dim sqlstr As String

Private Sub Command1_Click sqlstr = "select * from pub_info" Set rs = cn.OpenResultset(sqlstr, rdOpenKeyset, _         rdConcurRowVer) Debug.Print rs(3) End Sub

Private Sub Command2_Click rs.Edit rs(3) = "McMillan" Debug.Print rs(3) & " after edit" End Sub

Private Sub Command3_Click On Error GoTo errhand rs.Update Exit Sub errhand: MsgBox Err.Number rs.Move 0 Debug.Print rs(3) & " after move 0" End Sub

Private Sub Command4_Click If Not (rs Is Nothing) Then rs.Close End If      cn.Close Unload Me     End Sub

Private Sub Form_Load Set en = rdoEnvironments(0) en.CursorDriver = rdUseClientbatch Set cn = en.OpenConnection(dsName:="mymachine", _          Prompt:=rdDriverNoPrompt, _           Connect:="uid= ;pwd= ;database=PUBS;") Command1.Caption = "resultset" Command2.Caption = "edit " Command3.Caption = "update" Command4.Caption = "quit" End Sub </li>  Make two copies of this project. In the second copy, change the following line of code from rs(3)="McMillan" to the following: rs(3)= "Schuster" Run one of the projects. Click Resultset and then click Edit. </li> Run the second copy of this project and click Resultset, click Edit and then click Update. Return to the first instance of the project and click Update. The 40002 error is trapped and the MOVE 0 command is executed.

NOTE: The immediate window does not show the current value in the database. The value reverts to what the original query retrieved.</li> Change rdUseClientBatch to rdUseServer or rdUseIfNeeded. The value displayed in the debug window after the UPDATE error now shows the value currently in the database. MOVE 0 does not refresh the record to the current value if cursordriver is set to rdUseodbc or rdUseClientBatch.</li></ol>

<div class="references_section">