Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Gaps in Autoinc Value



Permlink Replies: 4 - Last Post: Jan 17, 2018 1:03 AM Last Post By: Ian Branch
Ian Branch

Posts: 442
Registered: 9/23/99
Gaps in Autoinc Value
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 16, 2018 12:13 AM
Hi Guys,
D10.2.2, Advantage Database v 12.
I am using a data table via a TTable with a primary index field that is an AutoInc in a Customer application.
Mostly all works fine but every now and then it will skip an Autoinc value. I had in the past attributed this to
something the User was doing to lose the value. Perhaps crashing the app or something although there was never any
evidence. Today the Customer advised that it skipped 3 numbers.
As best I can tell I have everything tied down OK as far as inserting, aborting & posting.
Any thoughts/suggestions as to where to look appreciated.

Regards & TIA,
Ian
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Gaps in Autoinc Value
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 16, 2018 8:20 AM   in response to: Ian Branch in response to: Ian Branch
While inserting a record and you get a database error, like the string is too long for a field (Trucate message..), then the Inc number is lost when canceling the operation.
The database takes the next value and then does the opertation. It will not rollback the number if the operation fails.
But why are you or your customer looking to a primairy database key ID ?
Ian Branch

Posts: 442
Registered: 9/23/99
Re: Gaps in Autoinc Value
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 16, 2018 12:08 PM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:

While inserting a record and you get a database error, like the string is too long for a field (Trucate message..),
then the Inc number is lost when canceling the operation.

Hi Robert,
Hmmm. I use TTable.Cancel when the user or the App wamts to cancel the new record. IIUC you are saying that this
will automatically lose the Inc #?
Is there some way around this?

The autoinc # is used as the rows Data ID don't care about the specific record #.

Regards,
Ian
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Gaps in Autoinc Value
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 17, 2018 12:01 AM   in response to: Ian Branch in response to: Ian Branch
Is there some way around this?
No, not if you handle the key by the database.

Read the 4 points of the remarks on the following page:
https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx

[DENTITY (Property) (Transact-SQL)|https://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx]

Edited by: Robert Triest on Jan 17, 2018 9:01 AM
Ian Branch

Posts: 442
Registered: 9/23/99
Re: Gaps in Autoinc Value [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 17, 2018 1:03 AM   in response to: Robert Triest in response to: Robert Triest
Thanks Robert,
Confirms pretty much what I had come to believe.
Thanks for taking the time.
Regards,
Ian
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02