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.

FIX: Problem Assigning ADO Recordset Numeric Fields to Another Field


View products that this article applies to.

This article was previously published under Q247029

↑ Back to the top


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.
This problem is observed when client side cursor is specified (adUseClient).

↑ Back to the top


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
    					

↑ 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.

This bug was corrected in Microsoft Data Access Components version 2.5 or later. You can obtain the latest version of Microsoft Data Access Components form the following Microsoft Web site:

↑ Back to the top


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
    					

↑ Back to the top


References

For additional information about MDAC release history, click the article number below to view the article in the Microsoft Knowledge Base:
231943� NFO: Microsoft Data Access Components (MDAC) Release History

↑ Back to the top


Keywords: KB247029, kbmdac250fix, kbfix, kbdatabase, kbbug

↑ Back to the top

Article Info
Article ID : 247029
Revision : 6
Created on : 5/17/2007
Published on : 5/17/2007
Exists online : False
Views : 367