Insert in table where ID is autoincrement

tompynation

I have following table:
Enabled Field No. Field Name Data Type Length Description
Yes 1 ID Integer
Yes 2 ReceptID Integer
Yes 3 Regelnr Integer
Yes 4 ReceptItem Text 50
Yes 5 Hoeveelheid Decimal
Yes 6 Percentage Decimal
Yes 7 ExtraOmschrijving Text 150
Yes 8 ToonAlsVolumeJN Boolean
Yes 9 ToonTussentotaalJN Boolean

The ID field is an autoincrement field...

Now i try to insert a record inside this table using following code, but the id that the new record receives is the last ID in the table.

So the autoincrement did not work ](*,)

IF CONFIRM('Bent u zeker dat u een nieuwe rij wil toevoegen?',TRUE) THEN BEGIN
lv_ReceptItem.RESET;
lv_ReceptItem.INIT;
lv_ReceptItem.ReceptID := ReceptID;
lv_ReceptItem.Regelnr := lv_ReceptItem.GetNextRegelNr(ReceptID);
lv_ReceptItem.INSERT;
COMMIT;
FORM.RUNMODAL(50007,lv_ReceptItem);
END;

Is there anything wrong with this code ?
Or should i fill up the ID myself?

Xypher

http://www.mibuso.com/forum/viewtopic.php?t=14145


There is no real use to the: lv_ReceptItem.RESET; command you are issuing unless there are filter(s)/key attached you wish to remove prior to the FORM.RUNMODAL. (Any present filter(s)/key will not affect a record insertion)

tompynation

that doesnt work for me...

The index that get's created during the insert, is the last already existing index inside the table :?:

i changed code to this, but its without success:


IF CONFIRM('Bent u zeker dat u een nieuwe rij wil toevoegen?',TRUE) THEN BEGIN
lv_ReceptItem.INIT;
lv_ReceptItem.ID := 0;
lv_ReceptItem.ReceptID := ReceptID;
lv_ReceptItem.Regelnr := lv_ReceptItem.GetNextRegelNr(ReceptID);
lv_ReceptItem.INSERT;
COMMIT;
FORM.RUNMODAL(50007,lv_ReceptItem);
END;

Xypher

Try the same code whilst commenting out the following lines of code:

COMMIT;
FORM.RUNMODAL(50007,lv_ReceptItem);

tompynation

what is even more strange is that when i remove the autoincrement property, so i set it to No

Then execute the code with 0 value assigned to ID,
i still receive the error that it is trying to insert a record with an ID '6'
which is the last ID in my table...

SO when i place the ID to 0 it still changes to 6
????

Xypher

Do you have any code within the table?

Have you tried removing/commenting out the COMMIT (& FORM.RUNMODAL)?

tompynation

yes i tried that, but still same error

tompynation

i also rebuild the index inside SQL Server but no success...

I guess i will delete the table and just recreate it

tompynation

this is the code i have inside the table which i call before inserting:

lv_ReceptItem.GetNextRegelNr(ReceptID);



GetNextRegelNr(varReceptID : Integer) : Integer

Rec.RESET;
Rec.SETCURRENTKEY(ReceptID,Regelnr);
Rec.SETFILTER(ReceptID,'%1',varReceptID);
IF Rec.FINDLAST THEN
EXIT(Rec.Regelnr + 1);

Xypher

The reason why you are experiencing this issue is because you're making direct calls to the table which in result is setting the table's current position whilst you are issuing your 'insert' code:

Rec.FINDLAST (aka 6th = last one)

And you are trying to insert new record with that.


Personally I would not even have that function inside the table itself. I would make the entire process transparent.

kine

1) Which NAV version?
2) Some first versions using Autoincrement had problems with backup/restore etc.
3) Autoincrement is woking correctly, you need just each time to zero the primary key (INIT is not clearing primary key fields, you need to assign 0 into the field...)

Xypher

You do see his function in the very table he is attempting to insert the new record(s) right kine?

Can also see how he is making reference to the current instance (Rec) of the table which in result is changing his current position within the table.

It makes sense since he IS assigning ID = 0 and everything else before making the call to his function of 'GetNextRegelNr'

Xypher

Here is a solution you might be able to work with:

//::Receipt Item Table::

OnInsert()

  lReceiptItemRec.RESET;
  lReceiptItemRec.SETCURRENTKEY(ID);
  
  IF lReceiptItemRec.FINDLAST THEN
    ID := (lReceiptItemRec.ID + 1)
  ELSE
    ID += 1;

  lReceiptItemRec.SETCURRENTKEY(ReceiptID, Regelnr);
  lReceiptItemRec.SETFILTER(ReceiptID,'%1',ReceiptID);

  IF lReceiptItemRec.FINDLAST THEN
    Regelnr := (lReceiptItemRec.Regelnr + 1)
  ELSE
    Regelnr += 1;

Just make sure to use lv_ReceiptItem.INSERT(TRUE); when inserting the new records.

(Since you would like multiple "Auto"Increments why implement one by code and another by property? I'd rather rely on looking at one area regarding this if I have to make any changes in the future)

kine

Xypher wrote:

Here is a solution you might be able to work with:

//::Receipt Item Table::

OnInsert()

  lReceiptItemRec.RESET;
  lReceiptItemRec.SETCURRENTKEY(ID);
  
  IF lReceiptItemRec.FINDLAST THEN
    ID := (lReceiptItemRec.ID + 1)
  ELSE
    ID += 1;

  lReceiptItemRec.SETCURRENTKEY(ReceiptID, Regelnr);
  lReceiptItemRec.SETFILTER(ReceiptID,'%1',ReceiptID);

  IF lReceiptItemRec.FINDLAST THEN
    Regelnr := (lReceiptItemRec.Regelnr + 1)
  ELSE
    Regelnr += 1;

Just make sure to use lv_ReceiptItem.INSERT(TRUE); when inserting the new records.

(Since you would like multiple "Auto"Increments why implement one by code and another by property? I'd rather rely on looking at one area regarding this if I have to make any changes in the future)

I recommend to use LOCKTABLE in your code to lock the table when searching for last record. And this locking can lead to performance problems in some situation. And it is why the Autoincrement is better if you need just quickly inert the entries with minimal locking... ;-)

You do see his function in the very table he is attempting to insert the new record(s) right kine?

Can also see how he is making reference to the current instance (Rec) of the table which in result is changing his current position within the table.

It makes sense since he IS assigning ID = 0 and everything else before making the call to his function of 'GetNextRegelNr'

1) Version of the client is base info for any support...
2) Yes, I know that he tried to zero the PK. I want to make him sure that if he is doing that, it is ok and it is not the source of the problem
3) All the code is wrong because this:

IF CONFIRM('Bent u zeker dat u een nieuwe rij wil toevoegen?',TRUE) THEN BEGIN
lv_ReceptItem.INIT;
lv_ReceptItem.ID := 0;
lv_ReceptItem.ReceptID := ReceptID;
lv_ReceptItem.Regelnr := lv_ReceptItem.GetNextRegelNr(ReceptID);
lv_ReceptItem.INSERT;
COMMIT;
FORM.RUNMODAL(50007,lv_ReceptItem);
END;

PK is zeroed on variable lv_ReceptItem
Values are assigned on variable lv_ReceptItem
But the call of lv_ReceptItem.GetNextRegelNr(ReceptID) will change the record in the variable lv_ReceptItem to something other because using the Rec. It means all previous assignments are lost and in the variable lv_ReceptItem is another record. The INSERT will fail because this record already exists.

Solution is simple, just use another variable (some local variable) instead REC in the function GetNextRegelNr. 8) Nothing with autoincrement... ;-)

tompynation

great, i replaced the Rec by a variable now is everything working fine

If i dont zero value the ID the insert is also working, using NAV 5.0SP1

Thanks for the help

Xypher

You may also want to consider that if you require each unique Receipt ID have its own incremented value (Regelnr) it would be in your best interest to make your 'GetNextRegelNr' function a transparent process. (As in not needing to call the said function every time you place code to insert new or modify current records.)

kine

tompynation wrote:

If i dont zero value the ID the insert is also working, using NAV 5.0SP1

Did you tried it under MS SQL with user which is not DB_OWNER nor sysadmin?

tompynation

no i didnt, i guess i'll better put the zero value back then

kine

tompynation wrote:

no i didnt, i guess i'll better put the zero value back then

Yes, put it back. Else you will have the "SET IDENTITY" SQL permission error when customer run the code.