PRB: Calculated Field Contents Cannot be Modified by ADO

ID: Q241818

The information in this article applies to:

Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2
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

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

Start a new Visual Basic project. Form1 is created by default.

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

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, please refer to the following article in the Microsoft Knowledge Base:

Q223771 PRB: Appending Fields to a Recordset Generates an Error

Additional query words: kbgrpvbdb kbdsupport kbdatabase kbADO

Keywords : kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbCodeSnippet
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2,5.0,6.0; winnt:6.5 Service Pack 4 and later,7.0
Platform : WINDOWS winnt
Issue type : kbprb