Solved

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

Posted on 2009-07-16
12
270 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now