Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Set Custom auto generated/increment field in SQL Server

Apr 8, 2012
Introduction:

In this article I will explain how to set custom auto increment or auto generated column during insertion of record in
SQL server.

Description:
In previous article I explained how to set auto identity column in SQL Server to generate a unique number for newly insert records like 1, 2, 3, 4, 5…etc based on records insertion. This identity column will work only for integer values. Suppose if I want to set id value starts from 08U13000 and increase that value based on new records inserted in table like 08U13000, 08U13001, 08U13002, etc… In this case auto identity property won’t work for us because it supports only for integer values.

To set this custom formatted auto increment values during insertion of records we need to write custom query. For that first design one new table in your database and give name as Users like as shown below

Column Name
Data Type
Allow Nulls
UserId
Varchar(50)
No
UserName
Varchar(50)
Yes
LastName
Varchar(50)
Yes
Location
Varchar(50)
Yes
Once table creation finished now write the following query to insert records in our table with required format of ids like 08U13000, 08U13001, 08U13002 etc.


DECLARE @totalcount INT
DECLARE @count VARCHAR(50)
SELECT @totalcount=(SELECT COUNT(UserId) FROM Users) /*This one is used to return total number of records in table */
IF @totalcount IS NULL     /*Condition to check whether totalcount contains value or not*/
SET @count='08U1'+CONVERT(VARCHAR(50),3000)
ELSE
SET @count='08U1'+CONVERT(VARCHAR(50), 3000+(@totalcount))
INSERT INTO Users (UserId,UserName,LastName,Location) VALUES(@count,'SureshDasari','Dasari','Chennai')


If you observe above query First I am getting count of records from “Users” table once if I get those records I am checking whether that count is NULL or not. If that count is null means Users table does not contain any data so I am setting @count value to “08U13000”. If count not equal to null then we are setting @count value to “08U13000”+count of records.

After insertion data in our table that would be will like this
 Demo



If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

28 comments :

Anonymous said...

thanx......suresh

Anonymous said...

I am very-very thankful to you sir,

really i am enjoying this....!

srinivas said...

please write an article on set up creation for asp.net webapplication on one click application should be copied in c://inetpub folder and database also should created automatically please it is very urgent and no of persons waiting for this

lnkhatri said...

is there can we use uniqueidentifier datatype as a primary key in the sql server database....if possible than how?? please give reply...

Meenakshi said...

Hi Suresh...

I tried the code...But if any row is deleted in middle...the count is repeating...Is there any solution ...

Phira said...

Hi Suresh

if you want the ID to be generated base on the username(taking 3 charecters) like if you inser username Suresh it must do something like this SUR001

Mukund.Potdar said...

Hi Suresh

Your article is really nice but still i have question regarding autoincrement so my question is
when we set identity property (1,1) it automatically inserted a new value like this
1
2
3

But is it possible can we do for this
08U13001
08U13002
08U13003
08U13004

Thank You

Mukund.Potdar said...

Another Question

I Got the Message

If i skip to enter any data into UserId

The following message i recevied

ie "No row was updated"

But when i set identity property it will automatically increment

1
2
3
4
5
6

But when i follow your article

"NO ROW WAS UPDATED"

Suresh Dasari said...

@Mukund.Potdar....
If you observe sample I clearly mentioned that one generating custom field during row insertion and it's working fine. Please check your code I hope you are not calling table correctly to insert data into it.

Mukund.Potdar said...

Hi Suresh

I Got my error and checked in your sample code but whenever i want to run i need to select
from select from

"DECLARE @totalcount INT"

Upto

"INSERT SYNTAX

Then press Execute F5

Am i right

Mukund.Potdar said...

But I have a Question

Normally when user enter information in application interface the UserId or ID will generate automatically but above sample i suppose to select the statement but I how to implement in ASP.NET C#

Mukund.Potdar said...

Another Question

If you start entering data via table
by living UserId and entering UserName, LastName, Location it will give error

No row was updated.

kiran kumar reddy said...

It is Executing perfectly ...but i need auto increment way it means that wn i inserting data from a webpage(aspx page) it should generate automatically in database increment of sequence

Unknown said...

thanks sir

Anil said...

Nice but not good for multi user and using aggregate function gives you performance Hit.
Better idea is to use a Separate Table with Table Name and Primary Key. With Proper locking Update values in Table. It will be fast and much safer on multi user environment.

Anil

expert advice said...

Enjoy reading your blog, keep going!

Technomacs said...

My problem is regarding the Product Category and Product (PRODUCTCODE)

Table: PRODUCTS
Columns: PRODUCTID (autoinc), PRODUCTCODE, PRODUCTCATERGORYID, PRODUCT NAME.

PRODUCT CATEGORY FROM DROPLIST
ID-CATEGORY NAME
1--Alcohol
2--Beverage
3--Food

