Master/Detail Post Problem

I have two tables in a master-detail arrangement.  The master table has an AutoIncrement ID field, called TRANSID.
The detail table also has a TRANSID field, to link records in the detail table to a record in the master table.

I Add a record to the MasterTable, then need to add a series of records to the detail table with the appropriate TRANSID field:

{START CODE--------------------------}

MasterTable.Append;
{the MasterTableTRANSID field is not set}
MasterTableField1.AsInteger:=blah;
MasterTableField2.AsString:=blah;
MasterTable.Post;

{now the detail table}
DetailTable.Insert;
DetailTableTRANSID.AsInteger:=MasterTableTRANSID.AsInteger;
DetailTableField1.AsInteger:=whatever;
DetailTable.Post;

{END CODE--------------------------}

problem is, when I go to post the detail table, it causes a referential integrity violation, and if I check the value of the MasterTableTRANSID field after the MasterTable.Post, it is zero - it should be the automatically allocated value of the TRANSID field.

This is in Delphi 3, using the ODBC MS-Access driver.

any clues?

ben
1750Asked:
Who is Participating?
 
buboiCommented:
1. AutoIncrement field has no actual value before post
2. after post, the current record is not exist, you should locate the append record.
Delphi Programming Question
    Title: "Master/Detail Post Problem"

    From: 1750
                                                          Date: Tuesday, July 21 1998 - 01:15AM PDT
    Status: Waiting for answer
    Points: 200 Points (Hard)


    I have two tables in a master-detail arrangement.  The master table has an
    AutoIncrement ID field, called TRANSID.
    The detail table also has a TRANSID field, to link records in the detail table to a
    record in the master table.

    I Add a record to the MasterTable, then need to add a series of records to the
    detail table with the appropriate TRANSID field:

    {START CODE--------------------------}

    MasterTable.Append;
    {the MasterTableTRANSID field is not set}
    MasterTableField1.AsInteger:=blah;
    MasterTableField2.AsString:=blah;
    MasterTable.Post;
    MasterTable.Last; //set the current record;

    {now the detail table}
    DetailTable.Insert;
    DetailTableTRANSID.AsInteger:=MasterTableTRANSID.AsInteger;
    DetailTableField1.AsInteger:=whatever;
    DetailTable.Post;

    {END CODE--------------------------}
0
 
ZifNabCommented:
Hi 1750,

what if you do this :

{START CODE--------------------------}

MasterTable.Append;
{the MasterTableTRANSID field is not set}
MasterTableField1.AsInteger:=blah;
MasterTableField2.AsString:=blah;

{now the detail table}
DetailTable.Insert;
DetailTableTRANSID.AsInteger:=MasterTableTRANSID.AsInteger;
DetailTableField1.AsInteger:=whatever;
DetailTable.Post;

MasterTable.Post;
{END CODE--------------------------}

Posting the mastertable at last? Just try it.

Zif.
0
 
1750Author Commented:
Nope, tha6t doesnt help.  Same error.  BUT, I just noticed that with the original code, if I look at the database after the crunch, it HAS added the master record.  after your suggested change, the master record isnt added (logically enough...)

its almost like it takes a finite time to do the post, and i'm jumping the gun trying to add the detail records so quickly...

still, doesnt solve my problem, but thanx for the suggestion.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ZifNabCommented:
1750,

What if you store that MASTERID in a local variable and use this to assign it in the detail table (just trying..)
0
 
1750Author Commented:
even if i store it in a local variable, its still zero, not what it should be (after the post).  It's been suggested that I abandon the autoincrement field and create another table to store the 'next transaction number'.  This sounds like a particularly ugly way to solve the problem tho...
0
 
ZifNabCommented:
Well, it's the method Inprise uses in there examples.
0
 
vladikaCommented:
I think the problem is:
When you call MasterTable.Post Delphi call
INSERT INTO (...) VALUES (...)  (see it in the SQL Monitor)
But you cannot get back autoincremented TransID
How can you get this TransID, I do not know.

You may call
  MasterTable.Close;
  MasterTable.Open;
before inserting into DetailTable.
Then you will see right TransID.

But before inserting into DetailTable
you must position MasterTable on the same record which was before Close;
How can you do it with 100% I do not know.
Maybe you have other unique key. If you have it you can call Locate for example.
(i.e before MasterTable.Post you remember other unique key value, then post, close, open,
locate(remembered unique key value), insert into DetailTable)


So, the possible solution:
You can abandon from using autoinc field as primery key.
To generate unique number you can use technique as in MastApp example
see for example procedure TMastData.OrdersNewRecord(DataSet: TDataSet);

0
 
vladikaCommented:
Sorry,
I did not notice your comment
"It's been suggested that I abandon the
autoincrement field and create another table to store the 'next transaction number'"

0
 
kruglovCommented:
{START CODE--------------------------}

MasterTable.Append;
{the MasterTableTRANSID field is not set}
MasterTableField1.AsInteger:=blah;
MasterTableField2.AsString:=blah;
MasterTable.Post;

{now the detail table}
DetailTable.Insert;
{after this procedure automatically
DetailTableTRANSID.AsInteger:=MasterTableTRANSID.AsInteger;}
{exclude this code
    vvvvvvvv   }
DetailTableTRANSID.AsInteger:=MasterTableTRANSID.AsInteger;
DetailTableField1.AsInteger:=whatever;
DetailTable.Post;

{END CODE--------------------------}

0
 
1750Author Commented:
sorry kruglov, doesnt work.

it seems to be the ODBC driver thats causing the problem.  when I run the test program with the native access driver, it works.  when I use ODBC, it crashes.  something to do with the way the odbc driver handles autoincrement fields, I think.

thanks anyway
0
 
1750Author Commented:
just a bit more info, the code works with the native access driver, but ODBC Access, ODBC SQL-Server and native SQL-Server dont work, all giving the error 'referential integrity rules require a related record in MasterTable'
0
 
333Commented:
Can't you use a simple Number field instead of auto increment field?
something like this:

.
var
  ID:longint;
.
procedure TForm1.MasterTableBeforePost(DataSet: TDataSet);
begin
  inc(ID);
  MasterTableTRANSID.AsInteger:=ID;
end;

procedure TForm1.DetailTableBeforePost(DataSet: TDataSet);
begin
  DetaiTableTRANSID.AsInteger:=ID;
end;

buboi,
I don't know, but will this work with indexed tables also?

AP
0
 
buboiCommented:
AP,

your method is also a good answer.

because we write these code based on the BDE driver, it work consistantly for most database.

Buboi
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.