Solved

Master/Detail Post Problem

Posted on 1998-07-21
13
724 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
automatically logging in via bde delphi 3 68
delphi prevent click fast 2 189
Create Database on Android via Delphi dbExpress 3 79
control image tags in a string ? 12 110
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

919 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

15 Experts available now in Live!

Get 1:1 Help Now