Adding a detail record with referential integrity

I have a master-table and a detail-table. The detail-table is linked to the master-table like so:

Master.ID(Autoincrement) <--> Detail.Disc(Longint)


How can I add a detail record to a newly added master-record? I can't get the Detail.Disc field right!

johan
LVL 1
sagerydAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
FreakyConnect With a Mentor Commented:
Well, I wrote a simple function which creates a query-object, executes and returns it.

like that

function DoQuery(Alias, SQLStr : String; JustExec, Live : Boolean) : TQuery;
begin
 Try
  Result:=TQuery.Create(Nil);
  With Result do
   Begin
    DatabaseName:=Alias;
    RequestLive:=Live;
    SQL.Add(SQLStr);
    Prepare;
    If JustExec then ExecSQL
                else Open;
   End;
 Except
  On E : Exception do Begin
                       Result:=Nil;
                       ShowMessage(E.Message);
                      End;
 End;
end;


Now you can call it to insert your record.

Ex

procedure InsertDetailRecord;
var Q : TQuery;
Begin
 Q:=Nil;
 Q:=DoQuery(AppDatabase.DatabaseName,
           'INSERT INTO TARGETDB (fieldname1,fieldname2,.....) VALUES (fieldvalue1, fieldvalue2, .....)',True,False);

Now you can check the Q if errors occured or just free it.

But be careful, working with SQL directly can be a tricky thing.


Greets

 Sascha
0
 
FreakyCommented:

Hi!

It should work with the following code:

If not Master.Active then Abort;
With Detail do
 Begin
  Append;
  FieldByName('Disc').AsInteger:=
Master.FieldByName('ID').AsInteger;
  Post;
 End;

Greets

 Sascha
0
 
sagerydAuthor Commented:
Well, no. That's what I've been trying. The "ID" field is an autoincrement, and it seems like it isn't updated until the master table is closed and then re-opened, but if I close it and re-open it, the cursor position will change, and I will not be able to check the ID of the last record. There must be some other way.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
FreakyCommented:

Well, you could insert it via SQL directly ?
0
 
sagerydAuthor Commented:
How?
0
 
kretzschmarCommented:
what database, sagaryd?

to freaky,
i guess your answer doesn't help,
because the value of the masterid (the autoi-field) isn't available,
when you do this.

meikl
0
 
FreakyCommented:


to kretzschmar,

yes, but you can read the mastervalue out before you add the detailrecord .. don't you think it's possible ?

Sascha
0
 
kretzschmarCommented:
to freaky,

depends on the database,
if access via odbc, then not

meikl
0
 
sagerydAuthor Commented:
Freaky, I think I will insert the records without using SQL. Thanks anyway. I found a workaround by myself to my problem, so I really don't need any help no more. Freaky, I'll give you the point anyway, I'm feeling great today!

Meikl, if you still want to know what type of database I'm using then it is a Local Paradox 7. (havn't really got so far using databases as you may know)

Thanks to ya all! ;)

//johan
0
 
FreakyCommented:

To Sageryd

I thank you sincerely and hope that the response will perhaps sometime be useful (again).


Greets

 Sascha

0
 
sagerydAuthor Commented:
Yes, maybe....


Cheers!

Johan
0
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.

All Courses

From novice to tech pro — start learning today.