lock some special records in a table in sqlserver

Question

0.00/5 (No votes)

See more:

C#

Hi How can I lock some special records in a table in sqlserver to prevent modifying whit c# code,
thanks in advance.

Posted 16-Sep-14 21:02pm

Angela 10293848

Add a Solution

Comments

BharatPrajapati212 17-Sep-14 3:28am

simple just make the trigger for update for preventing update in table for your special record

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2014-09-16T21:44:00

Solution 1

SQL does not support explicit lock using some commands (all the locks are done by the server according the needs of the running command)...
The best thing you can do is apply triggers on DML events and cancel changes via those triggers... http://technet.microsoft.com/en-us/library/ms191524(v=sql.105).aspx[^]

Posted 16-Sep-14 21:44pm

Kornfeld Eliyahu Peter

Comments

Angela 10293848 17-Sep-14 4:30am

my sql works in local networks and some user can update it,I want to when a user in updating a record other user not be able to modify it I used the following code but it lock all recoreds not just a recored
begin tran select * from Table_AR_Blok WITH (HOLDLOCK, ROWLOCK) where Id='" + TId.Text.Trim() + "'

Kornfeld Eliyahu Peter 17-Sep-14 4:33am

I do not understand you. What do you mean by 'when user updating'? At that very moment?
You additions to the select statement are only hints to the query optimizer and has no effect of locking anything outside the select itself...(and maybe not even inside)...

Angela 10293848 17-Sep-14 4:40am

I have a button to select when I click this button, data are shown in textboxes and I change the data then I update these data with another button I mean that I do not want to allow other user be able to change this record in this duration(from select up to click second button)

Kornfeld Eliyahu Peter 17-Sep-14 4:46am

This called 'concurrency control' and can not be done in pure SQL, you need some help from the application...
You may read here: http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.sql.doc/doc/the.purpose.of.concurrency.control.html

Angela 10293848 17-Sep-14 5:07am

ok but the question is when I lock Id=1 it locks all records Id=1,2,3,
why?

Kornfeld Eliyahu Peter 17-Sep-14 5:11am

I told you! It's only a hint and event it accepted by the server it last only as long as the select statement runs...

Angela 10293848 17-Sep-14 5:58am

thanks Peter