Click here to Skip to main content
14,699,886 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
in the database in sql server records are there.10 records.

in that 10 records suppose i have delete the 10th record its deleted tehn remaining 9 records are there.suppose then i insert next record in the database i want the serial no 10.but when i saves its comes as 11the record (serial no 11)

how to do


I think there is identity column in the table so it was generating 11. You can manually insert the value 10 by making use of SET IDENTITY_INSERT <tname> ON. Later you can make IDENTITY_INSERT OFF.

You can refer the following link for details[^]
First set Identity to Off...Then

string _qry = select isnull(max(Snocol),0)+1[Snocol] from Tablename;
sqlcommand cmd=new sqlcommand(_qry,con);

object obj = cmd.executescalar();
give This Object obj in the Insert query where u r inserting u r data.....
it is proper numbering

for continuous number

just create view and use row_number function

your table is tbl1
View for that table is = tbl1_
Query inside view 'tbl1_' is,
select row_number() over(order by id) as SrNo,id,col1,col2 from tbl1

now save it

and fetch data from view instead of table
select * from tbl1_

Happy Coding!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900