PRB: Calculated Field Contents Cannot be Modified by ADO (241818)


The information in this article applies to:

  • Microsoft Visual Basic Professional Edition for Windows 5.0
  • Microsoft Visual Basic Professional Edition for Windows 6.0
  • Microsoft Visual Basic Enterprise Edition for Windows 5.0
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • ActiveX Data Objects (ADO) 2.0
  • ActiveX Data Objects (ADO) 2.01
  • ActiveX Data Objects (ADO) 2.1
  • ActiveX Data Objects (ADO) 2.1 SP1
  • ActiveX Data Objects (ADO) 2.1 SP2
  • ActiveX Data Objects (ADO) 2.5
  • ActiveX Data Objects (ADO) 2.6
  • Microsoft SQL Server 6.5 Service Pack 4 and later
  • Microsoft SQL Server 7.0
This article was previously published under Q241818

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
This indicates that the field cannot be modified.

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:

    Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
    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=<User ID>;Password=<Strong 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.
Note Calculated columns are also read-only in Data Access Objects (DAO) and Remote Data Objects (RDO).

REFERENCES

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

223771 PRB: Appending Fields to a Recordset Generates an Error

Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbCodeSnippet kbDatabase kbprb KB241818
©2003 Microsoft Corporation. All rights reserved.