Search code examples
databasedelphidbgrid

"key violation" autoincrement field in ClientDataSet [Delphi]

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.

Solution

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