Microsoft KB Archive/104459

= PRB: Error 3219 When Updating Record Set Created w/ Distinct =

Article ID: 104459

Article Last Modified on 1/8/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q104459



SYMPTOMS
Trying to perform an update on a result set created with the Distinct predicate results in error 3219 "Can't perform operation, it is illegal."



CAUSE
An object variable or a data control result set created with the Distinct predicate is not updatable according to Help. The following statement is in the Distinct keyword Help topic:

The output of a query that uses Distinct is not updatable and doesn't reflect subsequent changes made by other users. Therefore, when you use the Distinct predicate in a query, you are prevented from trying to update your records.



WORKAROUND
The only workaround at this time is to not use the DISTINCT predicate to build the results set. Note that you may have to handle the duplicates by some other coding means.



STATUS
This behavior is by design.



Steps to Reproduce Problem
 Start Visual Basic or from the File menu, choose Open Project(ALT, F, O) if Visual Basic is already running. Form1 is created by default. Add a data control, two command buttons, and one text box to Form1.  Using the following table as a guide, set the properties of the controls you added in step 2.   Control Name   Property       New Value                     Comment Command1      Caption        "Set Up Distinct Predicate" Command2      Caption        "Press for Update" Data1         DatabaseName   BIBLIO.MDB                    Provide the full path to                                                              this file, which should be in C:\VB Data1         RecordSource   Authors Text1         DataSource     Data1 Text1         DataField      Author

  Add the following code to Command1 click event procedure: Sub Command1_Click '* Enter the following two lines of code as one, single line: data1.RecordSource = "Select DISTINCT Author From authors        where author > 'a'" data1.Refresh End Sub

  Add the following code to Command2 click event procedure: Sub Command2_Click data1.Recordset.Update End Sub

</li> From the Run menu, choose Start (ALT, R, S), or press the F5 key to run the program. Click the Command1 button to set up the Distinct predicate. Delete the zero in "Arnson, Robert, 1970." Then click the Command2 button. This should result in the 3219 error "Can't perform operation, it is illegal."</li></ol>

Additional query words: 3.00

Keywords: kbprb KB104459

-

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

© Microsoft Corporation. All rights reserved.