Options

Autoincrement Error?

DakkonDakkon Member Posts: 192
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?
Thad Ryker
I traded my sanity for a railgun :mrgreen:

Comments

  • Options
    Captain_DX4Captain_DX4 Member Posts: 230
    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.
    Kristopher Webb
    Microsoft Dynamics NAV Developer
  • Options
    garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • Options
    DakkonDakkon Member Posts: 192
    Actually I changed it in the Navision properties for the field. Nothing was changed from anywhere other than inside Navision.
    Thad Ryker
    I traded my sanity for a railgun :mrgreen:
  • Options
    ara3nara3n Member Posts: 9,256
    That property only works in sql.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    DakkonDakkon Member Posts: 192
    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.
    Thad Ryker
    I traded my sanity for a railgun :mrgreen:
  • Options
    kinekine Member Posts: 12,562
    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
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    DakkonDakkon Member Posts: 192
    You rock dear sir!! Initializing with 0 does indeed solve the issue.
    Thad Ryker
    I traded my sanity for a railgun :mrgreen:
  • Options
    kinekine Member Posts: 12,562
    8)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    Captain_DX4Captain_DX4 Member Posts: 230
    Nifty! I didn't even remember that property was there.
    Kristopher Webb
    Microsoft Dynamics NAV Developer
  • Options
    kinekine Member Posts: 12,562
    It wasn't. It is new property from 4.00 (or 3.70? I do not know now).
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    ara3nara3n Member Posts: 9,256
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    kinekine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    TrippyZTrippyZ Member Posts: 87
    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
    Professional Navision Developer
  • Options
    suvidhab_ssuvidhab_s Member Posts: 16
    =D> Thxx \:D/
Sign In or Register to comment.