locked
OptimisticConcurrencyException with Rows Affected Parameter on an Update Stored Procedure RRS feed

  • Question

  • I have an update sproc (notice I have inserted a RowVersion column of type timestamp in Product from Northwind):

    CREATE PROCEDURE [dbo].[ProductUpdate]
        (
        @productId int,
        @productName nvarchar(40),
        @categoryID int,
        @unitPrice money,
        @discontinued bit,
        @rowversion timestamp,
        @updateCount int OUTPUT
        )
    AS
        UPDATE Products
        SET ProductName = @productName,
            CategoryID = @categoryID,
            UnitPrice = @unitPrice,
            Discontinued = @discontinued
        WHERE ProductID = @productId
        AND RowVersion = @rowversion

        SELECT RowVersion
        FROM Products
        WHERE ProductID = @productId
        AND @@ROWCOUNT > 0

        SELECT updateCount = @@ROWCOUNT

        RETURN

    I mapped the update sproc to the Product entity, checking "Use Original Value" for RowVersion and "Rows Affected Parameter" for @updateCount.

    For some reason I am getting an OptimisticConcurrencyException when I should not.  Am I doing something wrong?  My sample project is here:
    http://tonysneed.com/elinq/download/HelloSprocs.zip

    Cheers,
    Tony
    • Edited by Tony Sneed Sunday, December 20, 2009 11:47 AM
    Saturday, December 19, 2009 6:49 PM

Answers

  • I found the problem.  It's in the stored proc.  There's a missing @ before updateCount in the select statement.  It should be:

    SELECT @updateCount = @@ROWCOUNT

    As a result the output parameter is not being set, which means it's always going to be zero and generate an OptimisticConcurrencyException.  I corrected the SQL in the update sproc, and now everything works as expected. :-)

    Tony
    • Marked as answer by Tony Sneed Sunday, December 20, 2009 2:25 PM
    Saturday, December 19, 2009 10:19 PM