SQL problem

iqbalmad · 2009-08-28

Hi guys,

am using the latest version of SQL.
I have written some codes in which i am inserting records in the table Change Log Entry.

On the form i have given insert permissions.

But i get a message like:

1088,"42000", [Licrosoft][ODBC SQL Server Driver][SQL Server] Object XXX.dbo.Cronus$Change Log Entry" cannot be found, as it doesn't exist or u do not have sufficient permissions .

SQL:
SET IDENTITY_INSERT "XXX.dbo.Cronus$Change Log Entry" ON

The same codes work fine in standard nav.

Can you pls help.

SigGunn · 2009-08-28

This error occurs if you are giving "Entry No." value other than 0 in your code, since this field has property AutoIncrement set as Yes.

See http://www.mibuso.com/forum/viewtopic.php?f=23&t=18411&hilit=SET+IDENTITY

Also have a look at codeunit 423 "Change Log Management", function InsertLogEntry to see how Navision does this.

iqbalmad · 2009-08-29

What should i change to make these codes work for sql??

IF NOT CONFIRM(Text001,FALSE,"Statement No.") THEN
EXIT
ELSE
BEGIN
IF ChangeLogEntry.FINDLAST THEN
LastEntryNum := ChangeLogEntry."Entry No.";

BankAccLedEntry.SETRANGE("Statement No.","Statement No.");
IF BankAccLedEntry.FINDSET(TRUE,TRUE) THEN
REPEAT
BankAccLedEntry.Open := TRUE;
BankAccLedEntry.MODIFY;

ChangeLogEntry.INIT;
ChangeLogEntry."Entry No." := LastEntryNum + 1;
ChangeLogEntry."Date and Time" := CURRENTDATETIME;
ChangeLogEntry."User ID" := USERID;
ChangeLogEntry.Time:=TIME;
ChangeLogEntry."Table No." := 271;
ChangeLogEntry."Primary Key" := 'N° de séquence:'+ FORMAT(BankAccLedEntry."Entry No.");
ChangeLogEntry."Field No.":= 36;
ChangeLogEntry."Type of Change" := ChangeLogEntry."Type of Change"::Modification;
ChangeLogEntry."Old Value" := 'N° de séquence:'+ FORMAT(BankAccLedEntry."Entry No.") + ' ' + 'False';
ChangeLogEntry."New Value" := 'N° de séquence:'+ FORMAT(BankAccLedEntry."Entry No.") + ' ' + 'True';
ChangeLogEntry.INSERT;
LastEntryNum += 1;
UNTIL BankAccLedEntry.NEXT = 0;

MESSAGE(Text002);
END;

Luc_VanDyck · 2009-08-29

iqbalmad wrote:

What should i change to make these codes work for sql??

The reply was

This error occurs if you are giving "Entry No." value other than 0 in your code, since this field has property AutoIncrement set as Yes.

so you'll have to remove this line:

ChangeLogEntry."Entry No." := LastEntryNum + 1;

iqbalmad · 2009-08-29

i did that Luc. But i was getting another error message that line already exists.

Luc_VanDyck · 2009-08-29

Probably because the Insert-trigger is not executed.

But you better use the existing functions in the ChangeLog codeunit to update that table, instead of inserting the records yourself. Have a look at Change Log for records modified with a codeunit how it's done.

kine · 2009-08-30

iqbalmad wrote:

i did that Luc. But i was getting another error message that line already exists.

.INIT is not clearing primary key. You need to assign 0 to the field (it means not remove the line but change it to

ChangeLogEntry."Entry No." := 0;

but you already knows that if you have used the search functionality... ;-)

kriki · 2009-08-31

kine wrote:
iqbalmad wrote:

i did that Luc. But i was getting another error message that line already exists.

.INIT is not clearing primary key. You need to assign 0 to the field (it means not remove the line but change it to
ChangeLogEntry."Entry No." := 0;

Better use CLEAR(ChangeLogEntry);

It cleans a lot better. (BTW : http://www.mibuso.com/howtoinfo.asp?FileID=22 )

kine · 2009-08-31

Yes, but sometime it have side-effect of clearing the variables inside the "object"... ;-

kriki · 2009-09-01

kine wrote:

Yes, but sometime it have side-effect of clearing the variables inside the "object"... ;-

True, it doesn't clear temptables in the object to be CLEAR-ed.

SQL problem

Comments

Categories