Solved

Master/Detail Post Problem

Posted on 1998-07-21
13
716 Views
Last Modified: 2010-04-04
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
Comment
Question by:1750
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358295
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
 

Author Comment

by:1750
ID: 1358296
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
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358297
1750,

What if you store that MASTERID in a local variable and use this to assign it in the detail table (just trying..)
0
 

Author Comment

by:1750
ID: 1358298
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
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358299
Well, it's the method Inprise uses in there examples.
0
 
LVL 3

Expert Comment

by:vladika
ID: 1358300
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:vladika
ID: 1358301
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
 

Expert Comment

by:kruglov
ID: 1358302
{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
 

Author Comment

by:1750
ID: 1358303
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
 

Author Comment

by:1750
ID: 1358304
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
 

Accepted Solution

by:
buboi earned 200 total points
ID: 1358305
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
 
LVL 2

Expert Comment

by:333
ID: 1358306
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
 

Expert Comment

by:buboi
ID: 1358307
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now