Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Master/Detail Post Problem

Posted on 1998-07-21
13
Medium Priority
?
761 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

972 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