| Author |
Topic |
|
pser
Starting Member
15 Posts |
 Posted - 2010-10-22 : 03:20:29
|
| Hi,I am facing a challenge I am not sure how to solve and I hope this forum could help point me in right direction. I am using a SQL server 2008 and is creating a Access 2007 ADP project (front end) application. I have a among several tables a main table in the application named TableA where Primary KEY id named ReferenceNumber (int auto increment). When a user want to create a new registration to this table, he click new, the form loads and I have until now used SELECT MAX to get highest number and then add 1 and show that value in the textbox Reference number on the form. This is all good when you have one user, but if we get two or more users and they create a new registration the reference number (primary key) will be according to who click first, but what is saved in the database will depends on who saved first, next and so on. Then the Reference number won't match what they initially set when filling out the form.The only solution I so far can think of is that when a user click new registration, perform an empty insert to reserve that ReferenceNumber (primary key), then when saving do an update. In this solution all will be good. However I have 87 fields in that table with a lot of datetime fields and so on and inserting a empty record reserving the next available reference number would be a problem due to foreign keys refering to other tables. But I could solve this by creating a dummy data in other tables, but I want to avoid this.So how would you approach this challenge?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-22 : 03:34:09
|
Why do you need the ReferenceNumber (int auto increment) before the data is saved?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
pser
Starting Member
15 Posts |
Posted - 2010-10-22 : 03:37:47
|
| I need it for the user since this registration is a process that involve several user and the user will pass this on to another user that will do his/her part of the registration. You can say that this first registration is a step through 9 steps which involves several user with different rights which will have different task on that initial registration. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-22 : 04:54:03
|
quote:
the user will pass this on to another user that will do his/her part
How is the user passing the ReferenceNumber to another user?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
pser
Starting Member
15 Posts |
Posted - 2010-10-22 : 05:11:02
|
| Well, first of all this database solution is a temporary solution which gonna last for a certain amount of time. So everything is going to be basic. Later, data in this database will me migrated into a larger system which will handle workflow with notification on changes and so on within the application. So, in the meantime when a user create a registration he would send an email with the reference (prim key) to notify next person responsible in chain and so on. It is complicated and for a permanent solution I would have done some differently, but I have been asked to create a basic temporary solution with a short deadline and are very soon completed. But this issue is causing some challenges. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-22 : 05:20:02
|
Is it an option to not have ReferenceNumber as auto increment?So you could for example use a free_number_table instead to get a unique value?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
pser
Starting Member
15 Posts |
Posted - 2010-10-22 : 06:06:49
|
| Hmmm wonder why I didn't think about that, so you're saying keep a record of the latest registration and when a person click new increase with one and if another one click new increase again and then when saving no problem since they are all unique.So the disadvantage is that I have to create some code keeping track of the latest reference number. Are there other issue you could think of? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-22 : 07:28:53
|
You could have gaps in the number if the user isn't using the number or the app hangs or ...But gaps shouldn't be a problem. You can have gaps in an identity column too when rows are getting deleted or something.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
pser
Starting Member
15 Posts |
Posted - 2010-10-25 : 02:12:15
|
| Well, I think this is the best option compared with the choices that are available. |
|
|
|
|
Several user Insert, primary key