At 01:09 PM 01-03-02 -0600, Russ Newcomer wrote:

> >Based on your and Helen's advice, I changed my code to the following :
> >
> > With cdm.Shipdelete do
> > begin
> > If not Prepared then Prepare;
> > Params[0].AsString := ContractNum.Text + '.';
> > Execute;
> > end;
> > cdm.ShipQuery.Refresh;
>
>This code is called during an operation to, as you can see, go through the
>database to delete all the records in a database where the key matches. The
>records are shipping records, and they're a subset of a contract number.
>The contract number is generated by the generator, then the . and the number
>are added (there are multiple shipments per contract)

So you have an integer primary key (ContractNumber) that is referred to by
a character foreign key of some type?

> The user is not
>involved in selecting these unique keys. The way the program works is if
>the user decides to modify the ammount of the contract to ship, the program
>goes through the database and deletes all the shipments, and then re-creates
>a new, single shipment record. My code runs fine the first time, everything
>works the way that it is supposed to. The records are deleted, new one
>created, etc, etc. However, if I run this code again, it gives me that Key
>Violation.

OK, it looks as if either
1 - you are not committing the dataset after the deletions
and/or
2 - the dataset is not being refreshed after the deletions
and/or
3 - the transaction is not in READ COMMITTED isolation.

>
>Upon further investigation, I've found that when it recreates that new,
>single shipment record, and it calls TIBOQuery.Post is when this error
>occurs.

You will get a key violation any time you try to post a record with a
duplicate key.

>The reason it errors is because the code I've previously posted
>doesn't delete the records (any of them, whether there is just one or
>multiple records to delete) the second time through. I'm at a loss on why,
>is there some sort of clear I need to do to the DSQL before I can run it
>again?

No. But you will have to tidy up and commit the transaction in which the
dataset is running before you perform the DSQL statement. After the DSQL
statement is run *and* its transaction is committed, you will have to
refresh the dataset.

You need to constrain the application flow to this sequence:

1. Open (or refresh) dataset and let the user edit it until s/he initiates
a "Process" event.
2. On this Process event (a button-click?) do
a) pass the current ContractNumber to the params of the DSQL.
b) post the query and commit the transaction.
Here ----> test whether it has committed and, if not, return control to
the user to fix.
c) if all is OK, do
--- invalidate the grid and run the DSQL.
--- refresh the dataset (now we are back at 1).

This is now a mess. I can see quite a few problems here:

1. The dataset must uniquely identify its rows. This is what Keylinks are
for. Keylinks should contain the columns which form the primary key.

2. The input params for the DSQL statement *must* uniquely identify the
rows it is operating on.

3. Possible underlying problems with your schema.

4. It looks as if you are "updating" data by destroying it totally and
then recreating it on the basis of new input. This is *not* a safe design
principle.

I'm willing to go back through your postings and try to make sense of what
you are doing. It would help if you would send me (to my own email
address) the source for the project and the metadata script for *all* of
the relevant database objects.

regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com