Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • Last Modified:

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
0
1750
Asked:
1750
  • 4
  • 3
  • 2
  • +3
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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