?
Solved

database design help

Posted on 2011-03-23
6
Medium Priority
?
602 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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

752 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