SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO & SQL Server

RowLock.exe is a sample file that demonstrates how to use Microsoft ActiveX Data Objects (ADO) with Microsoft SQL Server in a Microsoft Visual Basic application to lock individual rows within a recordset while they are being changed.

The following file is available for download from the Microsoft Download Center:
Release Date: May-17-2000

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The sample opens a recordset from the pubs database and allows the user to browse the records. The user can then update any of the fields if required. If a second client connects, they can browse the recordset and edit any of the records apart from the one being edited by the first client. If both clients try to edit the same record, a message box with this error message displays: Run the sample and browse the recordset by using the navigation buttons. Click the Begin Tran button to start editing a record and then either click the Rollback or Commit button to discard or save your changes.

Note Pressing any navigation button during a transaction will rollback any changes.

Run two instances of the sample and browse the recordset by using both clients. Browse to a record and begin a transaction with the first client. Now, try to begin a transaction on the same record with the second client. A message box appears, which states that the row is currently locked. Browse to a different record with the second client, begin a transaction, make some changes and then either rollback or commit the changes. Rollback or commit the changes on the first client.

The sample works by opening an optimistic server-side cursor on the recordset. This allows free browsing of the recordset. When the user begins editing (starts a transaction), a pessimistic cursor opens on the recordset at the appropriate record, locking only that record. A rollback or commit selection releases the lock and opens an optimistic cursor again.

Notes

• After opening a pessimistic, server-side cursor, the cursor sits just before the rowset. A Move operation was used to get the cursor into the rowset and obtain the lock.
  
  
• When you use ADO, a pessimistic lock can only be obtained on a server-side cursor. If you request a pessimistic lock on a client-side cursor you will get an optimistic lock by default. Please see the following article in the Microsoft Knowledge Base for more information:
  
  
  Q189853  (http://support.microsoft.com/kb/Q189853/EN-US/ ) PRB: Data Environment: Error Setting Lock Type
• Note You must change the uid=<username> value and the pwd=<strong password> value to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database. The connection string is in the following code:

  Private Sub Form_Load()
  
  Dim strConnect As String
  GboolTrans = False
  commitRollback False
  'set strConnect to your requirements: strConnect = "driver={SQL Server};server=(local); uid=<username>;pwd=<strong password>;database=pubs"
  .
  .
  .
  End Sub