Row Level Locking Problem - Important

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  
jatinderbahlAsked:
Who is Participating?
 
wsteegmansConnect With a Mentor Commented:
jatinderbahl,

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

Regards!
0
 
rajaamirapuCommented:
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
 
wsteegmansCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
MaxPolCommented:
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
 
jatinderbahlAuthor Commented:
hi


   i alrady downloaded RowLock.exe and i am going through it. rest i am also trying wsteegmans
 approach .  
0
 
jatinderbahlAuthor Commented:
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
 
jatinderbahlAuthor Commented:
hi

   any body to respond
0
 
jatinderbahlAuthor Commented:
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
 
jatinderbahlAuthor Commented:
hi

   i got the solution and thanks to  wsteegmans and rajaamirapu for discussion.
0
All Courses

From novice to tech pro — start learning today.