Q142926: PRB: Handling Errors Caused by the MSRDC Update Method

Article: Q142926
Product(s): Microsoft Visual Basic for Windows
Version(s): 
Operating System(s): 
Keyword(s): kbGrpDSVBDB
Last Modified: 11-JAN-2001

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

- Microsoft Visual Basic Learning Edition for Windows, version 6.0 
- Microsoft Visual Basic Professional Edition for Windows, version 6.0 
- Microsoft Visual Basic Enterprise Edition for Windows, version 6.0 
- Microsoft Visual Basic Enterprise Edition for Windows, version 4.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

Using the Edit method, followed by the Update method with the RDC (Remote Data
Control), can cause run-time errors generated by the ODBC API that are
identified in the rdoErrors collection and in the Error event of the RDC that
you can disregard. "No Current Row" is an example of an error trapped in the
Error event, and "37000: [Microsoft][ODBC SQL Server Driver][SQL Server]
Incorrect syntax near the keyword 'WHERE'" is an example of an error from the
rdoErrors collection.

RESOLUTION
==========

To correct this problem, add error handling code to ignore error messages that
can be disregarded. The code to trap these errors needs to be located in two
places:

- The first place is the sub procedure that contains the
  MSRDC1.Resultset.Update method, where you will place an On Error Goto
  statement.

- The second place is in the RDC's Error event in which you will set
  CancelError = True to disable its default error handling. The code example
  below will show you how to do this.

STATUS
======

Microsoft has confirmed this to be a issue in the Microsoft products listed at
the beginning of the article. We are researching this problem and will post new
information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
================

Steps to Reproduce
------------------

1. Start Visual Basic, or if it is already running, choose New Project from the
  File menu.

2. If the Microsoft RemoteData Control is not in the Toolbox, right click on the
  Toolbox and select "Custom Controls" from the pop-up menu. In the Custom
  Controls dialog box, click on the entry labeled "Microsoft RemoteData
  Control" and click OK to add the Microsoft RemoteData Control.

3. Double click on the RemoteData Control in the Toolbox to add a new RemoteData
  Control, named MSRDC1, to the form.

4. Right-click on the MSRDC and select Properties to set the DataSource property
  to a valid DSN, and the SQL property to an appropriate SQL statement.

5. Place three Text Boxes on the form and set their DataSource properties to
  MSRDC1 and their DataField properties to three different fields in the
  recordset.

6. Place two command buttons on form1 that will be named Command1 and Command2
  by default.

7. Copy the following code into the General Declarations section of form1:

     Private Sub Command1_Click()
       MSRDC1.Resultset.Edit
     End Sub

     Private Sub Command2_Click()
       On Error GoTo ehUpdate
       MSRDC1.Resultset.Update
     ehUpdate:
       Dim r As rdoError
       For Each r In rdoErrors
         Debug.Print "ehUpdate: ", r.Number, r.Description
       Next r
       rdoErrors.Clear
       Resume Next
     End Sub

     Private Sub MSRDC1_Error(ByVal Number As Long, _
       Description As String, ByVal Scode As Long, _
       ByVal Source As String, ByVal HelpFile As String, _
       ByVal HelpContext As Long, CancelDisplay As Boolean)
       Select Case Number
         Case 56524  '"No current row" error
           ' proper error handling
         Case Else
           ' other error handling
       End Select
       Debug.Print "MSRDC1_Error: ", Number, Description
       CancelDisplay = True   ' don't display default error handling
     End Sub

8. From the Run menu, choose Start (ALT, R, S) or press F5 to start the
  application.

9. If the Debug window is not visible, Press CTRL+G to make it visible, then
  arrange it with your form1 so you can see both of them at the same time.

10. Click Command1 to execute the Edit method, change some data in one or more
  of the bound Text Boxes, then click Command2 to execute the Update method.
  The data will be saved back to the table correctly but you will receive the
  following three errors from the error handling of the above code:

  MSRDC1_Error: 56524 No current row

  ehUpdate: 0 21S02: [Microsoft][ODBC SQL Server Driver]Degree of derived table
  does not match column list

  ehUpdate: 40009 No current row

  Note: The errors received will vary depending on your environment so it is
  important for you to trap for any valid errors and handle them
  appropriately. An example of a valid error is one that would be encountered
  because of duplicate keys in a primary, unique index or the absence of data
  in a field created with the NOT NULL attribute.

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp

======================================================================
Keywords          : kbGrpDSVBDB 
Technology        : kbVBSearch kbAudDeveloper kbZNotKeyword6 kbZNotKeyword2 kbVB600Search kbVBA600 kbVB600 kbVB400Search kbVB400
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 1986-2002.