I have multiple users and more than 50K of records.

I have multiple users inserting records at the same in one table throughout the day and most of the time data got mixed up. I have a header/detail entry form web page.

My insert occurs when the users click the Submit button, it will SELECT the next available number from DB and use it as my Primary key to insert record (header/detail).
Most of the time 2 users get the primary key at the same time.

If you could redirect me to the site with has the solution in locking or help me in my problem

My Solution but sometimes it failed.
bool blnExist = false;
while (!blnExist)
{
  string sKdey = PopulateDraftNumber();
  if (IfKeyExists(sKdey))
  {
    blnExist = false; //found record, reselect another key
  }
  else
   {
    blnExist = true; //found no record insert into table
    lblRequestNo.Text = sKdey;
    INSERT RECORD INTO TABLE
   }
}

1- Ask the server for a key

This is when users get two 1 key for their respective records.,

2- Attach that key to my Record
-- I WANT SOME SORT OF LOCKING HERE ON THIS PART BEFORE STEP 3 ---
3- Send record to server - ERROR PRIMARY KEY CONSTRAINTS.

Dabuskol

Hi,

I would avoid trying to implement some sort of locking in favour of some of the built in tools that databases provide.

For example, look at sequences for oracle databases, not sure if they exist in sql server.

With these you would say something like 'insert into {table} (id, text) values ({sequence}.nextval, 'test')

The sequence auto locks so it can't give out the same id twice.

This would get around your issue.

Regards

Simon C

For info, the equivalent of a sequence in SQL Server is an identity column

http://msdn.microsoft.com/en-us/library/aa933196(SQL.80).aspx