Solved

Auto create unique ID for a database entry

Posted on 2011-09-20
3
199 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

839 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