Solved

Generate a Unique Number in asp.net / sql server 2005

Posted on 2009-07-16
12
279 Views
Last Modified: 2013-11-08
hi... i need to generate a unique number.

I have used Date in Sql Server.

But Problem., its getting conficts while using more than one system.


Create PROCEDURE [dbo].[SP_FetchRequisitionID]
@ActionType varchar(30)
as
DECLARE	@Date Varchar(8),
			@MaxID VARCHAR(12),
			@Return varchar(12)
 
	IF(@ActionType='ReqID')
	BEGIN
		IF(DATEPART(DAY, GetDate()) < 10)
			SET @Date = '0' + CAST(DATEPART(DAY, GetDate()) AS VARCHAR) 
		ELSE
			SET @Date = CAST(DATEPART(DAY, GetDate()) AS VARCHAR) 
 
		IF(DATEPART(MONTH, GetDate()) < 10)
			SET @Date = @Date + '0' + CAST(DATEPART(MONTH, GetDate()) AS VARCHAR) 
		ELSE
			SET @Date = @Date + CAST(DATEPART(MONTH, GetDate()) AS VARCHAR)
 
		SET @Date = @Date + CAST(DATEPART(YEAR,  GetDate()) AS VARCHAR)
 
		SET @MaxID= '0'
		
		SELECT @MaxID= RequisitionID from RequisitionDetails where substring(RequisitionID,2,8)=@Date
										
		IF(@MaxID = '0')
			BEGIN
				SET @Return = 'R'+ @Date + '001'
			END
		ELSE
			BEGIN
				IF(DATEPART(DAY, GetDate()) < 10)
					SET @Return = 'R0' + CAST(CAST(RIGHT(@MaxID, 11) AS NUMERIC(12, 0)) + 1 AS VARCHAR)
				ELSE
					SET @Return = 'R' + CAST(CAST(RIGHT(@MaxID, 11) AS NUMERIC(12, 0)) + 1 AS VARCHAR)
		END 
 
		Select @Return as RequisitionID
	END

Open in new window

0
Comment
Question by:Rajeshk_cgm
  • 7
  • 5
12 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24867735
why don't you use GUID.

in .NET System.GUID.NewGuID()

and in SQL Server

Select newID()

that is guarantee that you will have unique number all the time.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24867744
moreover, it will save your time which you are consuming in generating that unique number from date.
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24867773
ofcourse i can use.., but i need the id, it should be meaningful.,

i have used R0110072009

here R is for requistion and 01 is refers the department details and the next values refers DD/MM/YYYY

NewID() is a uniqueidentifier..

is it possible to do with varchar...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24867803
well, in that case why don't you include hour, min and seconds in your ID?
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24867840
well.,... it will not conflict with Seconds.. ?

for example there is 50 system., at the same second every one is trying to get the value means.

Actually i need to show the ID in form load itself., while insert into the table am just using the id

In Stored Procedure itself, am inserting means, hope we will not get the conficts.


0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24867864
well, you do show it in form load but while inserting records, check once again whether that ID has been used in table or not, if used than regenerate new and change your old ID, give user message that your ID is "__________".
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24867986
no... that will not be the solution., because am using that id, to send mail ..

am using that id in may cases..

so., i can't regenerte the id while insertion..

  private void GenerateId()
    {
        long i = 1; foreach (byte b in Guid.NewGuid().ToByteArray())
        {
            i *= ((int)b + 1);
        }
        string number = String.Format("{0:d3}", (DateTime.Now.Ticks / 10) % 1000000000);
        Response.Write(number.ToString());
    }


how to make the digit as fixed.. is it possible.

if so., i can use the above method with the Format R01ddmmyyyy (GuidValue3digit or 4 digit)
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 500 total points
ID: 24868022
it is not fixed that what number in GUID you will get alphabate and at which position you will get number but can't you get simply first two character and last two character?
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24868098
ok.. fine is it possible.,  to get the newID() as 3/4 digit combination..
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24868112
you can get left 2 character and right 2 character
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24868637
SELECT RIGHT('0000'+ CONVERT(VARCHAR, ABS(CONVERT(INT, CONVERT(VARBINARY, NEWID())))), 3)

i have used the above method

and now the Id combination is R16072009003981     Last 3 digit is random number

i hope now i will not get the conflict.. right.. !!!!!
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24868690
yes, hope for it...
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…

685 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