473,372 Members | 834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

New concurrency error

Dear NG,

I was about to "improve" concurrency checking with a Timestamp when I
discovered
that my current code is not working. After about a day of beating my head
against the wall, I am turning to the NG in hopes that someone can spot what
I am doing wrong.

Key to this technique working is the SQL UPDATE statement. It is designed
to fail
if the row on file in SQL has been modified by some other user. User 1
comes in
and starts to change the txtPrime.Text. User 2 then makes another change to
the
same text box and her completes the operation by going through the
ChangeItemLoc()
method. Now user 1 goes to UPDATE SQL and raises NO exceptions! The only
trouble is that user 1 changes do not happen. What I expected was that user
1
would get the concurrency error message in the calling code below.

I am sure I have done something stupid, but I can not spot it. Can you guys
see what I have done wrong.

Thank you,

Bob

Robert Schuldenfrei
bo*@s-i-inc.com

{
//Change code goes here. Get or Add instantiates object
ItemLoc oldItemLoc = new ItemLoc(); //Concurrency check
oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
oldItemLoc.Loc_location = itemLoc.Loc_location;
oldItemLoc.Loc_quantity = itemLoc.Loc_quantity;
oldItemLoc.Loc_prime = itemLoc.Loc_prime;
LoadItemLoc(); //take the changes out of text boxes and load object
if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
{
MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString() + " Could not
change location record. " +
" Record may be in use by another user.", "Datebase error");
}
DisableAddEditMode();
}
private void LoadItemLoc()
{
itemLoc.Loc_PartNo = txtPartNo.Text;
itemLoc.Loc_location = txtLoc.Text;
itemLoc.Loc_quantity = Convert.ToDecimal(txtQty.Text);
itemLoc.Loc_prime = txtPrime.Text;
}
public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
{
string updateStmt = "UPDATE ItemLoc SET "
+ "loc_PartNo = @loc_PartNo, "
+ "loc_location = @loc_location, "
+ "loc_quantity = @loc_quantity, "
+ "loc_prime = @loc_prime "
+ "WHERE loc_PartNo = @Old_loc_PartNo "
+ "AND loc_location = @Old_loc_location "
+ "AND loc_quantity = @Old_loc_quantity "
+ "AND loc_prime = @Old_loc_prime "
; //notice ; on line by itself

//Change to TRANSACTION form 08/30/04. Notice order of statements.
SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here
SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
trans
updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);

updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@Old_loc_location", oldItemLoc.Loc_location);
updateCommand.Parameters.Add("@Old_loc_quantity", oldItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@Old_loc_prime", oldItemLoc.Loc_prime);
try
{
updateCommand.Transaction = updateTrans; //Necessary? Yes
updateCommand.ExecuteNonQuery();
updateTrans.Commit();
mcs3Connection.Close();
return true;
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}
Nov 16 '05 #1
4 3074
Hi Robert,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that your application is not throwing
exceptions when concurrency issue happens. If there is any
misunderstanding, please feel free to let me know.

Based on the code you have provided, we can see that your concurrency
checking relies on the exception thrown from the
SqlCommand.ExecuteNonQuery. However, this method will never throw
DbConcurrencyException when Concurrency issues happen even the operation is
put in a transaction. Because DbConcurrencyException is used when
DbDataAdapter.Update method meets concurrency issues.

So we have to use the return value from ExecuteNonQuery to check for this.
The return value indicates the number of rows affected. When concurrency
issue happens in this case, there will be no rows affected, the return
value is zero. So we can use the following:

if(updateCommand.ExecuteNonQuery() >0)
return true;
else
return false;

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #2
Kevin Yu's response is completely correct. your code will not throw an
exception in the condition you specify.

Here is why:
Person A reads record 1 with timestamp 333
Person B reads record 1 with timestamp 333
Person A updates record 1. Timestamp is change to 444
Person B issues the update statement. The update statement says "Update the
row where the timestamp is 333"
However, there is no matching row, because the timestamp is now 444.

The number of rows affected is zero.

This is not an error. In SQL, it is perfectly legal to issue an update
query that doesn't match anything. So, as Kevin suggests, if you query the
rowcount, you can get an idea if the query failed to match the row as you
expect.

I do think that your update statement is not efficient. Doesn't this table
have a primary key? If it does, you only need to match on the primary key,
not on every field. If it doesn't, you should add one, even if it means
adding a unique column like an Identity or a UniqueIdentifier.

Also: when I used to use timestamp for concurrency (SQL 6.5), I would put
code into a SQL Server trigger that would actually raise an error if the row
was updated and the new timestamp value didn't equal the old timestamp
value... that way the code had something to catch. However, checking the
number of rows affected will work just as well.

Hope this helps,
--- Nick

"Robert Schuldenfrei" <bo*@s-i-inc.com> wrote in message
news:By5Zc.101438$mD.24263@attbi_s02...
Dear NG,

I was about to "improve" concurrency checking with a Timestamp when I
discovered
that my current code is not working. After about a day of beating my head
against the wall, I am turning to the NG in hopes that someone can spot what I am doing wrong.

Key to this technique working is the SQL UPDATE statement. It is designed
to fail
if the row on file in SQL has been modified by some other user. User 1
comes in
and starts to change the txtPrime.Text. User 2 then makes another change to the
same text box and her completes the operation by going through the
ChangeItemLoc()
method. Now user 1 goes to UPDATE SQL and raises NO exceptions! The only
trouble is that user 1 changes do not happen. What I expected was that user 1
would get the concurrency error message in the calling code below.

I am sure I have done something stupid, but I can not spot it. Can you guys see what I have done wrong.

Thank you,

Bob

Robert Schuldenfrei
bo*@s-i-inc.com

{
//Change code goes here. Get or Add instantiates object
ItemLoc oldItemLoc = new ItemLoc(); //Concurrency check
oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
oldItemLoc.Loc_location = itemLoc.Loc_location;
oldItemLoc.Loc_quantity = itemLoc.Loc_quantity;
oldItemLoc.Loc_prime = itemLoc.Loc_prime;
LoadItemLoc(); //take the changes out of text boxes and load object
if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
{
MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString() + " Could not change location record. " +
" Record may be in use by another user.", "Datebase error");
}
DisableAddEditMode();
}
private void LoadItemLoc()
{
itemLoc.Loc_PartNo = txtPartNo.Text;
itemLoc.Loc_location = txtLoc.Text;
itemLoc.Loc_quantity = Convert.ToDecimal(txtQty.Text);
itemLoc.Loc_prime = txtPrime.Text;
}
public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
{
string updateStmt = "UPDATE ItemLoc SET "
+ "loc_PartNo = @loc_PartNo, "
+ "loc_location = @loc_location, "
+ "loc_quantity = @loc_quantity, "
+ "loc_prime = @loc_prime "
+ "WHERE loc_PartNo = @Old_loc_PartNo "
+ "AND loc_location = @Old_loc_location "
+ "AND loc_quantity = @Old_loc_quantity "
+ "AND loc_prime = @Old_loc_prime "
; //notice ; on line by itself

//Change to TRANSACTION form 08/30/04. Notice order of statements.
SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here
SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
trans
updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);

updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@Old_loc_location", oldItemLoc.Loc_location); updateCommand.Parameters.Add("@Old_loc_quantity", oldItemLoc.Loc_quantity); updateCommand.Parameters.Add("@Old_loc_prime", oldItemLoc.Loc_prime);
try
{
updateCommand.Transaction = updateTrans; //Necessary? Yes
updateCommand.ExecuteNonQuery();
updateTrans.Commit();
mcs3Connection.Close();
return true;
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}

Nov 16 '05 #3
Hi Kevin and NG,

Thank you very much for your insight. I will make this correction a bit
later on in the morning. Then it will be on to using Timestamps as Nick
Malik suggests. Will keep the NG posted.

Cheers,

Bob

Robert Schuldenfrei
bo*@s-i-inc.com
"Kevin Yu [MSFT]" <v-****@online.microsoft.com> wrote in message
news:o1**************@cpmsftngxa10.phx.gbl...
Hi Robert,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that your application is not throwing
exceptions when concurrency issue happens. If there is any
misunderstanding, please feel free to let me know.

Based on the code you have provided, we can see that your concurrency
checking relies on the exception thrown from the
SqlCommand.ExecuteNonQuery. However, this method will never throw
DbConcurrencyException when Concurrency issues happen even the operation is put in a transaction. Because DbConcurrencyException is used when
DbDataAdapter.Update method meets concurrency issues.

So we have to use the return value from ExecuteNonQuery to check for this.
The return value indicates the number of rows affected. When concurrency
issue happens in this case, there will be no rows affected, the return
value is zero. So we can use the following:

if(updateCommand.ExecuteNonQuery() >0)
return true;
else
return false;

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Nov 16 '05 #4
Dear NG,

Thanks to Kevin Yu, I now have the concurrency exception working. Of course
I am about to ruin it as I want to use Timestamps to implement concurrency
checking.

Cheers,

Bob

Robert Schuldenfrei
bo*@s-i-inc.com
"Robert Schuldenfrei" <bo*@s-i-inc.com> wrote in message
news:By5Zc.101438$mD.24263@attbi_s02...
Dear NG,

