ASP Access Record Locking

Results 1 to 2 of 2

Thread: ASP Access Record Locking

  1. #1
    Jon Guest

    Default ASP Access Record Locking

    Hi everyone, I am currently writing a web app which will allow users to perform a lot of database updates unfortunately to an Access database, with nasty page locking.<BR>I just want to get some feedback on a locking stratergy Im thinking of implementing.<BR><BR>Since ASP is stateless, and I cant lock a table/record when a user hits an &#039update&#039 button, I have introduced a time stamp system. On each table I have &#039Updated&#039 (date/time) and &#039UpdatedBy&#039 (long user Recid) fields which are set during the update processing.<BR>The code below shows an exert from my general update asp for some of the small static data tables...<BR><BR>After openning the update recordset, the code checks to see if the record has been updated by another user within the last 30 seconds, if so the user is redirected to a warning page showing who updated the record and when, he/she can then go back and make their changes anyway, or see the new changes as made by the other user.<BR><BR>Let me know if you can see anything dodgy about the idea, as Im sure Ive probably missed something important...<BR><BR>< BR><BR>Else<BR> &#039Update Existing Record....<BR><BR> SQLtxt = "SELECT * FROM " & sTable & " WHERE Company = " & sCompany _<BR> & " AND RecId = " & sRecId<BR><BR> Set objRS = Server.CreateObject("ADODB.Recordset")<BR> objRS.CursorLocation = adUseClient<BR> objRS.CursorType = adOpenDynamic<BR> objRS.LockType = adLockOptimistic<BR> objRS.Open SQltxt, objConn<BR><BR> If Err &#060;&#062; 0 Then<BR> objRS.Close<BR> Set objRS = Nothing<BR> objConn.Close<BR> Set objConn = Nothing<BR><BR> Response.Redirect "ErrorData.asp?Error=" & Server.HTMLEncode(Err.Description) _<BR> & "&Number=" & CStr(Err) _<BR> & "&Page=" & Server.HTMLEncode(sNextPage) _<BR> & "&Source=" & Server.HTMLEncode(Err.Source)<BR><BR> End If<BR><BR> If objRS.BOF = False And objRS.EOF = False Then<BR><BR> If IsNull(objRS("Updated")) = False And IsNull(objRS("UpdatedBy")) = False Then<BR> If sUserId &#060;&#062; "" Then<BR> If objRS("UpdatedBy") &#060;&#062; sUserId Then<BR> If DateValue(Now) = DateValue(objRS("Updated")) Then<BR> If DateDiff("s",objRS("Updated"),Now) &#060; 30 Then<BR><BR> sUpdated = objRS("Updated")<BR> sUpdatedBy = objRS("UpdatedBy")<BR><BR> objRS.Close<BR> Set objRS = Nothing<BR> objConn.Close<BR> Set objConn = Nothing<BR><BR> Response.Redirect "ErrorData.asp?UpdatedBy=" & Server.HTMLEncode(sUpdatedBy) _<BR> & "&Updated=" & Server.HTMLEncode(sUpdated) _<BR> & "&Page=" & Server.HTMLEncode(sNextPage)<BR> End If<BR> End If<BR> End If<BR> End If<BR> End If<BR><BR> objRS("Description") = sDescription<BR><BR> If sTable ="Rates" Then<BR> objRS("Rate") = sRate<BR><BR> ElseIf sTable = "ContactFrequency" Then<BR> objRS("Frequency") = sFreq<BR><BR> End If<BR><BR> objRS("Updated") = Now<BR> If Session("UserId") &#060;&#062; "" Then<BR> objRS("UpdatedBy") = Session("UserId")<BR> End If<BR><BR> objRS.Update<BR><BR> If Err &#060;&#062; 0 Then<BR> objRS.Close<BR> Set objRS = Nothing<BR> objConn.Close<BR> Set objConn = Nothing<BR><BR> Response.Redirect "ErrorData.asp?Error=" & Server.HTMLEncode(Err.Description) & SQLtxt _<BR> & "&Number=" & CStr(Err) _<BR> & "&Page=" & Server.HTMLEncode(sNextPage) _<BR> & "&Source=" & Server.HTMLEncode(Err.Source)<BR><BR> End If<BR> Else<BR> objRS.Close<BR> Set objRS = Nothing<BR> objConn.Close<BR> Set objConn = Nothing<BR><BR> sMessage = "Record Has Been Deleted By Another User!"<BR><BR> Response.Redirect "ErrorData.asp?Error=" & Server.HTMLEncode(Err.Description) _<BR> & "&Number=" & CStr(Err) _<BR> & "&Page=" & Server.HTMLEncode(sNextPage) _<BR> & "&Source=" & Server.HTMLEncode(Err.Source) _<BR> & "&Message=" & Server.HTMLEncode(sMessage)<BR> End If<BR> objRS.Close<BR> Set objRS = Nothing<BR><BR> End If<BR>

  2. #2
    Join Date
    Dec 1969

    Default Pessimistic

    With CurrentSolution<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;D odgy = "Yep"<BR>End With<BR><BR>Sorry just some quick humour ..... haw, haw, haw.<BR><BR>You don&#039t need to implement your own &#039table-locking&#039 system ADO can help you there. Look:<BR><BR>Dim cnn<BR>Dim rst<BR><BR>Const adOpenKeyset = 1<BR>Const adLockPessimistic = 2 &#039&#060;---- Pessimistic Locking<BR>Const adCmdTable = &H0002<BR><BR><BR>Set cnn = Server.CreateObject("ADODB.Connection")<BR>Set rst = Server.CreateObject("ADODB.Recordset")<BR><BR>dbSt ring = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<BR> "Data Source=" & Server.MapPath ("student.mdb") & ";" & _<BR> "Jet OLEDB:Engine Type=5;"<BR> <BR>cnn.Open dbString<BR><BR>rst.Open "Books", dbString, adOpenKeyset, adLockPessimistic, adCmdTable<BR><BR>Do Until rst.EOF<BR><BR> Response.Write rst(0)<BR> rst.MoveNext<BR> <BR>Loop<BR><BR>Using Access this is about as good as you&#039ll get (although I think you can apply the:<BR><BR>Const adModeShareDenyWrite = 8<BR><BR>property to the Connection Object which prevents all other users modifying the records contained in the recordset until the recordset is closed.<BR><BR><BR>HTH<BR><BR>Darren<BR>[ ]<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts