Solved

database design help

Posted on 2011-03-23
6
590 Views
Last Modified: 2012-05-11
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
Comment
Question by:dinko1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 10

Expert Comment

by:Mez4343
ID: 35201106
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
 
LVL 2

Expert Comment

by:ASta
ID: 35206614
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
 
LVL 2

Expert Comment

by:ASta
ID: 35206657
add:

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

find "sqlite3_last_insert_rowid()" - in UniDAC docs/samples? :)))
0
Independent Software Vendors: 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!

 

Author Comment

by:dinko1
ID: 35206750
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
 
LVL 2

Accepted Solution

by:
ASta earned 500 total points
ID: 35206980
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
 
LVL 2

Expert Comment

by:ASta
ID: 35206997
____InvoiceItems.Edit;
____InvoiceItemsINVOICEID.AsInteger := InvoiceID.AsInteger; // ! create fileds in dataset on design
____InvoiceItems.Post;
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

742 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question