Microsoft KB Archive/104459
Article ID: 104459
Article Last Modified on 1/8/2003
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q104459
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."
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.
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.
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
- 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."
Additional query words: 3.00
Keywords: kbprb KB104459