BUG: ADO/SHAPE Generates Incorrect UPDATE Statement When Using UNION Clause

If you issue a SHAPE command whose child recordset contains a UNION clause, and the UNION creates a single resultset from multiple different tables with different schema, the UNION works because the fields are concatenated in such a way that the resulting resultsets have the same fields.

However, if you try to update a field in the resulting child recordset, you may receive one of the following error messages: In the first case, several rows are updated in the base tables, and sometimes the updates are performed in the wrong table. With the second error, no rows are updated.

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

1. Create and populate the tables in Microsoft SQL Server by using the following script:
   
   NOTE: You can cut and paste the statements directly into the SQL Query Analyzer tool, the ISQL utility, or the OSQL utility.

   CREATE TABLE [dbo].[Contacts] (
   	[ContID] [varchar] (15) NOT NULL ,
   	[ContFName] [varchar] (50) NULL ,
   	[ContLName] [varchar] (50) NULL ,
   ) ON [PRIMARY]
   GO
   
   CREATE TABLE [dbo].[ContactEmails] (
   	[ContID] [varchar] (15) NOT NULL ,
   	[ContEmailAddr] [varchar] (30) NOT NULL ,
   	[ContEmailDefault] [bit] NULL 
   ) ON [PRIMARY]
   GO
   
   CREATE TABLE [dbo].[ContactFaxNumbers] (
   	[ContID] [varchar] (15) NOT NULL ,
   	[ContFaxNumber] [varchar] (30) NOT NULL ,
   	[ContFaxDefault] [bit] NULL 
   ) ON [PRIMARY]
   GO
   
   CREATE TABLE [dbo].[ContactPhones] (
     [ContID] [varchar] (15) NOT NULL,
     [ContPhoneNumber] [varchar] (30) NULL ,
     [ContPhoneDefault] [bit] NULL 
   ) ON [PRIMARY]
   GO
   
   INSERT INTO Contacts (ContId, ContFName, ContLName) VALUES(1,'John','Doe')
   
   INSERT INTO ContactEmails VALUES (1, 'johndoe@doe.com',0)
   INSERT INTO ContactEmails VALUES (1, 'johndoe@john.com',0)
   INSERT INTO ContactEmails VALUES (1, 'johndoe@johndoe.com',0)
   
   INSERT INTO ContactFaxNumbers VALUES (1, '1234567',0)
   INSERT INTO ContactFaxNumbers VALUES (1, '2345678',0)
   INSERT INTO ContactFaxNumbers VALUES (1, '3456789',0)
   
   INSERT INTO ContactPhones VALUES (1, '1234567',0)
   INSERT INTO ContactPhones VALUES (1, '2345678',0)
   INSERT INTO ContactPhones VALUES (1, '3456789',0)
   					

2. In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default.
3. On the Project menu, click References. From the list of available references, select Microsoft ActiveX Data Objects 2.5 Library.
4. Paste the following code in the Declarations section of Form1:

   Private Sub Form_Load()
       
     Dim cn As Connection
     Dim rs As Recordset
     Dim rsChild As Recordset
     Dim strSQL As String
      
     Set cn = New Connection
     Set rs = New Recordset
     Set rsChild = New Recordset
   
     cn.Open "PROVIDER=MSDataShape;Data Provider=MSDASQL;" & _
             "Driver={SQL Server};Server=<YourServer>;Uid=sa;" & _
             "Pwd=<YourPassword>;Database=<YourDB>"
               
     rs.CursorLocation = adUseClient
      
     strSQL = "SHAPE {select ContID, ContFName, ContLName From Contacts " & _
              "ORDER by ContID} AS ParentCMD APPEND ({select ContID,  " & _
              "ContEmailDefault as ContDefault from ContactEmails " & _
              "UNION select ContID, ContFaxDefault as ContDefault from " & _
              "ContactFaxNumbers UNION select ContID, ContPhoneDefault " & _
              "AS ContDefault from ContactPhones ORDER by ContDefault " & _
              "DESC} AS ChildCMD RELATE ContID TO ContID)"
              
      rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, -1
                   
                       
      Set rsChild = rs.Fields.Item("ChildCMD").Value
      rsChild.Fields.Item("ContDefault").Value = True
       
      rsChild.Update
       
      rsChild.Close
      rs.Close
      cn.Close
   
   End Sub
   					

5. Modify the cn.Open statement to point to your SQL Server.
6. Run the project.
7. Attempt to update rsChild. You receive one of the error messages that are listed in the "Symptoms" section.