Microsoft KB Archive/304277

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

Article ID: 304277

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.6 Service Pack 1
 * Microsoft Data Access Components 2.7
 * Microsoft Data Access Components 2.8

-



This article was previously published under Q304277



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.



STATUS
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
  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)  In Microsoft Visual Basic, create a new Standard EXE project. Form1 is created by default. On the Project menu, click References. From the list of available references, select Microsoft ActiveX Data Objects 2.5 Library.  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 &quot;PROVIDER=MSDataShape;Data Provider=MSDASQL;&quot; & _ &quot;Driver={SQL Server};Server=;Uid=sa;&quot; & _ &quot;Pwd=;Database=&quot; rs.CursorLocation = adUseClient strSQL = &quot;SHAPE {select ContID, ContFName, ContLName From Contacts &quot; & _ &quot;ORDER by ContID} AS ParentCMD APPEND ({select ContID, &quot; & _           &quot;ContEmailDefault as ContDefault from ContactEmails &quot; & _           &quot;UNION select ContID, ContFaxDefault as ContDefault from &quot; & _           &quot;ContactFaxNumbers UNION select ContID, ContPhoneDefault &quot; & _           &quot;AS ContDefault from ContactPhones ORDER by ContDefault &quot; & _           &quot;DESC} AS ChildCMD RELATE ContID TO ContID)&quot; rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, -1 Set rsChild = rs.Fields.Item(&quot;ChildCMD&quot;).Value rsChild.Fields.Item(&quot;ContDefault&quot;).Value = True rsChild.Update rsChild.Close rs.Close cn.Close

End Sub  Modify the cn.Open statement to point to your SQL Server.</li> Run the project.</li> Attempt to update rsChild. You receive one of the error messages that are listed in the &quot;Symptoms&quot; section.</li></ol>

Keywords: kbbug kbpending KB304277

-

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

© Microsoft Corporation. All rights reserved.