Click here to Skip to main content
15,118,573 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a Database for Supplier and Administrator and I want to achieve following thing: Please Help!

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
Comments
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!

Store the digit in separate column and concatenate them when used
   
Comments
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.
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[^]
   
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
   
Comments
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.
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.
   
SQL
SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHERE ID LIKE 'SUPPLY%'


hope it helps you..
   
Comments
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900