Solved

Row Level Locking Problem - Important

Posted on 2003-11-27
9
435 Views
Last Modified: 2012-05-05
hi

   This is multi user app. Now what i want when one user open record in edit/delete mode , other user should get the error msg the record is locked by other user. I have a table structure i.e

  EmpMaster ---- Master Records of Employees  -- Primary Key
  EmpLog ---- Records Attendance of Employees -- Foreign Key


   I want when 'A' user click on Edit/Delete Button , particular Record from the above tables should be locked on server. And When the 'B' User Want to delete/modify same record an error should be raised , but the user can view the record. How can i do this.


  i am using Vb6 with Ado 2.7 and Sql Server 7.0.

  i have heared the Row Level Locking Can also be done on Sql Server.


Thxs  
0
Comment
Question by:jatinderbahl
9 Comments
 
LVL 5

Expert Comment

by:rajaamirapu
ID: 9831171
See the link http://download.microsoft.com/download/ado/Utility/2.5/WIN98/EN-US/RowLock.exe
This is from MSDN
SAMPLE: Rowlock.exe Demonstrates Row-Level Locking Using ADO & SQL Server
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9831937
You could indeed do it on SQL-Server level, with the <table_hint> part in the FROM clause:

Table Hint Syntax

SYNTAX
[ FROM { < table_source > } [ ,...n ] ]

< table_source > ::=
    table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]
   
< table_hint > ::=
    { INDEX ( index_val [ ,...n ] )
        | FASTFIRSTROW
        | HOLDLOCK
        | NOLOCK
        | PAGLOCK
        | READCOMMITTED
        | READPAST
        | READUNCOMMITTED
        | REPEATABLEREAD
        | ROWLOCK
        | SERIALIZABLE
        | TABLOCK
        | TABLOCKX
        | UPDLOCK
        | XLOCK
    }

There are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level.
More info: http://www.sql-server-performance.com/rd_table_hints.asp
0
 
LVL 2

Expert Comment

by:MaxPol
ID: 9832067
Hi,

I think it should work already like that.

If it is not, then it is strange.

The important is that you open a recordset in Edit mode and using LockEdit=true (or false, try the two).

Then, whenever you try to open it again in edit mode, it should give the error and then you open it just for reading.

Bye,

Max



0
 

Author Comment

by:jatinderbahl
ID: 9835607
hi


   i alrady downloaded RowLock.exe and i am going through it. rest i am also trying wsteegmans
 approach .  
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jatinderbahl
ID: 9837747
Hi rajaamirapu


    i tried RowLock It works fine but when i try whole thing in my project nothing happens. Is there any some sort of setting on Server Side.

Hi  wsteegmans

   I tried the synatx On USer A like this
  rs.open "Select * from Author With (ROWLOCK)"
 But nothing happens to User B , no error generated. what is wrong.....
0
 

Author Comment

by:jatinderbahl
ID: 9841578
hi

   any body to respond
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 250 total points
ID: 9841855
jatinderbahl,

How/Where did you use this ROWLOCK? Because, I think you must always use it together with a TransAction ...

Regards!
0
 

Author Comment

by:jatinderbahl
ID: 9857066
hi  wsteegmans


    i write the same code as written in RowLock.exe , but the original code runs fine whereas my code does not reflect the desired results.
0
 

Author Comment

by:jatinderbahl
ID: 9865001
hi

   i got the solution and thanks to  wsteegmans and rajaamirapu for discussion.
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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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

19 Experts available now in Live!

Get 1:1 Help Now