Microsoft KB Archive/247029

= FIX: Problem Assigning ADO Recordset Numeric Fields to Another Field =

Article ID: 247029

Article Last Modified on 5/17/2007

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1

-



This article was previously published under Q247029



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



RESOLUTION
There are three ways to work around this problem:  Use Server-side cursor (adUseServer).  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   Use the .Value property of the Column object, such as: rs2("K2").Value = rs1("K1").Value 



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:

http://msdn2.microsoft.com/en-us/data/aa937695.aspx



Steps to Reproduce Behavior
 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.  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 </li></ol>

<div class="references_section">