vb.net data adapters - concurrency and insert comands

  • Thread starter Thread starter Suzanne
  • Start date Start date
S

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
 
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.
 
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
 
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
Click to expand...

That is certainly one way.
Again thanks for the help
Click to expand...

You're welcome :)
 
Back
Top