0

I am using SQL Server Express and VS2008.

I have a database with a table A, which has a column ID as IDENTITY. The ID is auto-incremented. Even if the row is deleted, the ID still increases.

After several data manipulation, the current ID has reached 15, for example.

When I run the application

  • if there's at least 1 row: if I add a new row, the new ID is 16. Everything is fine.
  • If the table is empty (no row): if I add a new row, the new ID is 0, which is an error (I think).

And further data manipulation (e.g. delete or update) will result in an unhandled exception.

Has anyone encountered this?

PS. In my table definition, the ID has been selected as follow:

Identity Increment = 1; Identity Seed =1;

The DB load code is:

dataSet = gcnew DataSet();

dataAdapter->Fill(dataSet,"A");

dataTable=dataSet->Tables["A"];

dbConnection->Open();

The Update button method

dataAdapter->Update(dataSet,"tblInFlow");

dataSet->AcceptChanges();

dataTable=dataSet->Tables["tblInFlow"];

dataGrid->DataSource=dataTable;

If I press Update:

  • if there's at least a row: the datagrid view updates and shows the table correctly.

  • if there's nothing in the table (no data row), the Add method will add a new row, but from ID 0. If I close the program and restart it again: the ID would be 16, which is correct.

This is the add method

row=dataTable->NewRow();

row["column1"]="something";

dataTable->Rows->Add(row);

dataAdapter->Update(dataSet,"A");

dataSet->AcceptChanges();

dataTable=dataSet->Tables["A"];
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
john White
  • 43
  • 1
  • 5
  • Can you show the definition of the table, and the code that adds a new row when the table is empty? Sounds like either the table is not initially seeded correctly or you are manually re-seeding it. If the identity should start at 1, the config should be `IDENTITY(1,1)` and therefore you shouldn't get 0 unless something else is going on. So you need to provide more information. – Aaron Bertrand Jun 04 '12 at 03:30
  • I think while declaring an identity column u need to specify the seed and increment value for example CREATE TABLE dbo.MyTable ( ID int identity(1,1), Name varchar(20) ) – praveen Jun 04 '12 at 03:32
  • Pls have a look at my above edited question. Thank you. – john White Jun 04 '12 at 03:47
  • Can u please tell me how do u reseed the identity column ? – praveen Jun 04 '12 at 04:17
  • I didnt reseed the column. I left it automatic. Maybe sometimes it takes time to update the table. If the data grid shows a row ID 14 (the biggest ID in the table) although the ID reaches 16 (the rows 15 and 16 have been deleted), the new added ID shown on the data grid view is 15, but the actual ID for the data is 17. If I close the program and restart it again, it shows 17 correctly. – john White Jun 04 '12 at 04:45

1 Answers1

1

If you are deleting the date using 

 Delete from myTable

It will not reset the identity column but on the other hand if your truncating the table then it will reset any identity columns to the default seed value ( in your case starting from 1) If you are truncating the table and then reseeding it ,the first identity reseed value will be 0 if you write 

 DBCC CHECKIDENT(MyTable, RESEED, 0)

Even though your identity column is specified as Identity=1 and seed =1 ,the very first row inserted will start from the Id=0

If you want to start from 1 then you need to reseed the column as 

 DBCC CHECKIDENT(MyTable, RESEED, 1)
praveen
  • 12,083
  • 1
  • 41
  • 49
  • Hello, my problem is that: sometimes the loaded table is not good, if successful if the ID has reached 200, the new inserted item should be 201 not matter how many items in the list. But sometimes if the maximum item's ID is 105, the new inserted item is 106 instead of 201. This happens just "sometimes" – john White Jun 04 '12 at 08:44