Autoincrement Error?

Dakkon

I have a table I created in Navision SQL 4.0 SP2. This table has a field called ID that is the primary key (integer) and I recently changed it to an autoinrement for my testing. The code I'm using to insert records doesn't assign the number as you can see below:

StatusWin.OPEN('Inserting test records\Record #1#############');
FOR I := 50000 TO 100000 DO
BEGIN
   StatusWin.UPDATE(1,I);
   Rec.INIT;
   //Rec.ID := I;
   Rec.RandomCode := 'AHJYEFASEJ';
   Rec.RandomText := 'sdf;alkjewrkewj;we';
   RANDOMIZE();
   Rec.SomeNumber := 100 * 100 / 5 + 200 + RANDOM(1000);
   Rec.DateTime := CURRENTDATETIME();
   Rec.INSERT(TRUE);
END;
StatusWin.CLOSE;

For some reason every time I try to run this code I'll get an error of:

The Test Entry already exists.
Identification fields and values:
ID='x'

where x = the next available number. When this happens it never actually inserts any records, however, it does increase the autoincrement seed number. Anyone know why this would happen?

Captain_DX4

If you changed it to AutoIncrement in SQL, that does not work for Navision. You will need to put in Navision code to find the last entry and increment the value, and assign that value to the new record.

garak

right, Navision doesnt see the SQL properties from SQL Server Tabel you have set in SQL Manager (or with other tools). also navision doesnt see Tabels which you have created with an 3-rd party program (like Enterprise manager). The reason is: This Tables ar not included in the Object Table.

regards

Dakkon

Actually I changed it in the Navision properties for the field. Nothing was changed from anywhere other than inside Navision.

ara3n

That property only works in sql.

Dakkon

We're using SQL. Navision SQL 4.0 SP2. Incidentally this error only happens when I insert via code. If you manually insert records into the table the autoincrement works as one would expect it to.

kine

Do not forget that INIT don't clear fields of primary key! To be able to use autoincrement, you need to assign 0 to the PK field before you do INSERT.

Dakkon

You rock dear sir!! Initializing with 0 does indeed solve the issue.

kine

8)

Captain_DX4

Nifty! I didn't even remember that property was there.

kine

It wasn't. It is new property from 4.00 (or 3.70? I do not know now).

ara3n

One issue with that property is that if you are on sql and you try to populate the field, you'll a wierd error if you are not a dbowner. I think there are a couple of threads about it.

kine

It is not weird and it is what we are solving (it is solved...) there... :-)

It is not weird if you take it from SQL point of view. This field has connected some identity (step, seed, etc.). This is some counter which is automatically increased when new record is inserted. When you insert record with this field filled in with some value, to prevent inserting same value through autoincrement, the counter must be set to this new manually inserted value to continue in counting. Bud because this identity is part of metadata of the table, you need to have enough rights to do that.

TrippyZ

Thanks guys, this was driving me nuts!

========================
Do not forget that INIT don't clear fields of primary key! To be able to use autoincrement, you need to assign 0 to the PK field before you do INSERT.
_________________
Kamil Sacek

suvidhab_s

=D> Thxx \:D/