I have 3 tables on the server:

TLSCase
TLSCaseTraffic
TLSCaseDiviorce.

The second table is and extension of the data in the first table, so
TLSCaseTraffic holds traffic data for the case in TLSCase

Within the DB i've got  PK-FK constraints linking TLSCase &
TLSCaseTraffic.

Within the ADODataset I have the string:

select TLSCaseTraffic.TLSCaseID, UIDCase, TLSCaseTypeID, TLSAspectID,
OpenDate, CloseDate, TLSTrafficOffenceID, Location, Date ,
TLSCase.TLSCaseID as TLSCaseTLSCaseID
FROM TLSCase
 INNER  JOIN
 TLSCaseTraffic
ON
 TLSCaseTraffic.TLSCaseID = TLSCase.TLSCaseID

and I have an on before post that makes the TLSCaseTLSCaseID.Value :=
TLSCaseID.Value

If I have the PK-FK from TLSCase to TLSCaseTraffic then I can insert a
record, the resulting SQL sent to the DB is broken into 2 parts...

Insert xxxxx INTO TLSCase
Insert xxxxx INTO TLSCaseTraffic

This is fine, but when I delete the record the SQL sent to the server
is also ordered TLSCase, TLSCaseTraffic... this then fails on a
constraint error.

If I change the PK-FK to be TLSCaseTraffic to TLSCase then inserts
fail, because the SQL trys to insert the FK before the PK, but if the
records alread exist then it can delete sucessfully.

I've tried to re-order the SQL in the dataset to see if this somehow
hints at which file should get SQL'd first, but it always uses TLSCase
then TLSCaseTraffic.

The problem is that the ADO is clever enough to break the SQL into 2
parts for the updates/deletes but not clever enough to realise that
the order of inserts must be the inverse of the order of deletes...

Is there any way to give a hint to ADO on which order should be used
so that I can do something like...

BeforeDELETE
        ADO.FileOrder := 'TLSCaseTraffic; TLSCase'
end

BeforeINSERT
        ADO.FileOrder := ;TLSCase; TLSCaseTraffic'
end

I guess the important question is, who handles the seperating of the
original SQL into 2 SQL statements, is it borlands VCL or is it the
ADO?

If the ADO then I guess/hope there is a property for this?

If I really have to I can have 3 ADO datasets, one for the SELECT and
the other 2 for the updates/deletes.... but then how do I use the
normal DB aware controls, and pass the changes to the other ADO
datasets for processing, while allowing the SELECT dataset to show any
changes the current user made? I guess there must be someway to tell
the ADO to cancel the updates yet still allow the ADOdataset to think
it was sucesfull?

Jon
--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

Why not have two datasets, one for TLSCase and another for TLSCaseTraffic.
Set the parameter for TLSCaseTraffic so it links back to TLSCase and make
your data buttons (add, delete, etc.) point to TLSCase.  Then, in the
AfterInsert of TLSCase you can manually append a record to TLSCaseTraffic
and in the BeforeDelete of TLSCase you could manually delete the
TLSCaseTraffic record.

HTH,

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

news:tphbkt44tk43k944l3oj041qi9m1aihvqe@4ax.com...

On Fri, 6 Jul 2001 10:46:50 -0500, "Michael Rodriguez"

I had thought of setting a detail/master relationship up (although
there would only ever be a 1-1 relationship) but three problems occur,

The first is that as each TLSxxx is read the program fires off a
select SQL to the other TLSxxx2. so the over head can be significent.

The second problem is that I need to somehow enclose both datasets
into 1 single commit processes to prevent partial updates.

The third is when the records presented need to be selected based on
the value of fields from both files.

None of my forms allow "grid" editing, rather the program presents a
list that the user selects items from, these are then displayed one at
a time for manipulation/deletion.

What I am now wondering is, how can I perform changes to a dataset
without the changes being imposed on the underlying database?

Thanks

Jon

--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

You will always have a select fired against both tables, no matter what you
do.  Not sure I understand this one.

Use a lock type of ltBatchOptimistic and commit all of the changes at once
inside of a transaction.

When you show the records available for selection, use a separate read-only
query that joins the two tables together.  Then, when they find the record
they want, go back to your two datasets for editing.

That should work fine.

???

--
Michael Rodriguez
Compeat Restaurant Accounting Systems
http://www.compeat.com
m...@compeat.com

On Fri, 6 Jul 2001 14:30:35 -0500, "Michael Rodriguez"

This is negated by your sugestion to have a read only set for the list
presentation. It would only have been a problem if the list was
generated via 2 seperate datasets, as each record in the primary table
was read then the program would have fired the select for the second
dataset.

The problem would have been similar to the way the TDBNav control
performs a refresh, instead of re-issuing the original SQL it performs
a select for every record currently in the dataset.... boy is this
slow and inefficient, or what!

With ADO is the transaction based upon the connection? So if I have
many datasets that I want to be under a common transaction control
they need to use the same connection to link them all together?

Also does ADO allow nested transaction? While I'm sure I don't need
this facility, it may be useful where I modularise my code, so I don't
need to keep track of who/where the commits begin and end, so I don't
end up commiting a transaction for one part of the program while
another part performs it's own commit.

The read only dataset you suggest needs to be "re-freshed", or at
least kept in sync with the current users changes.

If the user has added 3 records, deleted 1, and amended 4 records on
the updateable datasets [edit form], then the program aught to reflect
these changes in the read only dataset [list form].

I realise that I can just close and open the read only dataset,
forcing it to re-run the SQL, but if this takes 30 seconds then the
user is forced to wait.

I know from experience that the users will not be worried about seeing
changes performed by other users in "live time", they know that to see
the current "live" data off everyone will involve refreshing the
lists. But they will have a problem if they have deleted a record on
the edit form, but it still shows in the list form.  

Is it possible to have an updateable dataset that is "disconnected"
from the database? If this is possible then I could perform the
updates/changes/deletes in the "list dataset" for presentation
purposes, then just drop the dataset and discard the changes when the
user manually requests the list is refreshed from the backend
database.

The "real" updateable datasets that are used to present the edit
screen would perform live updates and selects as the user presses the
Apply button.

Jon

--

Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

news:fggdkt8pf3q98635mj56c8lp0u4ml9hakc@4ax.com...

Transactions in ADO are based on connections. For me is good idea to use one
connection with many datasets as possible. Each ADO Connection is overhead
of program.

ADO support nested transaction by design! But not all providers support this
feature! Nested transaction are very useful, but when you make one whole
system that use in many places nested transactions and then you try to
change ADO provider and it say that nested transaction is not supported -
then I want to kill someone ;).

This is true for Jet provider and MSSQL provider: second do not support of
nested transactions.

I not understand why you do not need to keep track of begin/commits/rollback
of transactions. With nested transaction you may make code of program more
clear and logical, but you stay need to keep track of your transactions.

I think that you cannot go on from this need.

I think that this problem is the same as one user delete record - other user
will still see it!