Q241818

• Microsoft Visual Basic Professional Edition for Windows, versions 5.0, 6.0
• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2, 2.5, 2.6
• Microsoft SQL Server versions 6.5 Service Pack 4 and later, 7.0

SYMPTOMS

Trying to modify the contents of a calculated field within an ActiveX Data Objects (ADO) recordset, generates the following error:

Runtime error '-2147217887(80040e21)' errors occurred.

CAUSE

Each calculated field in an ADO recordset contains the following attributes:

• adFldUnknownUpdatable = False
  
  -and-



• adFldUpdatable = False

RESOLUTION

Here are two ways to work around this behavior:

• Use the Shape command to append a field into the ADO recordset.
  
  -or-



• Use the calculated field for display purposes only without modifying its contents.

STATUS

This behavior is by design.

MORE INFORMATION

NOTE: Setting the adFldUnknownUpdatable and adFldUpdatable attribute flags is provider dependent. If the provider does not set the flags as indicated previously, you get a run-time error when you try to save the record.

The sample code below uses the publishers table in the pubs database that ships with SQL Server.

Steps to Reproduce Behavior

1. Start a new Visual Basic project. Form1 is created by default. Create a reference to the Microsoft ActiveX Data Objects 2.x Library.



2. Paste the following code in the General Declaration section of Form1:




Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub Form_Load()

' Connection String to your SQL Server
con.ConnectionString = "Provider=SQLOLEDB.1;Data Source=sequel;User ID=sa;Password=;Initial Catalog=pubs;"
con.Open

rs.CursorLocation = adUseClient
' Concatenate a character 'A' onto the value retrieved from the field pub_name.
rs.Open "SELECT pub_name + 'A' AS PN FROM Publishers", con, adOpenStatic, adLockBatchOptimistic, adCmdText

MsgBox rs.RecordCount

Debug.Print rs(0).Attributes And adFldUnknownUpdatable, _
            rs(0).Attributes And adFldUpdatable

rs(0) = "Hello" ' <----- ERROR OCCURS HERE

MsgBox "Passed!"

End Sub 

3. The code prints FALSE for each of the flags, adFldUnknownUpdatable and adFldUpdatable, indicating that the field is known to be non-updateable.

REFERENCES

For more information on how to use the Shape command to append a field to an ADO recordset, please refer to the following article in the Microsoft Knowledge Base:

Q223771 PRB: Appending Fields to a Recordset Generates an Error

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ammar Abuthuraya, Microsoft Corporation

Additional query words: error -2147217887(80040e21)

Keywords : kbADO kbDatabase kbVBp600 kbGrpDSVBDB kbGrpDSMDAC kbDSupport kbADO210sp2 kbCodeSnippet kbMDAC260 kbADO260
Issue type : kbprb
Technology :