Auto Generation Code , After all other values are inserted

Posted by Raja_89 under Sql Server on 10/17/2013 | Points: 10 | Views : 2439 | Status : [Member] | Replies : 8
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response