• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

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
0
sageryd
Asked:
sageryd
  • 5
  • 4
  • 2
1 Solution
 
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
 
FreakyCommented:

Well, you could insert it via SQL directly ?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sagerydAuthor Commented:
How?
0
 
FreakyCommented:
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now