Solved

Row Level Locking Problem - Important

Posted on 2003-11-27
9
436 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

932 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

13 Experts available now in Live!

Get 1:1 Help Now