I was about to "improve" concurrency checking with a Timestamp when I
discovered
that my current code is not working. After about a day of beating my head
against the wall, I am turning to the NG in hopes that someone can spot what I am doing wrong.

Key to this technique working is the SQL UPDATE statement. It is designed
to fail
if the row on file in SQL has been modified by some other user. User 1
comes in
and starts to change the txtPrime.Text. User 2 then makes another change to the
same text box and her completes the operation by going through the
ChangeItemLoc()
method. Now user 1 goes to UPDATE SQL and raises NO exceptions! The only
trouble is that user 1 changes do not happen. What I expected was that user 1
would get the concurrency error message in the calling code below.

I am sure I have done something stupid, but I can not spot it. Can you guys see what I have done wrong.

Thank you,

Bob

Robert Schuldenfrei
bo*@s-i-inc.com

{
//Change code goes here. Get or Add instantiates object
ItemLoc oldItemLoc = new ItemLoc(); //Concurrency check
oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
oldItemLoc.Loc_location = itemLoc.Loc_location;
oldItemLoc.Loc_quantity = itemLoc.Loc_quantity;
oldItemLoc.Loc_prime = itemLoc.Loc_prime;
LoadItemLoc(); //take the changes out of text boxes and load object
if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
{
MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString() + " Could not change location record. " +
" Record may be in use by another user.", "Datebase error");
}
DisableAddEditMode();
}
private void LoadItemLoc()
{
itemLoc.Loc_PartNo = txtPartNo.Text;
itemLoc.Loc_location = txtLoc.Text;
itemLoc.Loc_quantity = Convert.ToDecimal(txtQty.Text);
itemLoc.Loc_prime = txtPrime.Text;
}
public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
{
string updateStmt = "UPDATE ItemLoc SET "
+ "loc_PartNo = @loc_PartNo, "
+ "loc_location = @loc_location, "
+ "loc_quantity = @loc_quantity, "
+ "loc_prime = @loc_prime "
+ "WHERE loc_PartNo = @Old_loc_PartNo "
+ "AND loc_location = @Old_loc_location "
+ "AND loc_quantity = @Old_loc_quantity "
+ "AND loc_prime = @Old_loc_prime "
; //notice ; on line by itself

//Change to TRANSACTION form 08/30/04. Notice order of statements.
SqlConnection mcs3Connection = MCS3_DB.GetConnection();
mcs3Connection.Open(); //TRANSACTION requires this to be here
SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
trans
updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);

updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
updateCommand.Parameters.Add("@Old_loc_location", oldItemLoc.Loc_location); updateCommand.Parameters.Add("@Old_loc_quantity", oldItemLoc.Loc_quantity); updateCommand.Parameters.Add("@Old_loc_prime", oldItemLoc.Loc_prime);
try
{
updateCommand.Transaction = updateTrans; //Necessary? Yes
updateCommand.ExecuteNonQuery();
updateTrans.Commit();
mcs3Connection.Close();
return true;
}
catch (SqlException ex)
{
updateTrans.Rollback();
excp = ex.Message;
excpNumber = ex.Number;
mcs3Connection.Close();
return false;
}
}

Nov 16 '05 #5

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

Similar topics

4
by: Charlie Williams | last post by:
I am having difficulty performing updates and deletions on an Access database using the Update() method of the OleDBDataAdapter. I can insert rows without a problem, but I get a concurrency...
2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
8
by: Mike Kelly | last post by:
I've chosen to implement the "optimistic concurrency" model in my application. To assist in that, I've added a ROWVERSION (TIMESTAMP) column to my main tables. I read the value of the column in my...
5
by: Bari Allen | last post by:
I'm trying to test for concurrency, using a SQL Stored Procedure on a RowVersion (timestamp) Field. The vb code I'm using is as follows Dim cmd As New SqlCommand("ConcurrencyCheck", cn) Dim...
4
by: Jerry | last post by:
Hi, I have an app which retrieves data from a sql server table and displays it on a datagrid. If 2 sessions of this app are running and 2 users try to update the same record at about the same...
4
by: Bob | last post by:
While testing my my program I came up with a consistency exception. My program consists of three datagridviews, One called dgvPostes which is the parent grid and its two children,one called...
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...
1
by: John | last post by:
Hi I have a perfectly working vs 2003 winform data app. All the data access code has been generated using the data adapter wizard and then pasted into the app. I had to add a new field in the...
5
by: John | last post by:
Hi I have developed the following logic to handle db concurrency violations. I just wonder if someone can tell me if it is correct or if I need a different approach.Would love to know how pros...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

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.