FIX: Problem Assigning ADO Recordset Numeric Fields to Another Field

• Microsoft Data Access Components versions 2.1, 2.1 (GA), 2.1 SP1, 2.1 SP2, 2.5

SYMPTOMS

Using MDAC 2.1 SP2 (build 2.1.2.4202.3) or earlier, when attempting to assign an ADO recordset field with Numeric data type to a second recordset field (for example, rs2!K2 = rs1!K1), the following error appears:

Run-time error '-2147217887 (80040e21)':
Error occurred.

RESOLUTION

There are three ways to work around this problem:

1. Use Server-side cursor (adUseServer).



2. Assign field value to a variable, and then assign the variable to the field of the second recordset, such as:




myVar = rs1!K1
rs2!K2 = myVar 

3. Use the .Value property of the Column object, such as:




rs2("K2").Value = rs1("K1").Value 

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This problem is fixed in Microsoft Data Access Components 2.5 (MDAC 2.5).

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Visual Basic. Form1 is created by default. Make a reference to the Microsoft ActiveX Data Objects 2.1 Library.



2. Double-click Form1. Copy and paste the following code under the Form_Load() event:




    Dim cn As New ADODB.Connection
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
        
    With cn
      .ConnectionString = "Provider=MSDASQL;Driver={SQL Server};UID=xxx;PWD=xxx;Server=MyServer;Database=Pubs;"
       .CursorLocation = adUseClient
       .Open
    End With
    
    On Error Resume Next
    cn.Execute "Drop Table T1"
    cn.Execute "Drop Table T2"
    On Error GoTo 0
    
    cn.Execute "Create Table T1 (K1 Numeric Primary Key)"
    cn.Execute "Create Table T2 (K2 Numeric Primary Key)"
    cn.Execute "Insert Into T1 Values (1)"
    cn.Execute "Insert Into T2 Values (1)"
    
    rs1.Open "SELECT * FROM T1", cn, adOpenKeyset, adLockOptimistic
    rs2.Open "SELECT * FROM T2", cn, adOpenKeyset, adLockOptimistic

    rs2!K2 = rs1!K1
    rs2.Update
    
    rs1.Close
    rs2.Close
    cn.Close 

© Microsoft Corporation 2000, All Rights Reserved.
Contributions by Adrian C. Bradley, Microsoft Corporation

REFERENCES

For additional information about MDAC release history, click the article number below to view the article in the Microsoft Knowledge Base:

Q231943 NFO: Microsoft Data Access Components (MDAC) Release History

Additional query words:

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbMDAC250fix
Version : WINDOWS:2.1,2.1 (GA),2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbbug