BUG: "Insufficient Key Column Information" While Updating ADO Recordset (815542)


The information in this article applies to:

  • ActiveX Data Objects (ADO)

SYMPTOMS

You have a ADO Recordset that is populated with data from a join query, and the table that is used in the join query does not have primary keys. When you try to update the data of this recordset in the database, you may receive the following error message:
Run-time error '-2147467259 (80004005)' :
Insufficient key column information for updating or refreshing.

WORKAROUND

To work around this problem, use server-side cursor to update the recordset as follows:
myCn.CursorLocation = adUseServer
myCn.Open
myRs.Open "Your SQL Query", myCn

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  1. In SQL Query Analyzer, run the following SQL Statements to create the tables in Pub database:
    CREATE TABLE [dbo].[tbl_WorkOrderItems] (
 [WorkOrderItemSeq] [numeric](18, 0) NULL ,
 [EquipLocationSeq] [numeric](18, 0) NULL ,
 [UserSeq] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_UserInfo] (
 [UserId] [numeric](18, 0) NULL ,
 [UserSeq] [numeric](18, 0) NULL ,
 [UserName] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

INSERT INTO tbl_UserInfo VALUES (1,1,'Robert')
INSERT INTO tbl_WorkOrderItems VALUES (0,1,1)
GO
  2. Start Visual Basic 6.0. On the File menu, click New Project.
  3. In the New Project dialog box, click Standard Exe. By default, Form1 is created.
  4. On the Project menu, click References. Locate and select Microsoft ActiveX Data Objects 2.7 Library, and then click OK.
  5. Double-click Form1 to open the code view for Form_Load. Add the following code to Form1_Load event.
       On Error Goto ErrHandler
   Dim myConn As ADODB.Connection
   Dim myRs As ADODB.Recordset
   
   ' Connection for SQL Server
   Set myConn = New ADODB.Connection
   myConn.Open ("Data Source=(local);Provider=SQLOLEDB;Initial Catalog=Pubs;Integrated Security=SSPI")
   Set myRs = New ADODB.Recordset
   
   ' SQL Join Query
   Dim sSql As String
   Dim sKey As Integer
   sSql = 1
   sSql = " SELECT tbl_WorkOrderItems.*"
   sSql = sSql & " , tbl_UserInfo.UserId AS UserID"
   sSql = sSql & " FROM tbl_WorkOrderItems "
   sSql = sSql & " LEFT OUTER JOIN tbl_UserInfo ON"
   sSql = sSql & " tbl_WorkOrderItems.UserSeq = tbl_UserInfo.UserSeq"
   sSql = sSql & " WHERE tbl_WorkOrderItems.WorkOrderItemSeq=" & sKey
   
   ' Specify the Cursor location
   myRs.CursorLocation = adUseClient
   myRs.Properties("Update Criteria") = adCriteriaKey
   
   ' Open the Recordset
   myRs.Open sSql, myConn, adOpenKeyset, adLockOptimistic
   myRs("UserSeq").Value = 25
   
   ' Update the Recordset
   myRs.Update
   myRs.Close
   myConn.Close
   
   Set myRs = Nothing
   Set myConn = Nothing
   Exit Sub
ErrHandler:
   MsgBox "Error No.: " & Err.Number
   MsgBox "Error Message: " & Err.Description
  6. On the Run menu, click Start. You may receive the error message that is described in the "Symptoms" section of this article.

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

246905 BUG: Problem Updating ADO Hierarchical Recordset When Join Tables Share Same Column Name


Modification Type:MajorLast Reviewed:5/10/2003
Keywords:kbDataObject kbProgramming kbCursor kbtable kbbug KB815542 kbAudDeveloper
©2002 Microsoft Corporation. All rights reserved.