Member Avatar for 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.

  • 4 Contributors
  • 3 Replies
  • 136 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by carobee

Dani AI

Generated

— several replies touched on identity behavior and ad-hoc updates. Two practical, safe approaches are below so the UI shows contiguous numbers without forcing the primary key to change, and a cautious method if you absolutely must renumber stored IDs.

Keep keys stable and show sequential numbers only in the UI

Example query to bind to the grid:

SELECT
  ROW_NUMBER() OVER (ORDER BY category_id) AS DisplayNo,
  category_id,
  category_name
FROM Categories;

Maintain a separate order column if the order must be persistent

  • Add a display_order (or sort_order) INT column and update that on delete/insert. Recomputing display_order is safe and avoids touching the identity/PK. A common pattern uses a CTE to recompute sequential values and update the display_order.

If you must renumber the identity values (not recommended)

Recommendation: show contiguous numbers at display time (ROW_NUMBER) or add a dedicated ordering column. Renumbering identity values should only be done with full understanding of referential impacts and a tested migration script.

Recommended Answers

All 3 Replies

Member Avatar for 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?.

Member Avatar for 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?

Member Avatar for 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'

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.