Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-16
12
Medium Priority
?
292 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 2000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 2000 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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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