How to LOCK and MANTAIN the lock on a record in a FORM

Marcelo_B

Hello to all... there has been a long time since I ask for your help.... and here I am one more time.
After 2 years of working with NAV I am still learning new stuff... INCREDIBLE!

Well I am wondering.. :-k ... Is it posible to LOCK and MANTAIN the TABLELOCK on a record (or recordset) from the time a FORM is OPEN.... until it is CLOSE?

I trayed to do the following:

[b]Form - OnOpenForm()[/b]
RESET;
LOCKTABLE;

SETRANGE("Document Type", "Document Type"::Order);
SETRANGE("No.", 'SO123312');
FINDSET(TRUE,TRUE);

But when changing the record with other session, nothing happens.

On the other hand if I do this:

[b]Form - OnOpenForm()[/b]
RESET;
LOCKTABLE;

SETRANGE("Document Type", "Document Type"::Order);
SETRANGE("No.", 'SO123312');
FINDSET(TRUE,TRUE);
REPEAT
UNTIL NEXT <> 0;

The record is LOCK and the tablelock is MAINTAIN.

If you can give me any hints I´ll be more than gratefull....

Cheers,

MarceloB

gerdhuebner

The problem is, that NAV sometimes performs automatic COMMITs. For example after the OnValidate-Tabletrigger an automatic COMMIT is done, which is only relevant of course, if there were some write transactions (or LOCKTABLEs) within the trigger. (By the way, this is the reason for some mysterious Rec.MODIFYs in the OnValidate-Tabletriggers of the standard application). And - as you have tried - there seems to be a similar automatic COMMIT after the OnOpenForm-Trigger has been run through.
Presumably there are many other triggers after which an automatic COMMIT is performed, like for example the OnAfterGetCurrRecord - trigger or the OnValidate-Trigger of a form's textbox. So you may have no chance to establish a permanent LOCKTABLE while the form is open and the user navigates through it or even does some input...

bbrown

Marcelo_B wrote:

...
After 2 years of working with NAV I am still learning new stuff... INCREDIBLE!...
MarceloB

You seem surprised by this. If you've only been doing this for 2 years, you haven't even scratched the surface. Trust me. I've been working with NAV for 9 years and in this business for over 22 years. The learning NEVER stops.

Now on to your issue....

Are you using SQL or the Native DB? LOCKTABLE behaves differently. What are you trying to accomplish? I know you're trying to lock the table. But why? If the user is only viewing the record, why lock it. Have you considered the impact of that lock on other users/processes.

Marcelo_B

Hi Brown.... first of all YES, I have considered for a long time, and, as I am only locking 1 Record, the User wont complain and will be happy for this. Trust Me.
Now, I am Using SQL Server option, and I MUST Lock the Record that is being used by the User.
So, if you have any clues, I will be more than happy to hear from you...

Regards...

kine

May be there is one question which can offer other solutions. Locking the record means for you that it cannot be modified by other users, or it cannot be read? Or something else? You can use some Flag to mark the record as locked and modify the points where the record is used to check this flag or something like this... :-k

Marcelo_B

Kine, thanks for your point of view... actually I have already developed that solution, but if could LOCK the record to avoid other users to MODIFY it.... that, will be the best solution.

Regards,

gerdhuebner

Marcelo_B wrote:

... but if could LOCK the record ...

How would you lock a single record? In my opinion with the LOCKTABLE-FIND mechanism normally a whole page (of records) is locked in SQL ... (but I never tried it in detail...)

bbrown

Just a thought here...

Use ADO to manage a semaphore record in another table. Since ADO is outside of NAV's transaction control you would not have any issue with the auto or imbedded COMMIT statements. Build functions to manage the semaphore records. If one exist, for another user, then set the form as non-editable.

tobarich

Bulant, dejate de joder y ponete a laburar!!! \:D/

canadian_bacon

If you want to lock the form on one record and and maintain a LOCKTABLE:

variables
JustOpened -> boolean
OrigRec -> record, same type as form sourcetable

OnOpenForm
JustOpened := TRUE;

OnAfterGetCurrRecord
IF JustOpened THEN BEGIN
OrigRec := Rec;
JustOpened := FALSE;
END;
GET(OrigRec."No.");
LOCKTABLE;

gerdhuebner

canadian_bacon wrote:

If you want to lock the form on one record and and maintain a LOCKTABLE:

Have you ever tried this code? In NAV 2009, I get an error "You cannot make any changes in the database until a transaction has been started.
Furthermore it seems to be that there are no transactions or LOCKTABLEs possible within the OnAfterGetCurrRecord trigger at all.

gerdhuebner

gerdhuebner wrote:

In my opinion with the LOCKTABLE-FIND mechanism normally a whole page (of records) is locked in SQL ...

I have to correct myself. If you LOCK for example the last G/L Entry with the following code:

GLEntry.LOCKTABLE;
GLEntry.FINDLAST;
IF CONFIRM('Continue?') THEN
  ;

and try to modify the last G/L Entries from another client, you will notice that indeed only the last G/L Entry is locked.

canadian_bacon

gerdhuebner wrote:

canadian_bacon wrote:

If you want to lock the form on one record and and maintain a LOCKTABLE:

Have you ever tried this code? In NAV 2009, I get an error "You cannot make any changes in the database until a transaction has been started.
Furthermore it seems to be that there are no transactions or LOCKTABLEs possible within the OnAfterGetCurrRecord trigger at all.

Nope, never tried this code. Just did though and you're right. If you take away the LOCKTABLE statement the rest of the code will lock you on a specific record. I guess you could then use another table/field to maintain a "LOCK" on this record.

gerdhuebner

canadian_bacon wrote:

... the rest of the code will lock you on a specific record....

I think there are two different problems, which we are talking about now. The first, what you mean, is to open a (card) form with a specific record and the user cannot change that record for example with the page up and down buttons - he is "locked" on the record.
The other problem, what Marcelo_B meant, is to open a (card) form with an arbitrary record and as long as the user is "on" that record (as long as the record is visible in the card form) no other user should be able to make any changes to that record. The record should be "locked" for that user in the sense of the LOCKTABLE function of NAV.

Marcelo_B

gerdhuebner wrote:

canadian_bacon wrote:

... the rest of the code will lock you on a specific record....

I think there are two different problems, which we are talking about now. The first, what you mean, is to open a (card) form with a specific record and the user cannot change that record for example with the page up and down buttons - he is "locked" on the record.
The other problem, what Marcelo_B meant, is to open a (card) form with an arbitrary record and as long as the user is "on" that record (as long as the record is visible in the card form) no other user should be able to make any changes to that record. The record should be "locked" for that user in the sense of the LOCKTABLE function of NAV.

gerdhuebner you are right, that was my dilemma... so, as no "record level locking" solution came across, I decided to use a COLUMN as a Semaphore (flag). And so far everything works like a charm.
Thank you... and many thanks to everybody you have been very helpfull.

My Best Regards to all.

Administrator

[The use of [SOLVED] in the Topic Title is no longer supported. Please edit the first message in the thread and use the field Attribute (below the Subject-field) to put a green checkmark next to the Topic Title. And remove the [SOLVED] text in the Topic Title]