hi i am using vs 2005 asp.net c#. there is a database in which i perform the select max(col) operation in sql, how can i lock the database such that when 1 user is performing this operation, others will not be able to access the page( carry out insert operation) or be redirected to some waiting page? pls hlp

Regards,
Bharat Shivram

please reply

You have at least two choices:
1. use the lock(...) in C#, or use SqlTransaction (http://www.knowdotnet.com/articles/transactions.html) ; there should be only one instance of the application accessing the db. If you cannot guarantee that then
2. use BEGIN TRAN, COMMIT, ROLLBACK in SQL.
Read about all these and learn how to use them, copy & paste is not a good way to solve any such problem.
Wish you success!

actually what i am doin is first of all findind the max value of a col ( postid) and inserting a new row that has the value max (postid) +1.. postid is a primary key.. so if more than 1 user reads this value as "x" and adds 1 to it and tries to insert it then there will be a violation of the primary key.. as both of them would be inserting the value (x+1).. i want to lock this table when any user reads this value and inserts a record.. i have concurrency control in asp.net but i am using native c# code so how do i achieve this ?

don't do that! there are at least two other options SQL server offers:
1. use autoincrement fields, or
2. use guids
In either case the server will handle the creation of new value for a PK.

hi i am using vs 2005 asp.net c#. there is a database in which i perform the select max(col) operation in sql, how can i lock the database such that when 1 user is performing this operation, others will not be able to access the page( carry out insert operation) or be redirected to some waiting page? pls hlp

Regards,
Bharat Shivram

Hi bharat

You can Define an Identity column (postID) and increment by 1 in your Db table, and rest it will takecare by it self.

Step
1) Select the Column of your table (PostID)
2) In Column Property, set Is Identity = true under Identity Specification
3) set Identity Seed = 1

Remove the column PostID from you Insert Query and to retrive the New Value use select Scope_Identity

Mark as solved it it helps you!!!!

This article has been dead for over six months. Start a new discussion instead.