INSERT error on MODIFY statement

Fred_CIT · 2008-11-26

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 · 2008-11-26

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 · 2008-11-26

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 · 2008-11-26

I don't have sql running here :oops:

garak · 2008-11-26

You're sure?
Which version do you use?

Regards

Fred_CIT · 2008-11-26

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

Savatage · 2008-11-26

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 · 2008-11-26

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 · 2008-11-26

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

bbrown · 2008-11-26

Savatage wrote:

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

Well this one's actually documented.

garak · 2008-11-27

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 · 2008-11-27

Thanks for the replies

kriki · 2008-11-28

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/

INSERT error on MODIFY statement

Comments

Categories