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
|
|||
28 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
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 ...
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....?
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
how can use this command in asp .net form
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
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.
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
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
Very best Not Best
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
asp do not code suresh , jesus is the only answer i won't listen to you
Note: Only a member of this blog may post a comment.