Concurrency problem

  • Thread starter Thread starter Phil Daley
  • Start date Start date
P

Phil Daley

I am controlling concurrency in my database records via a datetime column,
with a stored procedure such as:
UPDATE .......WHERE myid=@myid AND lastmodified=@lastmodified
SELECT @lastmodified=lastmodified ........WHERE myid=@myid

In this way, after any update, I get the new lastmodified column returned so
that if the user saves the data again everything still works. This works as
expected.

However, if the update fails, I get the expected DBConcurrencyException, but
the data in the Row property does not contain the new datetime value, but
the original datetime. I can get at the new datetime by trapping the
RowUpdated event and examining Command.Parameters, but as I am using a
generic routine for all my updates I cannot use this method as different
tables may have different datetime column names and I cannot pass the column
name to the event.

Anyone any ideas?
 
Phil,

If you bound the parameter to the corresponding DataColumn
and your parameter name (@lastmodified) is generic, you could
rely on the parameter's SourceColumn name to determine the
appropriate column to change with code in the RowUpdated event
like:

parm = Command.Parameters["@lastmodified"];
e.Row[parm.SourceColumn] = parm.Value;

Keep in mind that you're enabling a potentially dangerous
situation by setting up failed update attempts to overwrite
changes made by another user that the current user has never
seen. I trust you're adding code to make sure this does not
occur without some sort of confirmation.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
David,

Thanks for the reply. I had worked out the solution already, exactly as you
pointed out.

Yes, I do throw up an error message when the concurrency error occurs. This
gives the user the option of cancelling their changes or overwriting the
other users changes. Dangerous, yes, but this is what my customer requires.

Phil
 
Glad to hear you have a solution that's working for you, and
for the customer.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Back
Top