Solved

How to Delete Record in multi user environment.

Posted on 2001-08-24
15
288 Views
Last Modified: 2008-03-03
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.

0
Comment
Question by:hijay23
  • 6
  • 2
  • 2
  • +5
15 Comments
 
LVL 5

Expert Comment

by:rkot2000
ID: 6422349
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
0
 

Author Comment

by:hijay23
ID: 6422372
Hello rkot2000 ,

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

regards jay
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6422427
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.

0
 

Author Comment

by:hijay23
ID: 6422497
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

regards
Jay
0
 
LVL 6

Accepted Solution

by:
andyclap earned 100 total points
ID: 6422499
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?!?)
0
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6422504
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.

Zaphod.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6422525
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:rkot2000
ID: 6422545
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 :


CREATE PROCEDURE BUP_U_UNIT
     (
     @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_ENABLED_IND Bit,
     @UNT_COMMENTS_DSC Varchar(255),
     @OldTimeStamp   TimeStamp,
     @TimeStamp   TimeStamp OUTPUT,
     )
AS
UPDATE  BUDPRST.dbo.BUT_UNIT
SET     UNT_UNIT_NM_DSC = @UNT_UNIT_NM_DSC ,
     UNT_UNIT_DSC = @UNT_UNIT_DSC,
     UNT_MOD_DT = @UNT_MOD_DATE,
     UNT_MOD_USER_NBR = @UNT_MOD_USER_NBR,
     UNT_ENABLED_IND = @UNT_ENABLED_IND,
     UNT_COMMENTS_DSC = @UNT_COMMENTS_DSC

WHERE      UNT_UNIT_CD = @UNT_UNIT_CD
AND         UNT_TIMESTAMP_TS = @OldTimeStamp;

IF @@ROWCOUNT = 0
      BEGIN
            RETURN - 5
      END
ELSE
      BEGIN
          SELECT @TimeStamp = UNT_TIMESTAMP_TS
          FROM BUDPRST.dbo.BUT_UNIT
          WHERE  UNT_UNIT_CD = @UNT_UNIT_CD;

          Return 0
     END
0
 
LVL 1

Expert Comment

by:krees
ID: 6422674
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.
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6422719
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.
0
 
LVL 1

Expert Comment

by:krees
ID: 6422757
Yes, I think that is a safe way to maintain the information accurate. And if something wrong happs you can recover the lost info...

cheers
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6422766
found a typo error( i had < should be > )

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

0
 
LVL 14

Expert Comment

by:wsh2
ID: 6423116
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.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7202508
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
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7216047
Per recommendation, force-accepted.

Netminder
CS Moderator

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now