New concurrency error

[Robert Schuldenfrei]

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
(e-mail address removed)

{
//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;
}
}

 

[Kevin Yu [MSFT]]

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."

 

[Nick Malik]

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]

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
(e-mail address removed)

 

[Robert Schuldenfrei]

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
(e-mail address removed)