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 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 Email
|
|||
|
|
|
17 comments :
thanx......suresh
I am very-very thankful to you sir,
really i am enjoying this....!
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
is there can we use uniqueidentifier datatype as a primary key in the sql server database....if possible than how?? please give reply...
Hi Suresh...
I tried the code...But if any row is deleted in middle...the count is repeating...Is there any solution ...
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
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
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"
@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.
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
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#
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.
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
thanks sir
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
Enjoy reading your blog, keep going!
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