DATA TO BE INSERTED
---Category----Product Name
1--Beverage----Coke
2--Alcohol-----Pure Coco Wine
3--Beverage----Pepsi
4--Food--------TunaFry
5--Food--------RiceTop
6--Beverage----Sprite
7--Food--------Cheese Burger

THE RESULT IS
PID-|-PCODE-|-PCATERGORYID-|-PRODUCT NAME
1-----200001-------2---------Coke
2-----100001-------1---------Pure Coco Wine
3-----200002-------2---------Pepsi
4-----300001-------3---------TunaFry
5-----300002-------3---------RiceTop
6-----200003-------2---------Sprite
7-----300003-------3---------Cheese Burger

So how to insert PcategoryID to PCODE+increment

Thanks a lot

Unknown said...

At below I have written a script that will create a table Test and will insert 100 values.

CREATE TABLE dbo.Test(PRODID INT,PRODNAME varchar(30))
declare @PRODID int=1
while @PRODID<= 100
begin insert dbo.Test values(@PRODID,'THREAD') set @PRODID+=1
end

It's Result is :
PRODID PRODNAME
1 THREAD
2 THREAD
3 THREAD
4 THREAD
5 THREAD
....
100 THREAD

But my point is rather than using that INT on PRODID I want to use VARCHAR(30)instead. My result should be like this
PRODID PROD-NAME
PR001 THREAD
PR002 THREAD
PR003 THREAD
PR004 THREAD
PR005 THREAD
PR010 THREAD
PR100 THREAD

What change should be made on the script ? I need a script for this ...

Unknown said...

sir, below error come in my sql

Cannot insert explicit value for identity column in table 'demo123' when IDENTITY_INSERT is set to OFF

what can i do....?

Unknown said...

sir please give me solution my code is given below

SELECT [UserId]
,[UserName]
,[LastName]
,[Location]
FROM [demo].[dbo].[demo123]
GO

select * from demo123

DECLARE @totalcount INT
DECLARE @count VARCHAR(50)
SELECT @totalcount=(SELECT COUNT (UserId) FROM demo123) /*This one is used to return total number of records in table */
IF @totalcount IS NULL /*Condition to check whether totalcount contains value or not*/
SET @count='08U1'+CONVERT(VARCHAR(50),3000)
ELSE
SET @count='08U1'+CONVERT(VARCHAR(50), 3000+(@totalcount))

INSERT INTO demo123 (UserId,UserName,LastName,Location) VALUES('@count','SureshDasari','Dasari','Chennai')

select * from demo123

reply sir

Unknown said...

how can use this command in asp .net form

Anonymous said...

It will not work properly if table has identity coulumn ;;;;
1 08U13000 deep
2 08U13001 kul
7 08U13005 abc
4 08U13003 kuldeep singh
5 08U13004 kuldeep
6 08U13005 ss
NULL NULL NULL

Anonymous said...

thnks alot ...its working great for inserting new record into table.
but after deleting any record its not working properly...as the column is set as Primary key,nvarchar(50)...and also there is problem of ID repeatation after deleting a record.
Please help.

Unknown said...

Hi suresh....
this is formatted.

Example: Y1RT2909001

Y1= Year 1 of production (say 2015-16)

R=Rice (crop name)

T= Tapsi (Name of seed producer)

29= Date of procurement

09=September (month)

001= number of lot.

 Sub section: Crop wise / variety wise and Lot wise stock of procured seed needed to maintain

please healp me

Unknown said...

Hi suresh....
this is formatted.

Example: Y1RT2909001

Y1= Year 1 of production (say 2015-16)

R=Rice (crop name)

T= Tapsi (Name of seed producer)

29= Date of procurement

09=September (month)

001= number of lot.

 Sub section: Crop wise / variety wise and Lot wise stock of procured seed needed to maintain

please healp me

Unknown said...

Very best Not Best

Anonymous said...

My problem is regarding the Product Category and Product (PRODUCTCODE)

Table: PRODUCTS
Columns: PRODUCTID (autoinc), PRODUCTCODE, PRODUCTCATERGORYID, PRODUCT NAME.

PRODUCT CATEGORY FROM DROPLIST
ID-CATEGORY NAME
1--Alcohol
2--Beverage
3--Food

DATA TO BE INSERTED
---Category----Product Name
1--Beverage----Coke
2--Alcohol-----Pure Coco Wine
3--Beverage----Pepsi
4--Food--------TunaFry
5--Food--------RiceTop
6--Beverage----Sprite
7--Food--------Cheese Burger

THE RESULT IS
PID-|-PCODE-|-PCATERGORYID-|-PRODUCT NAME
1-----200001-------2---------Coke
2-----100001-------1---------Pure Coco Wine
3-----200002-------2---------Pepsi
4-----300001-------3---------TunaFry
5-----300002-------3---------RiceTop
6-----200003-------2---------Sprite
7-----300003-------3---------Cheese Burger

So how to insert PcategoryID to PCODE+increment

Thanks a lot

Anonymous said...

asp do not code suresh , jesus is the only answer i won't listen to you

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.