Urgent Help Needed!! Exclusive Read Access

Brijesh81

Freshman
Joined
Jul 2, 2003
Location
New Delhi
am working on an ASP.Net application with SQL Server 2000 as the back-end server.

I need to calculate the MAX value of the primary key column of a table say TABLE_1 and then display MAX+1 for the new reord on the screen...which will be the primary key for the new record which is presently being edited by the user.

To maintain the uniqueness of the primary key, I need to retrieve the unique values of MAX from the database for the concurrent users. I am maintaining another Table TABLE_APP_COMMON for this purpose which conains a COLUMN namely MAX_KEY_TABLE_1 containing the maximum value used by the primary key field of
the table TABLE_1.

My problem is that if 2 users read this table TABLE_APP_COMMON simultaneously then they would get the same value for the
column MAX_KEY_TABLE_1. In order to avoid this I need to give exclusive read access to the users for the table
TABLE_APP_COMMON.

Could anybody tell me how to achieve this in ASP.Net, ADO.Net, SQL Server 2000.

Any help is greatly appreciated.

Thanks a lot in advance
 

Brijesh81

Freshman
Joined
Jul 2, 2003
Location
New Delhi
Hi Robby,

Thanks for your response.

Problem with using the AutoIncrement is that the user has to be shown the value of Primary Key on the screen when he starts editing the rest of the field values on the screen, and later he might decide not to save this record.

If we use the autoincrement then the following situation may occur--->
One user wants to create a record. I access the table and inserts a new record using the autoincrement feature. A new record with primary key value 50 (for example) will be inserted in the database, user will be shown this value 50 on the screen, he starts inputting other field values for the record and then if he decides not to save the record then the record just created in the database with the primary key value 50 will go waste, because it does not correspond to any valid user entry.

....or am I missing something??

Please explain..

Thanks & Regards,
--Brijesh
 

Robby

Ultimate Contributor
Joined
Nov 17, 2002
Location
Montreal, Ca.
With SQL Server the number is created only when the row is saved/inserted. So yes, you cannot know what that number is until the new row is inserted.

What is the PK used for elsewhere on the screen, is it part of another field, perhaps you can circumvent this approach.
 

Derek Stone

Exalted One
Joined
Nov 17, 2002
Location
Rhode Island, USA
Does it really matter if the primary key value "goes to waste"? That's the question that needs to be asked. It's nice to see neatly incremented values, but in the real world things don't work that way. You should consider Robby's circumvention statement, above.
 

Brijesh81

Freshman
Joined
Jul 2, 2003
Location
New Delhi
Hi Robby and Derek,

Thanks for your valuable inputs.

Yes, I just wanted to have neatly incremented primary key values. But yes it really does not matter much if it goes waste, also because the primary key value will not be used as part of any other field, as Robby pointed out.

I will adopt this approach.

Thanks a lot,
Regards
 
Top Bottom