I have an application, written in vb.net 2005, that allows the user to enter data into a table (MS SQL Server 2000). There are multiple users so I put in some code in the frontend that, I thought, would keep 2 users from creating a new record at the same time. The IDs for the records are identical and this is causing a problem.
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1. Because of the format of the ID I can not make it an identity field.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table from the last record created
I add 1 to the ID and then insert a new record with the new ID into the table.
But what is happening is that when the 2 users try to add a new record both users are grabbing the last ID before the first user to execute the query can insert a new ID.
For example:
Current last record = 1
User1 and User2 click New. The system goes to the table and both users get ID = 1. The system then adds 1 to the ID (1 + 1 = 2) and inserts and new ID of 2 for User1 and a new ID for User2.
I know that they are not getting to the table at the same time so what I need is for the first user to start the process to be able to lock the second user out until the first user has inserted a new record. That way the second user will get the new ID as the current ID.
Currently, I am not using stored procedures to do this. Should I?
I was told by someone else that I should use exclusive locks at the page level on the table. He did not tell me how to do it. I guess that he thought that I knew. Can anyone out there tell if this is the best way and if so, how to do this?
I really hope that this makes sense.
Any help is appreciated.
Thanks,
enak
The IDs are in the format of ####-mmyy. at the start of each month the #### part goes back to 1. Because of the format of the ID I can not make it an identity field.
We tried a test today where we had 2 users click on the New button at exactly the same time. The IDs that were created were identical. Is there anyway on the database that I can prevent this from happening?
Here is how I create the new record id:
I get the MAX(ID) from the table from the last record created
I add 1 to the ID and then insert a new record with the new ID into the table.
But what is happening is that when the 2 users try to add a new record both users are grabbing the last ID before the first user to execute the query can insert a new ID.
For example:
Current last record = 1
User1 and User2 click New. The system goes to the table and both users get ID = 1. The system then adds 1 to the ID (1 + 1 = 2) and inserts and new ID of 2 for User1 and a new ID for User2.
I know that they are not getting to the table at the same time so what I need is for the first user to start the process to be able to lock the second user out until the first user has inserted a new record. That way the second user will get the new ID as the current ID.
Currently, I am not using stored procedures to do this. Should I?
I was told by someone else that I should use exclusive locks at the page level on the table. He did not tell me how to do it. I guess that he thought that I knew. Can anyone out there tell if this is the best way and if so, how to do this?
I really hope that this makes sense.
Any help is appreciated.
Thanks,
enak