Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Delete Record in multi user environment.

Posted on 2001-08-24
15
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 

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 400 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
 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

670 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