DOCUMENT:Q142926 11-JAN-2001 [vbwin] TITLE :PRB: Handling Errors Caused by the MSRDC Update Method PRODUCT :Microsoft Visual Basic for Windows PROD/VER: OPER/SYS: KEYWORDS:kbGrpDSVBDB ====================================================================== ------------------------------------------------------------------------------- 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 2001.