Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

database design help

Posted on 2011-03-23
6
Medium Priority
?
609 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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