Urgent Help Needed!! Exclusive Read Access

[Brijesh81]

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

[Robby]

Why not use Identy (autoIncrement) on the ID column?

[Brijesh81]

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]

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]

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]

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