Solved

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

Posted on 2009-07-16
12
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

623 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