DOCUMENT:Q181720  09-JAN-2000  [vbwin]
TITLE   :PRB: MOVE 0 Works Only with RDO Server-Side Cursors
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:
OPER/SYS:
KEYWORDS:kbGrpDSVBDB

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0 
-------------------------------------------------------------------------------

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
---------------------------

1. In SQL Server Enterprise Manager, add two new fields to the Pub_Info table in
   the Pubs database:

   a. Open the Enterprise Manager and find the Pub_Info table in the Pubs
      database.

   b. Right-click the Pub_Info table and click Edit.

   c. 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).

   d. On the ISQL command window, issue the following query to initialize these
      new fields:

            UPDATE Pub_Info SET Pub_Name = 'Name'

2. 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."

3. 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.

         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=sa;pwd=;database=PUBS;")
          Command1.Caption = "resultset"
          Command2.Caption = "edit "
          Command3.Caption = "update"
          Command4.Caption = "quit"
         End Sub

4. 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.

5. 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.

6. 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.

REFERENCES
==========

In Visual Basic Books Online see the following:

   Guide to Building Client Server Applications in Visual Basic (Enterprise);
   Choosing an RDO Cursor Type

For additional information, please see the following article in the Microsoft
Knowledge Base:

   Q168162 FIX: RDO Move 0 Fails to Refresh Record

Additional query words: locking locktype rdconcurvalues kbVBp500 kbVBp600 kbRDO kbdse kbDSupport kbVBp

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB500Search kbVB600Search kbVB500 kbVB600
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2000.