Auto Generation Code , After all other values are inserted

Posted by Raja_89 under Sql Server on 10/17/2013 | Points: 10 | Views : 4635 | Status : [Member] | Replies : 8

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hai

I am having cus_id a field which is a identity column which generate a values like C001,c002..etc [SQL Server 2005]

After inserting other column values that cus_id does not generate through form
While inserting SQL Server Management Studio tool it will generate next value

How can i get the value

Regards

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Nismeh on: 10/18/2013 [Member] Starter | Points: 25

0	Hey Buddy, Did n't understood your requirement properly. IT KNOWLEDGE IS APPLIED KNOWLEDGE So Just Do It Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Ermahesh2009 on: 10/18/2013 [Member] Starter | Points: 25

0	identity column is integer type ..how it generate c001 ,c0002 ,c003 nand so on Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

0	Refer the following link http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server http://stackoverflow.com/questions/17668987/auto-increment-alphanumeric-id-in-mssql Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Raja_89 on: 10/18/2013 [Member] Starter | Points: 25

0	Hai Ermahesh I am combining the result into cust_id which is a varchar type Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

0	Write one stored procedure for getting auto generated alpha numeric value ( C001, C002, ... and so on) and insert into table so that you can generate customized identity values and return back that value as OUTPUT of the Stored Procedure....... http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server you can make use of the above URLs for getting C001, C002, ..... Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Write one stored procedure for getting auto generated alpha numeric value ( C001, C002, ... and so on) and insert into table so that you can generate customized identity values and return back that value as OUTPUT of the Stored Procedure.......

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server
you can make use of the above URLs for getting C001, C002, .....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raja_89 on: 10/18/2013 [Member] Starter | Points: 25

0	Hai Nismeh Table cust_id Name Age,Dob , DoJ cust_id Name Age Dob Doj c100 Raja 21 21-04-1989 21-09-2013 I will generated value for cust_id only when inserting through SQl Server Management Studio When i insert into ASP.Net cust_id Name Age Dob Doj cust_id field is empty Guna 23 21-02-1990 21-12-2012 how can i resolve it Regards Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Hai Nismeh

Table

cust_id Name Age,Dob , DoJ

cust_id Name Age Dob Doj
c100 Raja 21 21-04-1989 21-09-2013

I will generated value for cust_id only when inserting through SQl Server Management Studio

When i insert into ASP.Net

cust_id Name Age Dob Doj
cust_id field is empty Guna 23 21-02-1990 21-12-2012

how can i resolve it

Regards

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/18/2013 [Member] [MVP] Platinum | Points: 25

0	hi Raja, CREATE PROCEDURE dbo.AutoIncrement_Id ( @Name VARCHAR(255), @EmpID VARCHAR(10) OUT ) AS BEGIN DECLARE @Max INT, @ID VARCHAR(10) IF NOT EXISTS(SELECT ID FROM Emp) BEGIN SET @ID = 'E00001' INSERT INTO dbo.Emp(ID,Name) VALUES(@ID, @Name) SELECT @ID END ELSE BEGIN SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10),ID), 2, 10)) FROM Emp SET @ID = 'E' + RIGHT('0000' + CONVERT(VARCHAR(10), @Max + 1), 5) INSERT INTO dbo.Emp(ID,Name) VALUES(@ID, @Name) SELECT @ID END END The above procedure is for inserting records into Emp Table by auto generating E0001, E0002, and so on; then it returns the auto generated empid as OUTPUT Refer the following link for calling a stored procedure which has OUT param using C# http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

hi Raja,

CREATE PROCEDURE dbo.AutoIncrement_Id
(
	@Name VARCHAR(255),
	@EmpID VARCHAR(10) OUT
)
AS
BEGIN
	DECLARE @Max INT, @ID VARCHAR(10)

	IF NOT EXISTS(SELECT ID FROM Emp)
	BEGIN
		SET @ID = 'E00001'		
		INSERT INTO dbo.Emp(ID,Name) VALUES(@ID, @Name)	
		SELECT @ID
	END
	ELSE
	BEGIN
		SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10),ID), 2, 10)) FROM Emp
		SET @ID = 'E' + RIGHT('0000' + CONVERT(VARCHAR(10), @Max + 1), 5)
		INSERT INTO dbo.Emp(ID,Name)
		VALUES(@ID, @Name)
		
		SELECT @ID
	END
END

The above procedure is for inserting records into Emp Table by auto generating E0001, E0002, and so on; then it returns the auto generated empid as OUTPUT

Refer the following link for calling a stored procedure which has OUT param using C#
http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nismeh on: 10/18/2013 [Member] Starter | Points: 25

0	Hi, As you mentioned cust_id is varchar. And till I know we can set auto_increament to int datatype not varchar. You can do this kind of thing CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY, IDwithChar AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED ) For more details you ca refere http://stackoverflow.com/questions/4699591/how-to-autoincrement-a-varchar If it helps you please mark it as an answer. Thanks in advance :) IT KNOWLEDGE IS APPLIED KNOWLEDGE So Just Do It Raja_89, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

As you mentioned cust_id is varchar. And till I know we can set auto_increament to int datatype not varchar.
You can do this kind of thing

CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY,
IDwithChar AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED
)

For more details you ca refere
http://stackoverflow.com/questions/4699591/how-to-autoincrement-a-varchar

If it helps you please mark it as an answer. Thanks in advance :)

IT KNOWLEDGE IS APPLIED KNOWLEDGE
So Just Do It

Raja_89, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts