INSERT error on MODIFY statement

Fred_CIT

Consider the code below when executed on a SQL server

gRecSalesLine.INIT;
gRecSalesLine."Document Type" := gRecSalesLine."Document Type"::Quote;
gRecSalesLine."Document No." := 'XXXXXXXXXXXXX';
gRecSalesLine."Line No." := 20000;
MESSAGE('FIRST INSERT');
gRecSalesLine.INSERT;

MESSAGE('SECOND INSERT');
gRecSalesLine.INSERT;

MESSAGE('MODIFY');
gRecSalesLine.MODIFY;

When I execute this code (on SQL) I receive a error 'The Sales Line already exists' on the MODIFY statement and not as expected on the INSERT statement.
I get the impressions it has something to do with database caching, but could someone explain me the precise reason?

Savatage

How do you know it's on the modify statement? did you use the debugger?

personally I would put the message after the statement. If the statement errors then I would not see the message.

Fred_CIT

Both the debugger and the generated messages tell you the error occurs on the MODIFY statement. You may even put a load of extra code between the Insert and Modify statement; the result stays the same.

Just copy paste the code to a new codeunit and add a global variable 'gRecSalesLine' (Record 37). Run it on a SQL and see it for yourself.

Savatage

I don't have sql running here :oops:

garak

You're sure?
Which version do you use?

Regards

Fred_CIT

I'm using NAV 5.0 SP1 and SQL 2005
Anyone can confirm this issue?

Savatage

Anyone can confirm this issue?

I can't confirm it myself, perhaps someone else, I don't have sql avaialable here.
But it really wouldn't be an issue if the code is written correctly would it.?! :-k

Personally I would never write something like that anyway :whistle:

bbrown

This is happening in SP1 because of delayed inserts (new SP1 feature). The inserts to the database do not occur until the MODIFY is called. Try (for test only) putting a commit after each statement to force it to the DB. You will see the error message after the second insert.

Savatage

Oh, it's a new "Feature" you call it

bbrown

Savatage wrote:

Oh, it's a new "Feature" you call it

Well this one's actually documented.

garak

ok, 5.01.
I've tested with 4.03.
Your "phenomenon" is based on the "delay" insert (as bbrown said).
Check the release notes of 5.01.

Regards

Fred_CIT

Thanks for the replies

kriki

And I also blogged about the NAV 5.0SP1 bulk-insert possibility...
http://mibuso.com/blogs/kriki/2008/04/01/sql-changes-for-50sp1/
http://mibuso.com/blogs/kriki/2008/11/14/nav500sp1-bulk-insert-and-code-testing-on-sql/