Generating sequential numbers with prefix 1

[Glohamar]

Hello,

I have unique prefixes depending on what user is working on. When user needs a new number, I query database, find the last entry, remove the prefix and generate my next number in sequence. There are times when users are needing to generate multiple sequential numbers for a prefix. As long as they are the only ones requesting new numbers, all works well, it is when there are 2 or more needing a new number that I am having trouble. If while generating the multiple sequential numbers someone requests a number, it breaks the numeric sequence the first user is expecting to see.

This is where I am having trouble. I am in ASP.Net 4.0 using C#. To create the sequential numbers, I am using a for loop because the user will indicate how many they are needing. I suspect I am generating my sequential numbers the hard way, but I am not sure the best method.

So my question is, is there a way that once the for loop is generating the sequential list of numbers that another request from a different user has to wait until the first request is finished?

Example of the numbering:

ZZ1111-000156
ZZ2222-000157
ZZ3333-000158

If a user request a series of 25 numbers, I need that request to create say ZZ3333-000159 thru ZZ3333-000184 without one sneaking in there for a single request of ZZ4444-XXXXXXX in the middle somewhere.

below is not what the users are expecting.
ZZ3333-000159
ZZ4444-000160
ZZ3333-000161
ZZ3333-000162

Should be
ZZ3333-000159
ZZ3333-000160
ZZ3333-000161
etc unitl ZZ3333-000184
ZZ4444-000185

Hope this explains what I am trying to accomplish. No need to focus on the prefixes as there is specific criteria that is used to create those numbers, I just need to get the sequential numbers to workout.

Thank you for any suggestions.

Dave

 

[kray4660]

Are you storing the last generated number in a table? Seems the only way to correctly do it.

 

[Glohamar]

Yes, once the number is created, I add the needed prefix and store it in a SQL server table.

 

[jbenson001]

Honestly, this whole design seems a bit strange. Why do you need to have these prefixes? More information would be helpful to try to find a better solution.
And what exactly is the prefix the alpha portion or the numeric portion or is it the combination of both?
Also, what are number are you storing in your DB, is it just the numeric portion?

From what I see it looks like you are just storing a numeric value and then the alpha portion is like a userID or some sort of unique identifier is that correct?

I am not sure what your requirements are or why. But it seems to me that if you need sequential numbers, it should be per "UserID" (i.e. "ZZ3333", ZZ4444", etc.). Doing it this way will ensure that you will have sequential numbers. They way are you doing it now will almost certainly cause a skip in the sequence if more than one request for the last number is made at the same time.
If you want to continue the current way, it can become complicated. You will have to do something like do a rowlock or tablelock when someone queries the table, give them a sequence, and them immediately write to the table and unlock the row/table.

This might be a good time to sit down with some key people and make a smart business decisions as to why this is necessary and possibly change the requirements.

 

[Glohamar]

Hi jbenson,

I do see how it is confusing. The prefix is based on products. So ZZ would be our widget product, XX would be product01, YY would be product02, etc. The 4 digits after the alpha is code for a process in the manufacturing of the product. The numbers after that have no specific meaning, just the next number on the list.

ZZ3333-111111 is a unique number for a process step for that product.

So each product has its own sequence of numbers that are after the prefix. When the user needs a new number, they select the product, I use that to find the last entry, trim to get the last 6 digits, add 1 and assign that number along with the prefix that all get stored for reference later. There are many times when a user is working a product and needs to have multiple numbers reserved/checked out for his process. Right now they get the numbers one at a time with no issue, but there is a desire to get multiples at one time.

This used to be done via an Excel spreadsheet, but as business grows this is not working as well as they want, and several times the Excel file has been getting corrupted and we lose some information.

Hope this helps explain what I am trying to solve.

Thanks for you time and help.

 

[jbenson001]

Ok, so one thing you can do is store the product and sequence number in a table, if that is not what you are doing already.

Another way is to let the DB handle it. Again, I am not sure why this sequencing is important, or why it needs to be done, the DB has an identity column that can be used for this instead. But if this is what you need to do, so be it. Another thing is I am not sure why you need to reserve sequence numbers ahead of time.

What I would do is have a screen that allows the user to enter any number of rows on the screen that they need to. The user should not have to worry about grabbing sequence numbers. Now, when the user saves the rows, let's say you are using a gridview, pass the info to the db, and let the DB code (a stored procedure) handle adding a sequence number to the table for each row. This would help prevent gaps in the sequence, however, it is not fool proof.

 

[Glohamar]

Hi jbenson,

That is an interesting solution, I had not thought of doing what you are explaining. I am going to give that a try as I think it would work.

Thank You
Dave

 

[jbenson001]

Glad to help
Good luck and post back with your results / questions