This topic is locked

How to prevent two people from editing the same record

6/26/2007 9:43:20 PM
ASPRunnerPro Tips and tricks
admin

ASPRunnerPro 5.2

This schema requires two additional fields in the database table to implement record locking.
IsLocked - Yes/No field, LockTime - Datetime field.
Timeout is set to 20 minutes. More sophisticated version can also save user name who locked the record and display it on the page.

Sub EditOnLoad()
set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.Open "select IsLocked, LockTime from " & strTableName & " where " & where, dbConnection
if rs("IsLocked")=1 and DateDiff( "n", rs("LockTime"), now() )<20 then
Response.write "Record is locked. Come back later"

rstmp.close : set rstmp = nothing

Response.end
else
rstmp.close : set rstmp = nothing

dbConnection.Execute "update " & strTableName & " set IsLocked=1, LockTime=now() where " & where
end if
End Sub


To unlock the record we can use BeforeEdit event:

Function BeforeEdit(dict, where)
dbConnection.Execute "update " & strTableName & " set IsLocked=0, LockTime=now() where " & where
End Function




ASPRunnerPro 5.0


Use custom event (Insert ASP code snippet option on the Visual Editor tab) instead of EditOnLoad event. Replace wherevariable with strWhereClause in this event:

set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.Open "select IsLocked, LockTime from " & strTableName & " where " & strWhereClause, dbConnection
if rs("IsLocked")=1 and DateDiff( "n", rs("LockTime"), now() )<20 then
Response.write "Record is locked. Come back later"

rstmp.close : set rstmp = nothing

Response.end
else
rstmp.close : set rstmp = nothing

dbConnection.Execute "update " & strTableName & " set IsLocked=1, LockTime=now() where " & strWhereClause
end if

T
telemark 4/9/2009

Here is an updated version for editing the same record. It adds some font changes and also outputs the user who locked the record (in my case ActionUName). I am very much an amateur in writing code and this took me hours (I'm not joking) especially finding out that I needed the double quotes. What I would like to know, and this is for me to improve, is there a more economical or more standard way of writing the code below.
Thanks
Andy
v6.0 Build 766
[codebox]

Sub EditOnLoad ()
set rstmp = Server.CreateObject("ADODB.Recordset")

rstmp.Open "select ActionUName, IsLocked, LockTime from " & strTableName & " where " & strWhereClause, dbConnection
if rs("IsLocked")= True and DateDiff( "n", rs("LockTime"), now() )<3 then

strMessage = strMessage & "RECORD IS LOCKED"

strMessage1 = strMessage1 & "This Record is being Updated By "

strMessage2 = strMessage2 & "Please Try Again in 5 Minuets"

Response.write ("<p>&nbsp;</p>")

Response.write ("<div align=""center"">")

Response.write ("<table border=""1"" width=""41%"" id=""table1"">")

Response.write ("<tr><td>")

Response.write ("<p align=""center""><font face=""Times New Roman"" size=""5""><font color=""#FF0000"">")

Response.write strMessage

Response.write ("<p align=""center""><font face=""Times New Roman"" size=""4"">")

Response.write strMessage1

Response.write ("<font color=""#FF0000"">")

Response.write rs("ActionUName")

Response.write ("<p align=""center""><font face=""Times New Roman"" size=""4"">")

Response.write strMessage2

Response.write ("<p align=""center""><font face=""Times New Roman"" size=""4"">")

Response.write " " & Now()

Response.write ("</p>")

Response.write ("<align=""center"">&nbsp;</td>")

Response.write ("</tr></table></div>")

rstmp.close : set rstmp = nothing

Response.end
else
rstmp.close : set rstmp = nothing

dbConnection.Execute "update " & strTableName & " set IsLocked=1, LockTime=now() where " & strWhereClause
end if
End Sub 'EditOnLoad'
[/codebox]