Hi,

We are creating our first ASP.Net application, using ADO.Net for data
access.

Having taken advice from many sources, we were convinced to use stored
procedures instead of pushing details of our database structures about the
network in SQL queries. This is the advice contained in "Building secure
ASP.Net applications", from Microsoft.

However we find that although ADO.Net took care of concurrency for us when
we used SQL queries, once we moved to stored procedures this was no longer
the case. We are not generating the stored procedures automatically, but
are writing them ourselves, because they need to be quite complex (The user
must be able to enter values in the user interface that are used as a
retrieve profile. A null value means that all values for that field are
acceptable).

Is there any way that concurrency can be ensured whilst using stored
procedures?

Thanks in advance.

Peter Bradley

Here are two methods you could use:

CREATE TABLE myTable(
Id INT IDENTITY(1, 1) NOT NULL,
Value VARCHAR(50),
TimeStamp DATETIME
)

--------------------- DateTime stamp----------------------

CREATE PROCEDURE sp_Update
@Id INT,
@Value VARCHAR(50),
@TimeStamp DATETIME
AS

UPDATE myTable SET
Value = @Value
WHERE Id = @Id AND TimeStamp = @TimeStamp

GO

-------------------- First Update Succeeds -------------------

CREATE PROCEDURE sp_Update
@Id INT,
@Value VARCHAR(50),
@OldValue VARCHAR(50)
AS

UPDATE myTable SET
Value = @Value
WHERE Id = @Id AND Value = @OldValue

GO

Thanks Mark.

It's late Friday now here. So I'll look at it Monday, and let you know how
we get ong

Cheers

Peter

"Mark Miller" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Here are two methods you could use:
>
> CREATE TABLE myTable(
> Id INT IDENTITY(1, 1) NOT NULL,
> Value VARCHAR(50),
> TimeStamp DATETIME
> )
>
> --------------------- DateTime stamp----------------------
>
> CREATE PROCEDURE sp_Update
> @Id INT,
> @Value VARCHAR(50),
> @TimeStamp DATETIME
> AS
>
> UPDATE myTable SET
> Value = @Value
> WHERE Id = @Id AND TimeStamp = @TimeStamp
>
> GO
>
> -------------------- First Update Succeeds -------------------
>
> CREATE PROCEDURE sp_Update
> @Id INT,
> @Value VARCHAR(50),
> @OldValue VARCHAR(50)
> AS
>
> UPDATE myTable SET
> Value = @Value
> WHERE Id = @Id AND Value = @OldValue
>
> GO
>
>