"key violation" autoincrement field in ClientDataSet [Delphi]

Question

Hi, this is my third question here, so far excellent responses ^^

I'm having no problems in browsing, editing the data, but insertions...

Here is my doubt: In a finance/stock software i have a form to create a new order,
naturally i need to insert a new row in t_orders table
and insert items in t_orderitems table with orderId field linked to a row in t_orders

CREATE TABLE `t_orders` (
    `orderId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `clientId` INT(10) UNSIGNED NOT NULL,
    ...)

CREATE TABLE `t_orderitems` (
    `orderitemId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `orderId` INT(10) UNSIGNED NOT NULL,
    ...)

--> INDEXES AND FOREIGN KEYS OMITTED <--

How do i add itemorders to a grid and finally in "FinalizeOrder" button click

• Create an order in t_orders
• Insert items in t_orderitems linked to that order

Connection is made using ADO.

I'm not sure if it's possible to do like this, in that case, how i should do?

edit: I tried using nested ClientDataSets and it works in parts, but i still don't know how to get the inserted Order Id

edit2:
Now i have another problem, i can't add more than one item into the ClientDataSet.
Because OrderItemId is empty for all items (i can only get that values upon database insertion), when i attempt to add a second item it gives me Key Violation, any ideas??

If i set Updatemode to something different than upWhereKeyOnly and set pfInKey to False it works but i don't think it's an option

Any ideas?

Thanks in advance!
Arthur.

Answer 1

I assume that you have an ADO dataset that gets data from your DB and is linked to the grid? What you need to do this is an intermediate layer.

Create a TClientDataset and connect it to your ADO dataset, then connect the grid to the client dataset. When a new order comes in, call Append on the client dataset and insert the data for the new order into it. This will make it show up on the grid. When you want to save your changes to the DB, call .Update on the client dataset. It'l use the ADO dataset it's linked to to send the updates to the DB. Check the documentation on TClientDataset for explanations on how to set this all up; it's one of the few things in recent versions that's actually documented really well.

To update multiple tables, look at the documentation on master/detail relations and use two datasets like this, linked to each other.

Answer 2

thanks RRUZ, this isn't exactly what i wanted (i still have to manually set all orderitems OrderId's manually before insertion), but will do it

with DataModule1.ADOQuery1 do
begin
  SQL.Text := 'SELECT LAST_INSERT_ID()';
  Open();
  First();
  LastInsertId := Fields[0].Value;
  Close();
end;

Answer 3

If you make a master-details relationship between the two datasets in your application, ADO can handle it automatically. That means, once you insert a new record into master dataset (orders), you can insert new records in your details dataset (order_items) without the need to specify order_id, because order_id for the current record in master dataset will be automatically retrieved, and inserted in the newly inserted records of details dataset.

To make a master/details relationship between your datasets, if you are using AdoTable for detail dataset, you can set its MasterSource to a datasource connected to your master dataset, and define relatinship between the two datasets using MasterFields property. If you are using AdoDataset or AdoQuery, you should set DataSource property in detail dataset to a datasource connected to your master dataset. Then you have to add a WHERE clause in your detail dataset's SQL statement using a SQL paramater with the same name as your key field in the master dataset. In your case it would be something like this:

SELECT * FROM t_orderitems WHERE OrderID = :OrderID

Now you can set your relationship in MasterFields property of your detail dataset.

Since your orders can have multiple items, you can set LockType to ltBatchOptimistic in the detail dataset (order_items), so that once you insert a new item, it is not sent to database immediately. Using ltBatchOptimistic means your changes will be saved in client memory temporally until you call UpdateBatch method. UpdateBatch sends all the changes to the database.

If you want to cancel the order, you have to call CancelBatch method to cancel modifications done on detail dataset, and delete the created order record in master dataset manually.

Answer 4

Solved http://edn.embarcadero.com/article/20847