Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
enak
Starting Member
34 Posts |
 Posted - 2007-02-07 : 12:09:09
|
| I have an application that allows the user to enter data into a table. There are multiple users so I put in some code 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.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 tableI add 1 to the ID and then insert a new record with the new ID into the table.Any help is appreciated.Thanks,enak |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 12:30:13
|
| "Is there anyway on the database that I can prevent this from happening?"Create a UNIQUE INDEX (or constraint) on the column(s)You could use an IDENTITY for the ID - assuming it is numeric - and then the system would allocate it sequentially - however if a record fails to insert there will be a gap in the Identity sequence, which won't arise with your MAX(ID)+1 approach.Kristen |
 |
|
|
Tks
Starting Member
48 Posts |
Posted - 2007-02-07 : 12:49:25
|
| i always add the time stamp to the id string until mili seconds...also you could add the userlogin ID unique index is offcourse the best one.Tks |
|
|
|
enak
Starting Member
34 Posts |
Posted - 2007-02-07 : 12:50:59
|
| The ID column is not an identity since I create the number myself. Is there a way to setup an identity column so that it is formatted the way I need it to be formatted (####-mmyy)?What would I do for the constraints?Thanks for your reply. |
|
|
|
rcurrey
Starting Member
30 Posts |
Posted - 2007-02-07 : 15:27:03
|
| Are you getting the MAX(id) in a separate query? If so, you might want to use a subquery as a part of the insert statement, that should allow recordlocking to control the creation of 2 records at the same time.insert TABLE1 (col1,col2,col3 ...)select (select max(id) from table1),val2,val3 ....Thanks,Rich |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-08 : 07:42:25
|
| "Is there a way to setup an identity column so that it is formatted the way I need it to be formatted"No, but you could have two columns - Identity and then your "mmyy" is another column (and they can be joined together for any Outputs you need - e.g. using a computed column, or VIEW.However, the Identity will be a continuous sequence, not reset when mmyy changes ...A UNIQUE INDEX / CONSTRAINT will at least cause the system to fail when an attempt is made to insert a duplicate, and if you intend that that column(s) is unique you should really enforce at the database level anyway - even if only to save your application from itself - e.g. some future bug!Kristen |
|
|
|
|
|
|
|
|
Duplicate entries