Microsoft KB Archive/241818

= PRB: Calculated Field Contents Cannot be Modified by ADO =

Article ID: 241818

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft SQL Server 6.5 Service Pack 4
 * Microsoft SQL Server 7.0 Standard Edition

-



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
 Start a new Visual Basic project. Form1 is created by default. Create a reference to the Microsoft ActiveX Data Objects 2.x Library.  Paste the following code in the General Declaration section of Form1:

Note You must change User ID= and 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=;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.</li></ol>

Note Calculated columns are also read-only in Data Access Objects (DAO) and Remote Data Objects (RDO).

<div class="references_section">