insert record..

[yyt]

Hi

I read through quite a few news postings and articles related to the
Concurrency issues in ado.net. .. But didn't get the concept and hence has
not solved my problem.

I have a asp.net app. where user submits a form to the database. The
asp.net appl. calls a SQL2K stored procedure to perform the db update. -
Inside the SP, I check if any record exists for the form Sr. #. if not, then
insert a new record otherwise update the record for the form #. The table
had a Unique Identity column as the primary key. The sql to check for
unique record looks something like this:

DECLARE @RECID INT
select @RECID = Form_ID from FormData where Form_SrNum = @Form_SrNum
set @RECID = ISNULL(@RECID,0)
if @RECID = 0
BEGIN
/* Insert new record here */
END
else
BEGIN
/* Update the record */
END

The Inserts /Updates work fine in a single-user case. However, if 2 users
happen to submit a new form at the same time then the changes made by user 1
get overwritten by user 2.

How can I need to avoid the concurrency? Do I need to modify the SP or the
asp.net .vb class?

Appreciate any help.

 

[JT]

You could use your the primarykey (PK) for your new
record, along with a Timestamp/Rowversion (TS). Return
these to your app and run a check at the beginning of your
update sproc to be sure that remote PK and TS match
current database PK and TS before allowing updates of
existing records.
JT

 

[yyt]

The table I'm updating has a ModifyDate column which gets updated whenevr
the record is changed.
Can this column be used alongwith the Primary Key.

JT, can you give me example of the steps you mentioned.
Thanks for your help.


You could use your the
primarykey (PK) for your new

 

[JT]

Here's a sample sproc to do concurrency checking prior to
updating.

CREATE PROCEDURE dbo.procUpdate
(
@MyVar1 varchar(25),
@MarVar2 varchar(25) = null,
@OriginalVarID int,
@OriginalTS rowversion,
@TSnew rowversion output,
@RetCode [int] = null output,
@RetMsg [varchar](250) = null output

)
AS
SET NOCOUNT ON

DECLARE @CheckTS rowversion

/*Test for concurrency*/
SELECT @CheckTS = TS FROM MyTable
WHERE PtID = @OriginalPtID

IF @CheckTS <> @OriginalTS
BEGIN
SELECT @TSnew = @CheckTS,
@RetCode = 0,
@RetMsg = 'Another user updated this information '
+
'while you were editing it.'
RETURN
END

/* ASSUME SUCCESS*/
SELECT @RetCode = 1, @RetMsg = ''

/* You can put other validation logic here*/

IF @RetCode = 0
RETURN

BEGIN TRAN
UPDATE MyTable
SET

MyVar1 = @MyVar1,
MyVar2 = @MyVar2

WHERE VarID = @OriginalVarID AND TS = @OriginalTS;


/* Check if Update was successful.
If not, rollback transaction and exit. */
IF @@ROWCOUNT = 1 AND @@ERROR = 0 -- SUCCESS
BEGIN
COMMIT TRAN
SELECT @TSnew = TS FROM MyTable WHERE
(VarID = @OriginalVarID)
SELECT @RETCODE = 1,
@RetMsg = 'Data updated in database.'
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @RetCode = 0,
@RetMsg = 'Data NOT updated!'
END
RETURN

JT