vb.net data adapters - concurrency and insert comands

[Suzanne]

Hi All

I'm having problems getting my data adapter to throw a concurrency
exception with an INSERT command. I want to throw a concurrency
exception if an attempt is made to enter a row into tb_table when a
row with the same int_UID already exists in there.

Here is my stored procedure:

if not exists (select int_UID from tb_table where int_UID = @aint_UID)
begin
insert into tb_tables (int_UID, str_val1, str_val2)
values (@aint_UID, one, two)
end
else
begin
update xtb_SysConcurrency
set str_Update = 'No'
where int_Counter = -1
end

As you can see I'm trying to "force" 0 rows affected back to the data
adapter if the insert fails so that a concurrency exception is raised.
I thought that this was how it works .. 0 rows affected is interpreted
as a concurrency violation?

The problem is that although the second time the stored procedure is
run for the same UID, it (correctly) never does insert a row into the
table, however neither does it raise a concurrency violation.

I have tested my stored procedure in query analyser and can confirm
the second time it runs for the same UID it does come back with the
message 0 rows affected. Also I'm sure I have configured my data
adapter correctly as everything works just fine with update commands.
So what's going on? Does the INSERT command of the data Adapter not
respond to 0 rows affected?

Any help would be gratefully appreciated.

Thank you
Suzanne

 

[Miha Markic]

Hi Suzanne,

This is not a cuncurrency issue at all. Concurrency means that a user has
edited a record and saved it (or deleted) while you were editing the same
record.
Insert command can never produce a concurrency violation - that's why it is
never checked.
You shuld rather put a unique constraint on the column(s) and let database
throw you an unique constraint violation without any special code.

 

[Suzanne]

Hi Miha,
thanks for responding... I see your point and I do understand the
reasons.
The thing was - in the table I was trying to update - the column of my
int_UID is not /can not be unique (i.e. it is not the ID column for
this table).

However in the situation when 2 users download the same info at the
same time from the db then attempt to insert at the same time, I
wanted the second user to update the table to know that a row had been
inserted since they downloaded the data... thus I was trying to force
0 rows affected back to try and fabricate a concurrency violation.

Thanks for confirming the behaviour that on a insert command the data
adapter will not respond to 0 rows affected by generating a
DBConcurrencyException. I can now tackle things in a different way.. I
think what I must do instead is to raise an error in sql then catch it
in .net

Again thanks for the help

Suzanne

 

[Miha Markic [MVP C#]]

Hi Suzanne,

....

Thanks for confirming the behaviour that on a insert command the data
adapter will not respond to 0 rows affected by generating a
DBConcurrencyException. I can now tackle things in a different way.. I
think what I must do instead is to raise an error in sql then catch it
in .net

That is certainly one way.

Again thanks for the help

You're welcome