Microsoft KB Archive/142926

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

Article ID: 142926

Article Last Modified on 5/7/2003

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 Enterprise Edition

-



This article was previously published under Q142926



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.



Steps to Reproduce
 Start Visual Basic, or if it is already running, choose New Project from the File menu. 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. Double click on the RemoteData Control in the Toolbox to add a new RemoteData Control, named MSRDC1, to the form. 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. 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. Place two command buttons on form1 that will be named Command1 and Command2 by default.</li>  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

</li> From the Run menu, choose Start (ALT, R, S) or press F5 to start the application.</li> 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.</li> 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.</li></ol>

Additional query words: kbVBp400 kbVBp600 kbdse kbDSupport kbVBp

Keywords: kbprb KB142926

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.