• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1575
  • Last Modified:

Record locking, page locking and table locking of SQL2000 db with vb6

I am using Microsoft SQL 2000 Database with vb6, using ado2.5. To distribute in multiuser environment I need to implement locking features. At certain points I want to lock only one record, sometime a block of records and perhaps the whole table too. Can someone show with code samples how to achive all three options.
0
swtirs
Asked:
swtirs
  • 6
1 Solution
 
spongieCommented:
Hi swtirs. As far as i know locks can only be implemented on the row level. Locktypes would only give you an option when the records are locked (Pessimistic - before editing the row, Optimistic - during update only, others are self-explanatory).  Maybe you're better off using transactions on the connection object.

Refer to this for details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_7jov.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaexamples_vb01_10.asp

Good Luck. :0)
0
 
MYLimCommented:
from MSDN Library:
Specifying Locking
The LockType property or argument specifies what kind of locking is used while you edit records in the Recordset object. Locking is used to regulate what other users in a multiuser database can do with a record while it is being edited. If you don't set the LockType property, read-only locks will be used by default, which will prevent you from editing records. To edit the data in a Recordset object, you must set the LockType property before you open it, or else you must pass the LockType argument to the Open method. The LockType property is read/write before a Recordset object is opened (or after it is closed), and read-only while it is open. The following table lists the constants you can use to set or read the LockType property and describes how each lock functions when you are editing records.

Constant Description
adLockReadOnly Read-only you cannot edit the data. (Default)
adLockPessimistic 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 as soon as you start editing records. No other users can read or edit the data until you either save changes with the Update method or cancel them with the CancelUpdate method.
adLockOptimistic Optimistic locking, record by record the provider locks records only when you call the Update method. Other users can read, edit, and save changes to the same record while you have it open.
adLockBatchOptimistic Optimistic batch updates required for batch update mode as opposed to immediate update mode.  


The following addition to the previous code example specifies optimistic locking, so that the Recordset object can be edited.

rst.Open _
   Source:=strSourceTable, _
   ActiveConnection:=cnn, _
   CursorType:=adOpenKeyset, _
   LockType:=adLockOptimistic

Some OLE DB providers may not support all lock types. If a provider cannot support the requested LockType setting, it will substitute another type of locking. To determine the actual locking functionality available in a Recordset object, you can use the Supports method with adUpdate and adUpdateBatch constants. For more information about the Supports method, see "Determining Cursor Features" later in this chapter.

0
 
MYLimCommented:
more clear:
Constant Description
adLockReadOnly
Read-only you cannot edit the data. (Default)

adLockPessimistic
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 as soon as you start editing records. No other users can read or edit the data until you either save changes with the Update method or cancel them with the CancelUpdate method.

adLockOptimistic
Optimistic locking, record by record the provider locks records only when you call the Update method. Other users can read, edit, and save changes to the same record while you have it open.

adLockBatchOptimistic
Optimistic batch updates required for batch update mode as opposed to immediate update mode.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MYLimCommented:
CursorType, LockType, and EditMode Properties Example (VB)

This example demonstrates setting the CursorType and LockType properties before opening a Recordset. It also shows the value of the EditMode property under various conditions. The EditModeOutput function is required for this procedure to run.

Public Sub EditModeX()

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String

   ' Open recordset with data from Employee table.
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
     
   Set rstEmployees = New ADODB.Recordset
   Set rstEmployees.ActiveConnection = cnn1
   rstEmployees.CursorLocation = adUseClient
   rstEmployees.CursorType = adOpenStatic
   rstEmployees.LockType = adLockBatchOptimistic
   rstEmployees.Open "employee", , , , adCmdTable

   ' Show the EditMode property under different editing
   ' states.
   rstEmployees.AddNew
   rstEmployees!emp_id = "T-T55555M"
   rstEmployees!fname = "temp_fname"
   rstEmployees!lname = "temp_lname"
   EditModeOutput "After AddNew:", rstEmployees.EditMode
   rstEmployees.UpdateBatch
   EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
   rstEmployees!fname = "test"
   EditModeOutput "After Edit:", rstEmployees.EditMode
   rstEmployees.Close
   
   ' Delete new record because this is a demonstration.
   cnn1.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"

End Sub

Public Function EditModeOutput(strTemp As String, _
   intEditMode As Integer)

   ' Print report based on the value of the EditMode
   ' property.
   Debug.Print strTemp
   Debug.Print "  EditMode = ";

   Select Case intEditMode
      Case adEditNone
         Debug.Print "adEditNone"
      Case adEditInProgress
         Debug.Print "adEditInProgress"
      Case adEditAdd
         Debug.Print "adEditAdd"
   End Select

End Function

0
 
MYLimCommented:
U WANT TO LOCK THE RECORD AND DO NOT ALLOW OTHER USER TO EDIT ? RIGHT ?
ALL YOU HAVE TO DO ARE SET LockType to AdLockPesismistic . (this is an bad idea if you want to lock whole table,server will result heavy duty).

EX:you have a tablename TABLE1 and Fieldname MyNUMBER and 5 record inside.
==============
MyNumber
1,2,3,4,5
==============
Just change your String SQL to select record that you want to lock.
1.lock only one record - record will lock = 1:

  rstEmployees.CursorType = adOpenDynamic
  rstEmployees.LockType = adLockPesimistic
  rstEmployees.Open "select * from Table1 where MyNumber = 1", , , , adCmdText


2.lock a block of record-record will lock = 3:

  rstEmployees.CursorType = adOpenDynamic
  rstEmployees.LockType = adLockPesimistic
  rstEmployees.Open "select * from Table1 where MyNumber >=3", , , , adCmdText

3.lock whole table-records will lock = every record inside TABLE1 :

  rstEmployees.CursorType = adOpenDynamic
  rstEmployees.LockType = adLockPesimistic
  rstEmployees.Open "select * from Table1", , , , adCmdText
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now