Duplicate entries

I have an application that allows the user to enter data into a table. 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 really hope that this makes sense.


Any help is appreciated.

Thanks,
enak

The proper way to do this is have the program obtain a read lock on the table first. That way, only one thread can read or write the table. It will read the last record, store the new one, and then it will have to release the lock. The second thread will have to wait till the first one is finished, and will see the new record as the last one.

Have a look here as well.

This sounds like what I am looking for. However, I have searched high and low and can not find any info on HOW to do this. Can you please provide an example on how to do this? Do I do it in my vb.net code?

Thanks for the help.