Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

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

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
Rajeshk_cgm
Asked:
Rajeshk_cgm
  • 7
  • 5
2 Solutions
 
RiteshShahCommented:
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
 
RiteshShahCommented:
moreover, it will save your time which you are consuming in generating that unique number from date.
0
 
Rajeshk_cgmAuthor Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
RiteshShahCommented:
well, in that case why don't you include hour, min and seconds in your ID?
0
 
Rajeshk_cgmAuthor Commented:
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
 
RiteshShahCommented:
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
 
Rajeshk_cgmAuthor Commented:
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
 
RiteshShahCommented:
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
 
Rajeshk_cgmAuthor Commented:
ok.. fine is it possible.,  to get the newID() as 3/4 digit combination..
0
 
RiteshShahCommented:
you can get left 2 character and right 2 character
0
 
Rajeshk_cgmAuthor Commented:
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
 
RiteshShahCommented:
yes, hope for it...
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now