Microsoft KB Archive/815542

= BUG: &quot;Insufficient Key Column Information&quot; While Updating ADO Recordset =

Article ID: 815542

Article Last Modified on 5/10/2003

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.7

-



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 &quot;Your SQL Query&quot;, myCn



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 the Behavior
  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  Start Visual Basic 6.0. On the File menu, click New Project. In the New Project dialog box, click Standard Exe. By default, Form1 is created. On the Project menu, click References. Locate and select Microsoft ActiveX Data Objects 2.7 Library, and then click OK.  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 (&quot;Data Source=(local);Provider=SQLOLEDB;Initial Catalog=Pubs;Integrated Security=SSPI&quot;) Set myRs = New ADODB.Recordset ' SQL Join Query Dim sSql As String Dim sKey As Integer sSql = 1 sSql = &quot; SELECT tbl_WorkOrderItems.*&quot; sSql = sSql & &quot;, tbl_UserInfo.UserId AS UserID&quot; sSql = sSql & &quot; FROM tbl_WorkOrderItems &quot; sSql = sSql & &quot; LEFT OUTER JOIN tbl_UserInfo ON&quot; sSql = sSql & &quot; tbl_WorkOrderItems.UserSeq = tbl_UserInfo.UserSeq&quot; sSql = sSql & &quot; WHERE tbl_WorkOrderItems.WorkOrderItemSeq=&quot; & sKey ' Specify the Cursor location myRs.CursorLocation = adUseClient myRs.Properties(&quot;Update Criteria&quot;) = adCriteriaKey ' Open the Recordset myRs.Open sSql, myConn, adOpenKeyset, adLockOptimistic myRs(&quot;UserSeq&quot;).Value = 25 ' Update the Recordset myRs.Update myRs.Close myConn.Close Set myRs = Nothing Set myConn = Nothing Exit Sub ErrHandler: MsgBox &quot;Error No.: &quot; & Err.Number MsgBox &quot;Error Message: &quot; & Err.Description  On the Run menu, click Start. You may receive the error message that is described in the &quot;Symptoms&quot; section of this article.</li></ol>

<div class="references_section">