Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

How to Delete Record in multi user environment.

Suppose  i am working on a CustomerMaster form, in that form I am getting all the records in the grid. I am using disconnected recordset to fill the flex grid. After filling grid i am double clicking on Grid
and i get all the values of that record in the respective text boxex below the grid.
Now i am making changes in the text boxes. While doing that Some other user deletes the record which i am updating. so Plz Tell me how to restrict that user from deleting record.

  • 6
  • 2
  • 2
  • +5
1 Solution
since you save a disconnected recordset you can't do it.
if you want to hold a lock you should use pessimistic lock but you need to have a connection
hijay23Author Commented:
Hello rkot2000 ,

 Can u give me any alternative? Or tell me how to do it with using diconnected recordset.. plz ..

regards jay
when open a recordset :

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

you should set locktype to : adLockPessimistic

LockType   Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Can be one of the following constants (see the LockType property for more information).

Pessimistic locking, record by record?the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

hijay23Author Commented:
yes that i know we can do that using recordset.
But if i want to use it with disconnected recordset then how to do that. I am asking any idea ... plz

I'd never use passimistic locking for this sort of thing! It's really used in batch processing within transactions, otherwise you'll end up locking all the data the user is viewing, while he takes a three hour lunch break down the pub.

Normally one would use optimistic locking in this scenario. For a detached record set you'll need to implement the error handling yourself (although the default error handling of the data bound controls really gets on my nerves).
Do this using some kind of timestamp field on the table. You can use real timestamps, version counters, lastUpdate time fields etc.
When you update the record, check the timestamp is the same as the one you got when you loaded the record you're displaying (ie use something like update tbl set fld='newval', ts='newtimestamp' where ts='oldtimestamp'). Check the records affected - if you get no records affected then msgbox the user saying "The data has changed, please refresh this screen and try again" Or alternatively something a lot friendlier.

I've used various other techniques in the past - many of which would probably be overkill (how does a socket based network synchronisation and locking/checkout system sound?!?)
Two options that I can think of off the top of my head if you insist on using a disconnected recordset.

1. Have two recordsets, one which locks the records and the other which the user makes the changes to. Apply all of the changes the user makes to the disconnected recordset to the connected recordset.

2. Add a flag to all of the records that is used to indicate that they are locked by a disconnected recordset.

You can add 2 extra columns locktime and user id.
Then you select a record  in a grid you can send an update sql to update columns with now(datetime) and your user id.

In delete statement you can check if user id from record <> current application user.
You need to check locktime value if it?s less than 20 min(20 ? timeout) you can?t delete that record.
I agree 200% with andyclap

I'd never use passimistic or optimistic locking for this sort of thing!
I am using timestamps with the following logic
if you have the same timestamp  user can update a record :

     @UNT_UNIT_NM_DSC Varchar(10),
     @UNT_UNIT_CD Int,
     @UNT_UNIT_DSC Varchar(25),
     @UNT_MOD_DATE DateTime,
     @UNT_MOD_USER_NBR Varchar(30),
     @UNT_COMMENTS_DSC Varchar(255),
     @OldTimeStamp   TimeStamp,
     @TimeStamp   TimeStamp OUTPUT,

AND         UNT_TIMESTAMP_TS = @OldTimeStamp;

            RETURN - 5
          SELECT @TimeStamp = UNT_TIMESTAMP_TS

          Return 0
Yes, always when u use disconnected recordset you must do the updates in batch. You need to know who changed/deleted the record at what time and when you are doing the upload compare that info and do the commands in a first come, first serve basis.
This is kinda iffy due the fact somebody could have taken hours to change the record only to find out that someone else just erased it. Soy you may want to maintain profiles of users who can delete/update.

Keep us posted.
Two more options:
During update batch you can add this logic : If record is deleted add a new one.

Or you can implement soft delete (you can have status column and last modified time) for status columns you can use the following values : A ? active d ? deleted.

So if somebody tries to delete a record you can set a status to ? d, but during any regular update set to a

Also you need to create a batch job to delete soft deleted  records
Job :
Delete * from table where status = d and last modified < 1 week ago.

So you have a week to recover a record.
Yes, I think that is a safe way to maintain the information accurate. And if something wrong happs you can recover the lost info...

found a typo error( i had < should be > )

job should be :
Delete * from table where status = d and last modified > 1 week ago.

hijay23 Question History:

Questions Asked 9
Last 10 Grades Given A C  
Question Grading Record 2 Answers Graded / 2 Answers Received

SEVEN Open Questions dating back to NOVEMBER of last year.
Hi hijay23,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Split points between: rkot2000 and andyclap

hijay23, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.

CS Moderator

rkot2000: points for you at http://experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20335609

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now