Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

database design help

I have two three tables "Invoices" "Items" and "InvoiceItems".
Everytime an invoice is created I insert a new record on Invoices

InvoicesTable.Insert();

then inside the invoices form I have an option to start adding Items so I add items to the InvoiceItems.
But InvoiceItems has a reference ID on the Invoices but the ID is not  yet know:

InvoiceItems.FieldByName('invoice'id)'.AsInteger := {we dont know yet the invoice ID}
InvoiceItems.FieldByName('itemid').AsInteger := Items.FieldByName('itemid'); // item id is known
InvoiceItems.Insert;

can someone help me understand how you solve this problem?

For your information I use Delphi and UniDac with SQLite DB. Although I need help on idea/design not specifically commands of UniDac.
0
dinko1
Asked:
dinko1
  • 4
1 Solution
 
Mez4343Commented:
The referenceID on InvoiceItems is a foreign key to the Invoice table. This means that the code deisgner requires that an Invoice be created before any InvoiceItems can be assigned to the Invoice.

This is a good practice to insure data integity. So based on your snipit, I would investigate how to get the InvoicesID from the InvoicesTable.Insert() method.

Post the Insert() method code if you need more help.
0
 
AStaCommented:
See docs/samples from UniDac and SQLite DB?

- you need get invoice Id and write it to InvoiceItems (in firebird - before post using generator, in oracle - before post using sequence?, in mssql - after post using select @@identity?, in SQLite - i don't know


0
 
AStaCommented:
add:

http://www.sqlite.org/faq.html#q1

find "sqlite3_last_insert_rowid()" - in UniDAC docs/samples? :)))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dinko1Author Commented:
I already know about @@identity and last_insert_row but the problem is that Delphi handles this without query. Its a table entity and you just Table.Insert(); and when you are ready Table.Post();
0
 
AStaCommented:
I don't understand what is problem :(

I use code like this sample:

InvoicesTable.Insert;
InvoicesTable.FieldByname .... := ...;

//add 10 rec 4 sample
InvoiceItems.CachedUpdates := True; // !!! check UniDAC docs 4 it
for I := 1 to 10 do
begin
__InvoiceItems.Insert;
__InvoiceItems.FieldByName('itemid').AsInteger := Items.FieldByName('itemid'); // item id is known
  ...
__InvoiceItems.Post;
end;

// save recs 2 db:
try
__<Start transaction>
__InvoicesTable.Post; // after post Id is known???

__//fill InvoiceId in detail table
__InvoiceItems.First;
__while not InvoiceItems.Eof  do
__begin
____InvoiceItemsINVOICEID.Edit;
____InvoiceItemsINVOICEID.AsInteger := InvoiceID.AsInteger; // ! create fileds in dataset on design
____InvoiceItemsINVOICEID.Post;
__end;
__InvoiceItems.ApplyUpdates; // write chached data in db // !check UniDAC docs
__<Commit transaction>
except
__<Rollback transaction>
__on E ....
____ShowMessage(Format('Class: %s, Msg: %s', [E.ClassName, E.Message]));
end;
0
 
AStaCommented:
____InvoiceItems.Edit;
____InvoiceItemsINVOICEID.AsInteger := InvoiceID.AsInteger; // ! create fileds in dataset on design
____InvoiceItems.Post;
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now