I have sqltable which has two fields

category_id category_name

1 india
2 africa
3 china

In front end i have 2 text boxes
categoryid
category name

But the category id field is read only , i mean i am automatically incrementing it by one.
Then i am displaying the data in the grid.
For the grid i have given delete option

The problem is when i delete the records the category id should decrement , which is not happening.
I mean when i delete china category id should display 3 but still it displays 4 which is for the next record.

After checking it i found that even after me performing delete operation the sql table is unchanged until i run/execute sql
so i want to know how to run execute sql statements under delete operation in the front end asp.net code.


Please Help and solve this.

I assume that category id is set as identity column in your database table. Identity column values will not be decreased after a delete operation. It is not adviceable to decrease the primary/unique id values during a database operation. Assume that you have 100 records and if you are deleting 50th record, then you want to reorder the table?.

Why can't you run a SQL UPDATE statement...

UPDATE TABLE_NAME SET INT_COLUMN = (INT_COLUMN - 1) WHERE INT_COLUMN > @DEL_ROW_VALUE

Would something like that work?

if id is your primary key then delete command would not let the identity to start from where you have left.you have to use 'Insert Identity On'

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.