autoincrement field increases wrong

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 21
Joined: Fri 09 Nov 2007 23:29

autoincrement field increases wrong

Post by hmelihkara » Sat 21 Feb 2009 00:46

Hi All,

I'm using:

Code: Select all

Delphi: BDS 2009 with all updates installed.
SDAC: Standart.
SQL Server: SQL Express 2008 Latest
OS: Windows Vista 64-Bit Up to Date with 4GB Ram
I have a table:

Code: Select all

CREATE TABLE [dbo].[gunlukkur] (
  [gunlukkurid] bigint IDENTITY(1, 1) NOT NULL,
  [gunlukkur] money CONSTRAINT [DF__gunlukkur__gunlu__060DEAE8] DEFAULT 0 NOT NULL,
  [parabirimiid] bigint CONSTRAINT [DF__gunlukkur__parab__07020F21] DEFAULT 0 NOT NULL,
  [parabirimi] varchar(5) COLLATE Turkish_CI_AS NOT NULL,
  [tarih] datetime NOT NULL,
  [tstamp] timestamp NULL,
  CONSTRAINT [gunlukkur_pk] PRIMARY KEY CLUSTERED ([gunlukkurid])
When i simply try to enter a record i sometimes got error:

Code: Select all

...binary data or string truncated error...
Now as i read this problem will fixed in the next reelease of sdac (when will it be released?).

For eg: the latest Autoincrement field value is: 5,
if i did not got any errors the new value will be 6.

But after getting every error, the autoincrement field continues increasing / counting. But there's no data after getting the errors.
For eg: If I got 3 errors; the next successful record's autoincrement value be 9 (cause i got 3 errors this means, 6-7-8 numbered autoincrement numbers fly away), but the next successful record must be 6 cause no data entered to the table for caused by errors...

I'm using DBedits (Devexpress) and Query.Append & Query.Post for new records... not insert into queries...

Is there any way to defeat this?

thanks for helps...

Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Tue 24 Feb 2009 08:37

This problem with autoincrement fields is not connected with SDAC. SQL Server uses its own mechanism of generation unique increment values of field and there is not possibility to control this process.

Posts: 8
Joined: Fri 20 Mar 2009 11:26

Post by shreq » Wed 15 Apr 2009 12:31

If you need to reset the identity value, you can use DBCC CHECKIDENT:

DBCC CHECKIDENT (tablename, RESEED, value)

where value can be last_used_identity for example :-)

Anyway remember: identity fields aren't sequential. They're only guaranteed to be unique in the table.

Post Reply