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.
swtirsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)
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.

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.  
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.