Generate unique number on a network 2

[jurgen]

Hi,

I'm having a little bit of a problem.
I want to assign a unique number to a document, something like 2001001, all the numbers
have to be used, so they must follow up each other 2001001,2001002,2001003, ....

There're 15 users who make documents on a network, so I must be aware that 2 people or more can save their documents at the same time or cancel it without saving it

How can I solve this problem for the numbering of the documents ?
I've tried it with a sequence, but when someone cancels his document and an another one saves his document, the numbers aren't follwing up each others

What is the best way to generate a unique number on a network and not skipping one ?

Thank You

Jurgen

 

[fluteplr]

consider setting an indentity field in your table. This will increment each time a record is added. You should not have any skips in the number sequence unless someone deletes a record. Out of curiosity, why is is so important no numbers be skipped?

 

[jurgen]

Even the identity doesn't give a correct one ...

Add a record, cancel it and the nunber is gone ....

The reason we search for this number is because the law forced us for this kind of documents ...

Thnx anyway

 

[ColinM]

You could maybe use an insert trigger, which does something like:
recordID = select max(recordID) + 1

 

[jurgen]

This is a method I have concidered, and I think that it is the only one that's left.

I think that if I use that way and assign the number when de document is saved into the database that I get the least problems. But I still think that there are going to be blank numbers.

When 2 people save a record at the same time, how does SQL Server handle this ?

Tnx

 

[fluteplr]

When 2 people save a record at the same time, how does SQL Server handle this ?


You will have to lock the table which is going to cause you other problems.

I would look again at the identity key and not allow people to delete records or 'cancel'. Perhaps if they try to cancel you could just write the record anyway and mark it as record addition canceled (or something like that.)

 

[Cagliostro]

You can use GUIDs. They also are named replica. John Fill

 

[foxdev]

A GUID is the "proper" solution, but only to a different problem. Jurgen needs consecutive numbers, and GUIDs won't provide that.

I'd start with all the other suggestions here first, but if they don't work out, you could take the kludgy low-tech way:

Have a table of pre-generated numbers (say, 1 to 10,000) and a bit column UsedFlag. When you need a number, you select the lowest available row:

[tt]select min(DocNumber) where UsedFlag = 0[/tt]

then update that record by setting the UsedFlag = 1. I'd suggest making this atomic by using a stored procedure to do both operations.

The (only) beauty of this approach is that if one cancels their document, you can update the record and reset the UsedFlag to 0, thus making it available again.

Not pretty, granted, but reasonably efficient. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[NorthChert]

where dose the "UsedFlag" be from?

 

[essa2000]

I think it is a user defined column in that table and it is bit field.

I think this method is suitable for the above problem. Muhammad Essa Mughal
Software Engineer
essamughal@yahoo.com