Avoiding duplicate entries in SQL database

[enak]

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

 

[mtaylor314]

I don't think this is so much a database issue but maybe something that needs to change in the code. Since multiple users are logging in at the same time I am assuming that you are using a multi-threaded application. The code that is used to call the stored procedure should probably be in a

synclock

box.

 

[enak]

It is not a multithreaded app. It is a web based app that runs internally.

 

[cjard]

Read up on what an IDENTITY field is. If youre bothered about formatting issues, reconsider your primary key or format the identity later on.. I.e. have 2 columns, one IDENTITY and the other that is YYYYMM, and just join them (ID and format)


In oracle, we have sequences, that we can format how we like, so we dont suffer this problem. There are a few articles out there about how to mimick sequences in SqlServer, or you can upgrade your database to OracleExpress

 

[enak]

Duh! Thanks for the help. Sometimes you can't see the trees for the forrest.

 

[cjard]

i always thought it was "see the wood for the trees" given that a wood is a collection of trees and also wood is what trees are made of.. ya know