Deleting some records in sqldatabase re assign autocount number from C#

Question

0.00/5 (No votes)

See more:

I bounded my Datagridview with SQL database file.I am Maintaining a column in SQL called slno. I am Displaying that slno in datagridview.

When I am selecting a row and deleting the record I want to reassign the autocount numbers to slno.

What I have tried:

I tried to count the rows in database file and assign that number to

DBCC CHECKIDENT ('tempsales', RESEED, rowcount)\\in my case 5
But error is coming "Parameter 5 is incorrect for the DBCC statement"

Posted 16-Apr-17 0:10am

vijay_bale

Updated 16-Apr-17 0:20am

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2017-04-16T00:19:00

Solution 1

If you're trying to use a variable for re-seeding the table, use @ before the name or the variable. For example

SQL

DECLARE @rowcount INT;
SET @rowcount = 5;
DBCC CHECKIDENT ('tempsales', RESEED, @rowcount);

Or simply

SQL

DBCC CHECKIDENT ('tempsales', RESEED, 5);

Note that normally reseeding the identity isn't necessary, since the only purpose is to give unique values. Never assume that the values are continuous!

Posted 16-Apr-17 0:19am

Wendelius

Comments

vijay_bale 16-Apr-17 6:27am

I did Exactly what you told. But error is coming "Must declare the scaler variable "@rowcount"

Wendelius 16-Apr-17 6:39am

Do not run the statements separately, instead, run them in a single batch.

If you're using Management Studio, you can for example select all the three lines and hit F5 to run the selected statements.

vijay_bale 16-Apr-17 6:59am

running in c# so all will run in a singal batch na.
My code is
con.Open();
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM tempsales",con);
cmd.ExecuteNonQuery();
Int32 rowcount = (Int32)cmd.ExecuteScalar();
MessageBox.Show(rowcount.ToString());
SqlCommand cd = new SqlCommand("DBCC CHECKIDENT ('tempsales', RESEED, @rowcount)", con);
cd.ExecuteNonQuery();

Wendelius 16-Apr-17 7:34am

As said, you can't get a continuous sequence of numbers with identity field. The sole purpose of an identity is to give unique numbers, not continuous.

Consider a situation where you have rows 1, 2, 3, and 4. If 1 is deleted, what's supposed to happen? If you reseed the identity it will get a number of 3 but that already exists...

If you need to show running row numbers, just use ROW_NUMBER (Transact-SQL) | Microsoft Docs[^] when querying.

Wendelius 17-Apr-17 4:29am

Based on the description from Solution 2:
"ex:-I have 2,3,4,6,7,8 sno(index number) records(1,5 I already delete) but this displaying numbers 1,2,3,4,5,6. That is what i want. But When I try to delete 2 record it is not.because no 2 is existing in both. but i tried to delete 5th record it will delete. because that 5th sno(index number) record index is not in database file but is there in this list what you told, so it will delete and numbers also updated from begining. Maybe be I am thinking I explained as much as I can."

I'm not sure if I understand correctly, but are you trying to delete/update the record based on the ordinals? For example if you have values 1,5,9, and 11 in the database you would have 4 records. When ordered you would have list 1,2,3, and 4. However, you cannot update record 2 based on the ordinal since the key value is 5. So in order to manipulate the record, always use the key value, not the position in a list or similar.

On the other hand it may be that I misunderstood your example...

OriginalGriff · Answer 2 · 2017-04-16T00:20:00

Don't. The idea of an IDENTITY field is that it's unique, but (deliberatly) not necessarily sequential. In practice, they are sequential, but SQL never "reuses" freed numbers when you delete records, so that any other systems or tables that reference the row via the field don't pick up the "wrong" data.
If you want a sequential number for your rows based on the Identity field, then use ROW_NUMBER and ORDER BY:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS [Sequence Number], * FROM MyTable

That will always give you a sequence starting from 1 with no gaps.

"yes It is Giving but I want to display all in datagridview so that every time if user delets the record it will recount"

That's trivial! All you need is a column to put it in, and to handle the RowPrePaint event:

C#

void myDataGridView_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
    {
    myDataGridView.Rows[e.RowIndex].Cells[0].Value = e.RowIndex + 1;
    }