The information in this article applies to:
- Enterprise Edition of Microsoft Visual Basic 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
- 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.
- 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
- From the Run menu, choose Start (ALT, R, S) or press F5 to start the
application.
- 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.
- 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.
|