472,783 Members | 1,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,783 software developers and data experts.

vb.net data adapters - concurrency and insert comands

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
Jul 21 '05 #1
3 2353
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.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"Suzanne" <sj***********@mhapensions.com> wrote in message
news:3c**************************@posting.google.c om...
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

Jul 21 '05 #2
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" <miha at rthand com> wrote in message news:<#f**************@TK2MSFTNGP12.phx.gbl>...
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.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Jul 21 '05 #3
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 :)

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com
Jul 21 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Kevin C | last post by:
I was curious to know what some developers out in the industry are doing when it comes to exposing Data access logic, specifically persistence. This is assuming that your not using an O/R framework...
3
by: Suzanne | last post by:
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...
2
by: BobAchgill | last post by:
Do you know why this error might be happening? Maybe it is because I have two data adapters open on the same MDB file?? If this is the problem... how can I keep them from disturbing each other?...
4
by: George | last post by:
Hi all, I am having trouble with updating my data in an Access database. here is my code: Imports System.Data.OleDb Dim AppPath As String = Mid(Application.ExecutablePath, 1,...
2
by: Paul Craig | last post by:
Hi everyone, I have recently upgraded several of my projects from Visual Studio 2003 to 2005 and everything went across quite smoothly. The main problem that I am having is I have used SQL Data...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
3
by: John | last post by:
Hi I have two data adapters bound to two separate tables. How can I; 1. Loop through all records one by one in one of them while reading column values, and 2. Insert a record from data...
3
by: John | last post by:
Hi I have a vs 2003 winform data app. All the data access code has been generated using the data adapter wizard and then pasted into the app. The problem I have is that I am getting a data...
12
by: Randy | last post by:
Hi, Trying to pass along a table row delete to the datasource, but I'm crashing. Here is the code: Private Sub btnDeleteIngr_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.