One of the limitations of ADO Recordsets is that you cannot edit or modify calculated or constant fields. Microsoft has a knowledgebase article 241818-PRB: Calculated Field Contents Cannot be Modified by ADO (
Instead of using constants or calculated fields in your SELECT statement, create a temporary or in-memory table (or tables) and select the constants or calculated fields from there. The following is a sample stored procedure that returns a constant (Status) and a calculated field (StateCounty):
CREATE PROCEDURE StatusReadOnly
SELECT TestID, 'Ready' AS Status,
State + ', ' + County AS StateCounty
The following script can be used to create the Test table used in the stored procedure above:
CREATE TABLE [dbo].[Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[State] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[County] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
) ON [PRIMARY]
If you run the stored procedure and place the results in an ADO Recordset, you will not be able to edit the Status or the StateCounty fields.
The following stored procedure uses in-memory tables to prevent the Status and StateCounty fields from being read-only:
CREATE PROCEDURE StatusReadWrite
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Build in-memory tables
DECLARE @Constants TABLE (
Status NVARCHAR(15) NOT NULL)
DECLARE @CalculatedValues TABLE (
TestID INT NOT NULL,
-- Populate table with desired editable constants
INSERT INTO @Constants VALUES (N'Ready')
-- Populate table with desired editable calculated values
INSERT INTO @CalculatedValues (TestID, StateCounty)
SELECT TestID, State + N', ' + County
SET NOCOUNT OFF;
-- Return data to user
SELECT Test.TestID, Constants.Status AS Status,
CalculatedValues.StateCounty AS StateCounty
INNER JOIN @CalculatedValues AS CalculatedValues
ON CalculatedValues.TestID = Test.TestID
CROSS JOIN @Constants AS Constants
If you run this stored procedure and place its results in an ADO Recordset, you will be able to edit both the Status and the StateCounty fields. This stored procedure demonstrates using an in-memory table for constant values and it also demonstrates using an in-memory table for calculated fields. The example above makes two passes through the Test table to obtain the desired results, but it could be rewritten to only make one pass through the Test table by copying all fields, not just the calculated fields, to the in-memory table.
I have written a very simple VB 6 application that you can use to test these two stored procedures. You can download the application and its source files at
- You do not have to use the Shape command.
- If you are returning constants, you have to create and populate a constants in-memory or temporary table. This could negatively affect performance.
- If you are returning calculated fields, you may have to make two passes through your data to get the desired results. This could adversely affect performance.
- Your queries may become more complex.