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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
wsteegmansCommented:
jatinderbahl,

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

Regards!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.