Modifying Calculated Fields in ADO

The Problem
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 (http://support.microsoft.com/default.aspx?scid=kb;en-us;241818 https://support.microsoft.com/en-us/kb/241818 https://static.heironimus.info/blog/msdn/241818.html) that describes this issue. The article suggests using the Shape command to get around this issue. That workaround is OK for some situations, but there are other situations where you may want to avoid using the Shape command. For those situations you can try the following technique.

The Workaround
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
AS
BEGIN

    SELECT TestID, 'Ready' AS Status,
        State + ', ' + County AS StateCounty
    FROM Test

END

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
(
    [TestID] ASC
) 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
AS
BEGIN
    -- 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,
        StateCounty NVARCHAR(54))

    -- 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
    FROM TEST

    SET NOCOUNT OFF;

    -- Return data to user
    SELECT Test.TestID, Constants.Status AS Status,
        CalculatedValues.StateCounty AS StateCounty
    FROM Test
        INNER JOIN @CalculatedValues AS CalculatedValues
            ON CalculatedValues.TestID = Test.TestID
        CROSS JOIN @Constants AS Constants

END

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 http://private.csgsolutions.com/heironimus/
codesamples/StoredProcTester.zip https://static.heironimus.info/blog/codesamples/StoredProcTester.zip. The application prompts you for a database connection string and the name of the stored procedure you want to run. When you press the "Get Recordset" button, it executes the stored procedure and places the results in a data grid. You can use the data grid to test whether or not you can edit a particular field.

Pros

• You do not have to use the Shape command.

Cons

• 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.