Solved

Auto create unique ID for a database entry

Posted on 2011-09-20
3
192 Views
Last Modified: 2012-06-27
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
Comment
Question by:kenuk110
  • 2
3 Comments
 
LVL 8

Expert Comment

by:gena17
ID: 36567293
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
 

Author Comment

by:kenuk110
ID: 36567493
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
 
LVL 8

Accepted Solution

by:
gena17 earned 500 total points
ID: 36580423
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now