-2

I need to assign a unique number to each "customer". Basically an account number or so to speak. I need to generate a sequential number with a prefix... say... 10001111 to eventually 19999999. The numbers need to be unique. I know how to create a random number but those numbers must be unique and can't be repeated so I am stuck with how to even begin with the programming logic. I found some C# that would help but I am programming in VB.NET. Any help would be appreciated!

Lord Relix
  • 922
  • 5
  • 23
  • 50
  • If you want them to randomly fall in that range, and yet still maintain their uniqueness, every time you generate one you'll have to poll all other previously generated numbers to assure uniqueness. – NominSim Apr 29 '13 at 14:07
  • Start at one and keep adding one? Or is there a specific reason you need to use Random Numbers? – Binary Worrier Apr 29 '13 at 14:07
  • I need to have consistency. I can obviously just do a uniqueID, with 1, 2 ,3 but they all need a prefix. – Lord Relix Apr 29 '13 at 14:10
  • 1
    Where are you storing this data? A database would normally be used to generate unique sequential IDs for you. And most of them can handle a prefix, if it's numeric. – RBarryYoung Apr 29 '13 at 14:11
  • yes I am using a SQL Server database. Is there a specific function or code for this? – Lord Relix Apr 29 '13 at 14:12
  • 3
    What purpose does the prefix serve? More than likely you can just use the identity column with a seed specification in SQL server to get a unique ID. You can then automatically append the prefix in code and strip it away elsewhere. There is no need to complicate it more than that. – Frazell Thomas Apr 29 '13 at 14:18
  • Do the UniqueID approach and add the prefix. Where’s the problem? Also, if you’ve found a C# solution what is preventing you from translating it into VB? – Konrad Rudolph Apr 29 '13 at 14:26
  • Konrad you probably found the same C# code I did. I looked into it but I think a smarter/faster way could be done. What Frazell said makes perfect sense so I think I am going with that. – Lord Relix Apr 29 '13 at 14:27
  • @LordRelix I haven’t found any C# code – *you* mentioned that code in your question. I still don’t understand your problem. – Konrad Rudolph Apr 29 '13 at 14:31

2 Answers2

2

Honestly, I don't see why you need to do anything more complicated than an Identity Column in SQL Server.

CREATE TABLE Persons
(
P_Id int PRIMARY KEY IDENTITY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Source: W3 Schools

You can then pull the customer data back and pre-pend a prefix to this either static or dynamically.

A static example would be 

SELECT 'CUST-' + P_Id [CustomerNumber], LastName
FROM Persons

But nothing is stopping you from adding the prefix in as a column an dynamically joining them (if you need to store multiple prefixes).

At the end of the day, you'll need a persistent store to ensure you're getting a unique number. Instead of reinventing the wheel on this you can leverage the DB server, which is written for this purpose.

You can also have a table in the DB whose job is to store the latest ID number and you can manually increment and update that, but I'm not a huge fan of that. It is too messy.

Frazell Thomas
  • 6,031
  • 1
  • 20
  • 21
  • I am going with your suggestion. Seems the smarter and simpler way as well. I do need multiple prefixes but I'd probably just make a column or an identifier that identifies where the "account number" goes. Also write a logic that, for example, if column "Identifier" is 1 it appends the prefix 1000-uniqueIDhere. I guess that'd work or am I wrong/ – Lord Relix Apr 29 '13 at 14:32
  • @LordRelix I would keep it as simple as adding a column on the table called "IdPrefix" (or similar) and whenever I return the customer ID I would concatenate that column with the Id column. Then you're free to store whatever prefix you need on account creation and never need to adjust it later (if anything changes regarding prefixes being added or removed or etc.). – Frazell Thomas Apr 29 '13 at 14:36
  • Perfect. I thought there would be a more complex way. I just love making my life miserable. Thanks a million! – Lord Relix Apr 29 '13 at 14:41
1

One common way to do this kind of thing is to generate all of the possibilities at once, and store them in a list. When you want to create a new customer, choose a random number from the list and then remove it from that list so that it can't be used again. I don't know how practical this would be in your scenario.

FakeDIY
  • 1,425
  • 2
  • 14
  • 23
  • For example say I have a table... with a min/max range of 1000001 to 199999...... I could just make a query that randomly selects a value? – Lord Relix Apr 29 '13 at 14:11
  • Yes, and then you need to do something to remove that value so that you can't choose it again. Whether or not this is practical depends on the size of your table, there may be "smarter" ways. See also http://stackoverflow.com/questions/3627029/smart-way-to-generate-unique-random-number – FakeDIY Apr 29 '13 at 14:14
  • `generate all of the possibilities at once` We are not in the age of quantum computers yet. :) – Victor Zakharov Apr 29 '13 at 18:13