Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q304277

↑ Back to the top


Symptoms

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:
-2147467259 Key column information is insufficient or incorrect. Too many rows were affected by update.
-or-
-2147217864 Row cannot be located for updating. Some values may have been changed since it was last read.
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.

↑ Back to the top


Status

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

↑ Back to the top


More information

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.

↑ Back to the top


Keywords: KB304277, kbpending, kbbug

↑ Back to the top

Article Info
Article ID : 304277
Revision : 4
Created on : 11/3/2003
Published on : 11/3/2003
Exists online : False
Views : 444