• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Auto create unique ID for a database entry

Hi,

I have a table which contains references to blocks within supermarkets. The code for these blocks uses a type of telephone type code:

44-01-006-AA11-S

This code = United Kingdom, London, Store 006, S = Supermarket and AA11 is the block code.

These codes are printed on forms so the person reading it can understand 'in English' where this block is located. I should mention that my term 'block' refers to a display in a supermarket, the cereal block, the coffee block, the shampoo block etc.

What I need to try to do though is also have a second code for this block, something that is shorter than xxx-xx-xxx-X-xxxx. Our merchandisers are going to start takin gpictures of these blocks on a 'PDA' and in order for us to initially identify the block is by typing in the full code, they take a picture, name it then send it to our server to break the code down again. My problem is that 1 - they have around 40 blocks to enter per 6 stores they visit each day and 2 - they will type the code wrong, I just know it! If the code doesn't come in to the system properly then our report will not be able to find the picture.

The most blocks we will ever have in one country is 99,999. is it possibe to create an automatically created unique 5 digit alphanumeric code that is just added when we add the main code for this block in our database or am I thinking way off the mark here?

So this:
044-01-006-S-AA95
would equal
BY32S (as an example)

Any help would be much appreciated.

Best Regards,

Ken

0
kenuk110
Asked:
kenuk110
  • 2
1 Solution
 
gena17Commented:
Option 1: Use GPS on the PDA (if possible) to recognize the country, the city, and maybe more.
Option 2: Generate a random 5-chars code for each block in advance and store it in your database. So your users will use these short codes. The chance for mistake will be small since there are 36^5 available options and you will only need 10^5.

Hope this helps
0
 
kenuk110Author Commented:
Thanks for the suggestions!

I'd like to go with option two but how do I generate this amount of codes randomly? Is there a way to do it in SQL or do I need to look for another program OR do I have to sit and think of 99,999 differnent codes from my own head?

Cheers,

Ken
0
 
gena17Commented:
There is a RAND() function that generates a random number between 0 and 1.

You can use Round ( RAND()*35 ) to generate between 0 and 35 (10-35 are for letters A-Z). Run it 5 times and you get a code.
I found a nice guide for RAND() here: http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

After you have your code, just check the same code does not exist in your database already. If yes (unlikely) - generate one more.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now