Solved

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

Posted on 2003-11-27
7
1,527 Views
Last Modified: 2013-12-25
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
Comment
Question by:swtirs
  • 6
7 Comments
 
LVL 8

Expert Comment

by:spongie
ID: 9832337
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9834648
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9834653
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:MYLim
ID: 9834667
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9834691
0
 
LVL 8

Accepted Solution

by:
MYLim earned 200 total points
ID: 9834720
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9879100
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 (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 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

9 Experts available now in Live!

Get 1:1 Help Now