hijay23
asked on
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.
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.
ASKER
Hello rkot2000 ,
Can u give me any alternative? Or tell me how to do it with using diconnected recordset.. plz ..
regards jay
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.
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.
ASKER
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
But if i want to use it with disconnected recordset then how to do that. I am asking any idea ... plz
regards
Jay
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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 :
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
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
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.
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.
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...
cheers
cheers
found a typo error( i had < should be > )
job should be :
Delete * from table where status = d and last modified > 1 week ago.
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.
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
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.
Netminder
CS Moderator
rkot2000: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20335609
Netminder
CS Moderator
rkot2000: points for you at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20335609
if you want to hold a lock you should use pessimistic lock but you need to have a connection