Improve company productivity with a Business Account.Sign Up

x
?
Solved

database design help

Posted on 2011-03-23
6
Medium Priority
?
618 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
  • 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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

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 2000 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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

606 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