Board index » delphi » Auto Increment Fields and Master Details Relationship

Auto Increment Fields and Master Details Relationship

Hello

In one of my application I have used autoincrement fields. In a master
detailed setup, I have observed that autoincrement field get filled up
only on a post and not on the creation of record itself. This creates
abnoramlity in a master detailed setup when a new master record is
inserted and not yet posted and some editing has to be done in detail
database. Since there is no value for autoincrement field, the
relationship is broken.

Is there any way this value can be known (for the auto increment field)
before a post.

The other way is to force a post on the master record, but if the user
want to cancel all changes, this again calls for further action.

Any help will be appreciated.

The enviornment is : Delphi 5 Professional, BDE 5

Thanks
--
Natwar Lath
Lath Consultants
ROURKELA 769012 (INDIA)
~~~ Offshore Software Development ~~~
http://www.kalinga.com/lath

 

Re:Auto Increment Fields and Master Details Relationship


I had the same problem. I am using Sybase SQL as back end.
The way I solved it was by creating a new table that holds sequence numbers
for each table requiring autonumbering.
In the database I created stored procedure that looks like this:

create function
"DBA".fn_GetSeq(in @TableName char(15))
returns integer
begin
  declare @Seq integer;
  select Seq into @Seq from tblSeq where TableName=@TableName;
  update tblSeq set Seq=@Seq+1 where TableName=@TableName;
  return(@Seq)
end

in the program I created a function:

function fnGetNextSeq(stableName: string): integer;

function fnGetNextSeq(stableName: string): integer;
begin
     with frmData.proc_GetSeq do begin
          ParamByName('@TableName').AsString := stableName;
          Prepare;
          ExecProc;
          Result := Params[0].AsInteger;
          Close;
          end;
end;

and I call it just before I need the next number, for example:

        iSeq := fnGetNextSeq('tblTransaction');

        sSQL := 'INSERT INTO tblTransaction (';
        sSQL := sSQL + 'Seq,';
        sSQL := sSQL + 'Transaction_Client_Seq,';
        ......
        sSQL := sSQL + 'Transaction_User_ID)';
        sSQL := sSQL + ' VALUES (';
        sSQL := sSQL + IntToStr(iSeq) + ',';
        sSQL := sSQL + IntToStr(iClient_Seq) + ',';
        .....
        sSQL := sSQL + QUOTE + gsUserCode + QUOTE + ')';

        fnRunQuery(sSQL)

Then I know what the sequence number is and I can use it for all my links.

Ryszard

Quote
Natwar Lath <l...@kalinga.com> wrote in message

news:MPG.12e2fb09216ffc2798968a@news.supernews.com...
Quote
> Hello

> In one of my application I have used autoincrement fields. In a master
> detailed setup, I have observed that autoincrement field get filled up
> only on a post and not on the creation of record itself. This creates
> abnoramlity in a master detailed setup when a new master record is
> inserted and not yet posted and some editing has to be done in detail
> database. Since there is no value for autoincrement field, the
> relationship is broken.

> Is there any way this value can be known (for the auto increment field)
> before a post.

> The other way is to force a post on the master record, but if the user
> want to cancel all changes, this again calls for further action.

> Any help will be appreciated.

> The enviornment is : Delphi 5 Professional, BDE 5

> Thanks
> --
> Natwar Lath
> Lath Consultants
> ROURKELA 769012 (INDIA)
> ~~~ Offshore Software Development ~~~
> http://www.kalinga.com/lath

Re:Auto Increment Fields and Master Details Relationship


I have also had similar problems. What I do to get around the problem is to
create a temporary working file that is the same structure of the detail table.
That way the user can add or change records. When the user saves the master
record, simply copy the working table into the detail table after obtaining the
next unique number. This also helps if the user does not want to save the
information as all you need to do is call EmptyTable on the working table.

Regards

Carl

Other Threads