Non-skipping IDs with Master-Detail


I've been looking quite a while for a solution to this and I'm
still having a lot of trouble, so please help me!

I'm creating an Order form app using ADO and SQL Server. I want
to use BatchOptimistic so the user can cancel or apply the
order after all master and detail information has been entered.

I've created a table called KeyTable which will store the next
master key (Order Number). I'm NOT using an IDENTITY/AutoInc
field. I have a function called GetNextKey() that uses TADOQry
to SELECT the next order number key and then UPDATE the column
to increment the key by 1.

The master and detail tables are linked: datasetDetail's
MasterFields property. This works fine when browsing or editing
but NOT when inserting (see below).

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.

When placing a NEW order, after I issue a POST command (not
UpdateBatch) on the DETAIL table, the record just disappears,
as if the foreign key was not set. I can't set the MASTER key
right away (using my GetNextKey function) because if another
user creates a new order simultaneously, they would both get
the same ID since UpdateBatch hasn't been called yet and
the transaction has not been commited.

On the other hand, if I call UpdateBatch up front, and the user
aborts the order, the ID has already been used, and the Order
number will skip.

I tried creating an IDENTITY field to hold an internal ID just
for Master-detail link and create a separate OrderNumber field,
but the master-detail link still doesn't work when inserting a
new order.

Somebody PLEASE help me. I really need to find a solution to
this as soon as possible!

Sorry for the long post.

.

Relevant Pages

  • Re: UpdateBatch or Post changes state to dsBrowse
    ... problem regarding Master-Detail keys. ... I'm using a detail datasource that is linked to the master ... UpdateBatch everything after the user clicks the apply button. ... >If you are not using batchupdates you will get the database errors returned on ...
    (borland.public.delphi.database.ado)
  • Non-skipping order numbers and Master-Detail links
    ... order after all master and detail information has been entered. ... I issue a BeginTransaction right before calling my GetNextKey ... UpdateBatch) on the DETAIL table, the record just disappears, ... as if the foreign key was not set. ...
    (comp.lang.pascal.delphi.databases)
  • Re: Non-skipping IDs with Master-Detail
    ... I am having a similar problem (D7 Pro, SQL Server7, ADODatasets) ... only the master is saved - not the details - ie I have ... UpdateBatch to minimize conflicts in a multi-user environment. ... I issue a BeginTransaction right before calling my GetNextKey ...
    (borland.public.delphi.database.ado)
  • Re: Non-skipping IDs with Master-Detail
    ... >Also, the order number (master ID) CANNOT SKIP, so from what I ... >UpdateBatch to minimize conflicts in a multi-user environment. ... There is not much worse that you can do to create problems on an SQL server than ...
    (borland.public.delphi.database.ado)
  • Re: BeginTrans
    ... that by caching writes to the server until you updateBatch ... >need to use a transaction to keep master and detail changes together. ... The transaction gets cleaned up on the server side but not on the client side. ...
    (borland.public.delphi.database.ado)