Is there a way to Execute sqltable from asp.net page. please solve this problem

[coollife]

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.

[Ramesh S]

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?.

[ericstenson]

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?

[carobee]

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'