Click here to Skip to main content
15,845,853 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,

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.
Posted
Updated 15-Feb-11 19:52pm
v3
Comments
JF2015 16-Feb-11 0:52am    
Added code formatting.

1 solution

Most of the time 2 users get the primary key at the same time.

This is because you're code is generating it's own primary keys. This should be left up to the database. Insert a new record in the database (without the key value) and one or more required fields and let the database assign the key value. Retrieve that with an appropriate IDENTITY and finish your INSERTS or UPDATES of that record using the assigned key. Read more on it here[^].

Locking tables is frowned upon because it turns you're multi user app into a signle user app until the lock is gone. If your app crashes before the lock is freed, your database table is locked until an admin goes and clears it manually.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900