Generating autonumber with date format like 23/Mar/2008/001 when the new record is inserted
-
Hi, I am developing a web application in which the customer/user register(enter)with their complaints. When the End user enters all the required data and clicks the Register button automatically a unique number should be generated. For instance the number should be in the format like 23/Mar/2008/001 and for every new user the number should be incremented if according to the system date. If the system date changes then the number should be start again from current date/001........ Thank you Dsr
-
Hi, I am developing a web application in which the customer/user register(enter)with their complaints. When the End user enters all the required data and clicks the Register button automatically a unique number should be generated. For instance the number should be in the format like 23/Mar/2008/001 and for every new user the number should be incremented if according to the system date. If the system date changes then the number should be start again from current date/001........ Thank you Dsr
-
Hi, I am developing a web application in which the customer/user register(enter)with their complaints. When the End user enters all the required data and clicks the Register button automatically a unique number should be generated. For instance the number should be in the format like 23/Mar/2008/001 and for every new user the number should be incremented if according to the system date. If the system date changes then the number should be start again from current date/001........ Thank you Dsr
Hi Mcmilan, You can have a logic like this .. try it out .. could be helpful, Before that following code part gives
--SQL SERVER 2005 code assuming that this ID will be generated at the time of insertion of values into DB
declare @currDate varchar(20),
@NextID varchar(20)
select @currDate = replace(convert(varchar,getdate(),106),' ','/')
print @currDate -- PRINTS 23/Jun/2008 FOR TODAY !!Pseudocode .......................................... set @CurrentDate = select replace(convert(varchar,getdate(),106),' ','/') select @MaxuserID = Max(UserIDColumn) from UserTable -- Consideing your table name is "UserTable" and Column name is "UserIDColumn" if(@CurrentDate is a SUBSTRING of @MaxuserID) { get @dateExcludedIndexPart from @MaxuserID -- here you should get 001 from "23/Mar/2008/001" @dateExcludedIndexPart++ @NextID = @CurrentDate + @dateExcludedIndexPart } else { @NextID = @CurrentDate + '001' -- First ID value of the day .. } I know there could be more better algo for your requirement !!! But you can give a try ...
Thanks, Arindam D Tewary
-
So what do you want to know? How to do it? Where, in a database or in the web app? Please try to help yourself by being specific with what you want - nobody else knows what you are working on
Bob Ashfield Consultants Ltd
-
Hi Mcmilan, You can have a logic like this .. try it out .. could be helpful, Before that following code part gives
--SQL SERVER 2005 code assuming that this ID will be generated at the time of insertion of values into DB
declare @currDate varchar(20),
@NextID varchar(20)
select @currDate = replace(convert(varchar,getdate(),106),' ','/')
print @currDate -- PRINTS 23/Jun/2008 FOR TODAY !!Pseudocode .......................................... set @CurrentDate = select replace(convert(varchar,getdate(),106),' ','/') select @MaxuserID = Max(UserIDColumn) from UserTable -- Consideing your table name is "UserTable" and Column name is "UserIDColumn" if(@CurrentDate is a SUBSTRING of @MaxuserID) { get @dateExcludedIndexPart from @MaxuserID -- here you should get 001 from "23/Mar/2008/001" @dateExcludedIndexPart++ @NextID = @CurrentDate + @dateExcludedIndexPart } else { @NextID = @CurrentDate + '001' -- First ID value of the day .. } I know there could be more better algo for your requirement !!! But you can give a try ...
Thanks, Arindam D Tewary
-
Thank you Arindam D Tewary, I have one more doubt ,...where should I write the code? In stored procedure or in aspx.cs page............ Please help me in this... Thank you Mcmilan
It should be in SQL server!! I have already mentioned that in reply ...
Thanks, Arindam D Tewary