Click here to Skip to main content
15,881,938 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
 
Share this answer
 
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[^]
 
Share this answer
 
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
 
Share this answer
 
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.
 
Share this answer
 
 
Share this answer
 
SQL
SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHERE ID LIKE 'SUPPLY%'


hope it helps you..
 
Share this answer
 
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