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