[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Row Level Locking Problem - Important

Posted on 2003-11-27
9
Medium Priority
?
445 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 

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 750 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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.
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…
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…
Suggested Courses
Course of the Month17 days, 17 hours left to enroll

831 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