15,881,938 members
See more:

The Administrator SHALL provide supplier identification with SIX characters or numbers or combination of both but this SHALL be applicable in SIX digits only. After providing that six digit then auto increment number SHALL be added automatically from system. This SHALL be like “SUPPLY-0000”, similarly for next “INDIVI-0001”. Here 0000, 0001 SHALL be auto increment.
Posted
OriginalGriff 20-Jun-11 3:42am
What have you tried?
shoebass 20-Jun-11 3:56am
I dont know what should I do! I have not done anything till now! I am reading about "Auto-Increment" now. I have made forms and linked to database only. I need to incorporate the Above asked question as functionality but m not pretty sure what I need to do. Suggest me!
[no name] 20-Jun-11 15:48pm
I don't understand what you are asking!

## Solution 1

Store the digit in separate column and concatenate them when used

Pete O'Hanlon 20-Jun-11 7:20am
A fair suggestion - hence my vote of 5.
BobJanova 20-Jun-11 8:58am
Simple and effective.

## Solution 4

You can achieve that also by using stored procedures. BTW put constraint for that auto-increment column to restrict the duplicates.

Check this question for those answers.

Auto generate - stored procedure in sql server 2005[^]

## Solution 2

Create a new table in your database that stores an auto incremented integer. Then when you create a Supplier you can get the number from that table and append it to the supplier identification and save that in the suppliers table.

Hope this helps

shoebass 20-Jun-11 4:12am
Creating a new table would increase Overhead . I need some other-way round that address my problem without creating a separate table. Anyways thanx for ur suggestion.

## Solution 5

Use an auto increment column and append your output with "SHALL-" and proper number formatting. - This solution applies for a small data environment. If you are targeting large database then you can tweak the logic accordingly.

## Solution 3

SQL
`SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHERE ID LIKE 'SUPPLY%'`

hope it helps you..

Nagy Vilmos 20-Jun-11 7:20am
If two suppliers are being created at the same time, this would generate duplicate ID's
Pete O'Hanlon 20-Jun-11 7:25am
Too bloody right it could. So many people approach multi-user systems with a touching naivety.
fjdiewornncalwe 20-Jun-11 10:01am
Agreed.
JustWorking 20-Jun-11 10:02am
Not if we included it in Lock

Object thisLock = new Object();
lock (thisLock)
{
// Critical code section.
}
Rob Grainger 20-Jun-11 10:26am
Unless the DB can be accessed by more than one system.