Solved

Database locking

Posted on 1997-12-28
18
232 Views
Last Modified: 2006-11-17
How can I give the computer to check whether this record was locked by other users or not? I just know how to lock record but I need to know that when other user lock record, how can I check that it was lock or unavailable?
0
Comment
Question by:Mcblake
  • 6
  • 5
  • 2
  • +4
18 Comments
 
LVL 6

Expert Comment

by:anthonyc
Comment Utility
I don't think 10 points is enough for this question.  Explaining database locking and how to work with it is not worth the time.
0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
I'll agree... 10 points questions are questions that can be answered in 2-3 words max.  Mcblake, point values at Experts-Exchange are based on a question difficulty.  Common point values for questions for various difficulties are as follows:

  25: Very Easy
  50: Easy
100: Medium
150: and up Hard


0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
Try setting the Exclusive property(or smth like it, I don't remember the name exactly) to true. If you get an error then it's locked by some other application.

Matvey
0
 
LVL 6

Expert Comment

by:anthonyc
Comment Utility
Sorry, I've misunderstood your question.
I haven't worked dmuch with multiuser databases...

Matvey
0
 
LVL 3

Expert Comment

by:Matvey
Comment Utility
I wanna know about 'record' locking not database locking.
Thanks
0
 

Author Comment

by:Mcblake
Comment Utility
What is your environment? VB4, VB5? Access/Jet, xBase, some SQL server?
0
 
LVL 4

Expert Comment

by:rantanen
Comment Utility
The only sure way to check in all environments is to try to wirte the row.  If using Jett to remote SQL databases, all locking is done with optimisitc concurrency, rows are only checked for conflict at update time.

The current Microsoft Databases (JET/Access and SQL Server) only perform page locking.  Any row selected for update using and updateable cursor will lock the entire page on which that row is contained.  Mltiple row recordsets lock multiple pages.  Order by and large resultsets can be escalated to entire table locks without your knowing it by the server.  IMHO, MS has provided a fairly weak solution to a strong problem, but promises to implement row level locking "real soon now"
0
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
My database information is Foxpro as my database. I use Visual basic 4.0 (16-bit version). I have plan to install my application in each workstation and share only database on the server. Anyway, I would like to know what command to check whether the required record was locked by other user. So, I can create some other option when the require record can't lock for update because it was locked by other user.
0
 

Author Comment

by:Mcblake
Comment Utility
you cant in simple terms.
What you need to do is when you open the record to lock it, write to a field on that record the user id that locked the record. When the reocord become unlocked, clean that field and it will show available.
Thus - simplist way is to program it into your system.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Expert Comment

by:staffiidba
Comment Utility
McBlake, why don't you try error traping, for example, create a function called IsLocked that you can pass the recordset, to and...


On Error Goto CheckError

'Try to edit a specific field in a specific record - but don't actually edit or update the record.
IsLocked = False
Exit Function

CheckError:
'If this is executed - check if err is one associated with attempting to edit a locked record - if so
IsLocked = True
else: You choose, looks as if the record can't be edited anyway, might as well be IsLocked = True
Exit Function
End Function

0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
The code below is correct, and the microsoft recommended way to handle database locking.  It is Cut/Pasted from the Mastering VB5 CD, so I know it's correct.  I think I deserve more than 70 points for this too.

This code handles locking errors on the edit command, assuming an edit button was pushed:

Private Sub cmdEdit_Click()
      On Error GoTo HandleError
      rs.Edit
      ButtonEditAddMode
      Exit Sub
HandleError:
      Select Case Err.Number
      Case 3260 'page currently locked
            MsgBox "Record is currently locked. Try again later."
      Case 3197 'data has changed
            MsgBox "Data has been changed and will be refreshed."
            rs.Bookmark = rs.Bookmark 'get updated data
            FillFields
            rs.Edit
      Case Else
            MsgBox Err.Number & ": " & Err.Description
      End Select
End Sub


This code handles errors from the update command
Private Sub cmdSave_Click()
      Dim answer As Integer
      On Error GoTo HandleError
      rs.Fields("Category Name") = txtCategoryName.Text
      rs.Fields("Description") = txtDescription.Text
      rs.Update
      rs.Bookmark = rs.LastModified
      FillFields
      ButtonNavigateMode
      Exit Sub
HandleError:
      Select Case Err.Number
      Case 3260
            MsgBox "Record is currently locked. " & _
                  "Try Save again later or cancel changes."
      Case 3197
            answer = MsgBox("Data has been changed by another user. " & _
                  "Overwrite changes?", vbYesNo)
            If answer = vbYes Then
                  Resume
            Else
                  rs.Bookmark = rs.Bookmark 'refresh w/other users changes
                  cmdCancel_Click
            End If
      Case Else
            MsgBox Err.Number & ": " & Err.Description
      End Select
End Sub
 

This handles an error on a deleted record by another user:
Private Sub cmdMoveFirst_Click()
      On Error GoTo err_movefirst:
      retry_MoveFirst:
      rs.MoveFirst
      txtLName.Text = rs("Last Name")      'Error may occur
      Exit Sub
err_Movefirst:
      If Err.Number = 3167 Then
            'record is deleted,
            'remove entry from your dynaset
            rs.Delete
            Resume retry_MoveFirst
      Else
            MsgBox Err.Description
            Exit Sub
      End If
End Sub
 

0
 
LVL 6

Accepted Solution

by:
anthonyc earned 70 total points
Comment Utility
As far as checking if a record is locked - which is/was the question - This is no different from my suggestion.
0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
mrmick:

I pretty much agree.  This solution may be a little more robust, where your solution considered every error a database locking error, which may not be the case.  However, an exact example may answer his question better
0
 
LVL 6

Expert Comment

by:anthonyc
Comment Utility
No anthony, I said check only for a locked error - which would satisfy the question on hand.  Then I suggested that if there were any error, might as well treat it as a locked record because it was unavailable for edit.  I suggested this because of the last two words of the question are "or unavailable".

Would your solution work for RDO?
0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
whoops, I meant to ask: what is "ButtonEditAddMode"?
0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
Mcblake,

Please comment whenever you reject an answer so the person offering you a solution will understand how he/she  misinterpreted your question or learn why the solution doesn't work for you.
0
 
LVL 8

Expert Comment

by:mrmick
Comment Utility
I don't agree with treating all errors like locked records.  You can make debugging and support of your application more difficult than it needs to be.
0
 
LVL 6

Expert Comment

by:anthonyc
Comment Utility
anthonyc, an error at the point in question means that the record is unavailable for edit for whatever reason, this is the reason for my suggestion.  Sure, there's no reason why you couldn't invoke a msgbox displaying the error that has caused the condition - and if I took a look at the project, there may be other suggestions I could make, but for now, I addressed the question at hand.

Mcblake was simply asking "How to determine weather a record was LOCKED or UNAVAILABLE?".  Do you see what I'm saying?

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now