Re: Non-skipping IDs with Master-Detail


That is why I want to begin a transaction only when the user
saves the order, at the last moment:

*** OnButtonSaveClick

connection.BeginTrans;

try
datasetOrderID.Value := GetNextKey();
datasetOrder.Post
datasetItems.Post
except
connection.RollbackTrans;
raise;
end;

datasetOrder.UpdateBatch();
datasetItems.UpdateBatch();

connection.CommitTrans;

But as I stated, the problem here is that the master key is
only set when the user saves the order, right before
UpdateBatch. This causes problems with master-detail link
because the master key is unknown to the detail table.

But, if I call GetNextKey() before, for example OnNewRecord,
the key skips when user aborts the order.

This is a common real world situation, where order numbers
must not skip and all order information, including detail
(order items) has to be saved all at once when clicking on the
Save button.

It is so frustrating to know that a simple task like this is
so complicated to implement in Delphi!!! Or am I missing
something?? What are the logical steps to implementing this?
I've been posting so many questions til now and I'm getting
nowhere, and most importantly, time is running out.

I'd greatly appreciate your help.


Brian Bushay TeamB <BBushay@xxxxxxxxx> wrote:
>
>>Also, the order number (master ID) CANNOT SKIP, so from what I
>>read, I'm only supposed to set the next key right before
>>UpdateBatch to minimize conflicts in a multi-user environment.
>>
>>I issue a BeginTransaction right before calling my GetNextKey
>>function (which also increments the key). This way, if the user
>>aborts the order, everything gets rolled back at once.
>
>There is not much worse that you can do to create problems on an SQL server than
>to leave transactions open while a user has control of your application.
>--
>Brian Bushay (TeamB)
>Bbushay@xxxxxxxxx

.

Quantcast