error message prompt!
-
Hi all,
if the record is locked before hand. Otherwise it is not user friendly if user opens, edits it. And then finally updates it to see the error message that says "Cannot perform update operation since this record is locked". and can tell who is accessing the record
How can i handle with store procedure.
-
Egads, yet another thread.
Refer to my last post over here,
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=163086
If you don't understand my suggestion, post a reply explaining what you don't understand.
--------------------
Colt 45 - the original point and click interface
-
Sorry phil i do understand that but just would like to do this
IsThisRecordLocked
(
@RecordID int
@retval int out --0 is not locked. 1 is locked.
)
how can i write that sp, not able to figure it out! help pls.
thanks
-
???
Do you want to interrogate SQL Server to determine the locking, or do you have a seperate field that indicates if the record is locked or not? If you have a seperate field, then in your front-end just check that field before allowing edits. This would be the simplest way to achieve what you want.
I still don't understand why you specifically need this requirement and why you can't just use SQL Servers internal locking mechanisms.
--------------------
Colt 45 - the original point and click interface -
Hi phil,
I don't have the separat field that indicates the record is locked or not!! but trying to do this because they want to see the error message on front end saying that the user is using this record and then the other user can just view it, cannot change it or anything. with this do but getting error in this sp.
create procedure IsThisRecordLocked
(
@RecordID int,
@retval int out --0 is not locked. 1 is locked.
)
AS
if ( select * from EIS_repairlog
where repair_number = @RecordID
)
begin
@retval = 1
will this return the objective i want to achieve. but there is error in this sp. please tell me how to sole it
thanks
-
The query you're using will not work. I don't know what your front-end is or how it displays the data to users. The usual method would be for the front-end to request a record. At this time SQL Server will attempt to obtain a shared lock on the data. If it obtains the shared lock the query succeeds and the record is displayed to the user. If there is a lock held on the record, the query is blocked and SQL Server will wait till the record is available, or depending on connection settings it may time-out. This is where the term "blocking" comes from. Excessive blocking is the first indication of bad design.
Once the record is displayed on the users screen SQL Server is no longer involved. The query to return the result has completed and there is nothing more for it to do so any locks are released. Any editing that happens on the client is totally seperate from the server. When the client submits changes to the server, then SQL will attempt to obtain an exclusive lock. If it obtains the lock then changes are written to the database. Again if it can't obtain the lock it will wait or time-out.
As mentioned in previous posts, to achieve what you want to do, add a field to the table to indicate if the record is locked or not. Then in your front-end BEFORE you allow the user to "edit" the record check if this field is set. Then you can either allow the user to edit the record, or display you message saying that the record is locked.
--------------------
Colt 45 - the original point and click interface -
Hi phil that clears some clouds of confusion but the question if i add field which tells me that the record is locked or not but how am i going to keep track of it! idea please!
Thanks
-
What do you mean by "keep track of it"??
When a user want's to edit a record you check if the field is set. If not, then set it and allow the user to edit the record. Otherwise display a message saying the record is being edited. When the user has finished editing the record you clear the field.
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply