[HELP] Modify only works with SQL db_owner

xina_man · 2009-06-02

Hi there everyone!

I need to update a field on the item table and I'm using the code below on the OnRun() Trigger of a button placed in the item list form.

Window.OPEN(Text001 + '@1@@@@@@@@@@@@\');
Window.UPDATE(1,0);
TotalRecNo := Rec.COUNT;
RecNo:=0;

itemTable.RESET;
itemTable.FIND('-');
itemTable.MODIFYALL("Qtd Calculo Necessidades", 0);

Rec.FIND('-');
REPEAT
   Rec.CALCFIELDS(Inventory2);
   Rec.VALIDATE("Qtd Calculo Necessidades", Inventory2);
   Rec.MODIFY;
   RecNo+=1;
   Window.UPDATE(1, ROUND(RecNo/TotalRecNo*10000,1));
UNTIL Rec.NEXT = 0;

FORM.RUN(50069);

My problem is that the code above only works if the user as the db_owner permision. Otherwise it doesn't update the required field.
I've given the premision to RMID for the Item Table, sinchronized the user and it doesn't work...
Is there any problem with my code? How can I solve this issue?

Thans in advance

DenSter · 2009-06-02

No you don't need db_owner to be able to modify data in tables. All that regular NAV users need on SQL Server is 'public', db_owner is only for users who need to make object changes. You need to make sure that the user has proper permissions through their role assignments.

slmaluwa · 2009-06-02

Or, that Security is synchronized in NAV Client

xina_man · 2009-06-02

DenSter wrote:

No you don't need db_owner to be able to modify data in tables. All that regular NAV users need on SQL Server is 'public', db_owner is only for users who need to make object changes. You need to make sure that the user has proper permissions through their role assignments.

But the role is updated and the user is synchronized...

I've given the SUPER role to the user, after that synchonized him and it still doesn't work...
It only works properly after I give him the db_owner permission. I know it is strange... I hope that someone in mibuso can give me a possible solution for this...

Thanks

kine · 2009-06-02

And can you post the exact error you get when trying with no db_owner permissions? ;-)

xina_man · 2009-06-02

kine wrote:

And can you post the exact error you get when trying with no db_owner permissions? ;-)

The problem is that I dont get an error message.
The Field that I want to modify doesn't get updated... after the routine it stays just as it was before.
If I give the db_owner permission the field is updated.

kriki · 2009-06-03

When you loop a table and change some fields in each record, it is best to use an extra record-variable
I changed your code to do this:

Window.OPEN(Text001 + '@1@@@@@@@@@@@@\');
Window.UPDATE(1,0);
TotalRecNo := Rec.COUNT;
RecNo:=0;

itemTable.RESET;
// NO NEED TO DO A FIND: itemTable.FIND('-');
itemTable.MODIFYALL("Qtd Calculo Necessidades", 0);

Rec.FIND('-'); // or better : Rec.FINDSET(TRUE,TRUE);
REPEAT
   rec2YourTable := Rec;
   rec2YourTable.CALCFIELDS(Inventory2);
   rec2YourTable.VALIDATE("Qtd Calculo Necessidades", Inventory2);
   rec2YourTable.MODIFY;

   RecNo+=1;
//> this is for performance : update the dialogbox each 100 records.
   IF RecNo DIV 100 = 0 THEN
//< this is for performance
   Window.UPDATE(1, ROUND(RecNo/TotalRecNo*10000,1));
UNTIL Rec.NEXT = 0;

Window.CLOSE; // best to close the dialogbox before running a form

FORM.RUN(FORM::"Your Form"); // best use this instead of the ID. It makes upgrading easier

xina_man · 2009-06-04

kriki wrote:

When you loop a table and change some fields in each record, it is best to use an extra record-variable
I changed your code to do this:

Window.OPEN(Text001 + '@1@@@@@@@@@@@@\');
Window.UPDATE(1,0);
TotalRecNo := Rec.COUNT;
RecNo:=0;

itemTable.RESET;
// NO NEED TO DO A FIND: itemTable.FIND('-');
itemTable.MODIFYALL("Qtd Calculo Necessidades", 0);

Rec.FIND('-'); // or better : Rec.FINDSET(TRUE,TRUE);
REPEAT
   rec2YourTable := Rec;
   rec2YourTable.CALCFIELDS(Inventory2);
   rec2YourTable.VALIDATE("Qtd Calculo Necessidades", Inventory2);
   rec2YourTable.MODIFY;

   RecNo+=1;
//> this is for performance : update the dialogbox each 100 records.
   IF RecNo DIV 100 = 0 THEN
//< this is for performance
   Window.UPDATE(1, ROUND(RecNo/TotalRecNo*10000,1));
UNTIL Rec.NEXT = 0;

Window.CLOSE; // best to close the dialogbox before running a form

FORM.RUN(FORM::"Your Form"); // best use this instead of the ID. It makes upgrading easier

Thank you for your reply.

I've made those changes but unfortunately my problem continues...
It doesn't update the required Item table field...
But, by the way, thank you for those programing guidelines!

kriki · 2009-06-08

Run the code with the client monitor and check what "itemTable.MODIFYALL("Qtd Calculo Necessidades", 0);" does (=what query does it send to SQL).

[HELP] Modify only works with SQL db_owner

Comments

Categories