Hi
I am using d5 and SQL Server 7.

My master record is displayed in a TDBEdit and the detail records are
displayed in aTDBGrid.

The master table has a primary key 'MasterID' it is a identity field with a
seed of 1.
The detail table has a primary key 'DetailID' it is a identity field with a
seed of 1, and a additional index on a field called 'MasterID'.

I get the desired results when scrolling through existing master records,
but when a new master record is inserted
then all of the detail records are displayed.

This undesired behavior is eliminated once the master record is posted.

When using Paradox tables it is not necessary to post the master record in
order to get the desired results.

Is there any way to get the same behavior with SQL Server tables without
first posting the new inserted record?

Thanks in advance
Bob

Obviously the reason for all of the records being displayed is that there
is no master to "limit" the result set.  I don't like to use any prebuilt master / detail
controls since I "lose" control.  I have two queries one master, one detail and on the
onafterscroll event of the master I refresh my details.  Works wonderful and I can control
when & what gets displayed.  Not to mention I can then also control my own sort order.

Just a suggestion,

Craig Baugh

Hi Bob!

On Wed, 26 Jan 2000 08:47:45 -0800, "Bob Day"

Consider another aproach ...

In my detail tables I usualy have fields MasterID int, and DetailID
int identity and clustered primary key on both fields MasterID and
DetailID. Thats because:

1. In that way I only need one index (primary key) and don't need an
additional index on MasterID.

2. Records are in natural order by MasterID so detail records from one
master record are grouped together what is not the case in your
approach. This speeds up detail data feching.

3. Also identity field generation problem is avoided because primary
key is master + detail, so it wouldn't happen that I get two
DetailID's in one MasterID. But it can happen to get two DetailID's in
whole table - I can not state that for sure but it happend in MSSQL
6.5 without service packs and I do not rely on indentity any more.

I don't use identity fields for master records, but instead I always
use my AutoInc table for master key generation and on new record event
in delphi I put new master key value and detail is shown empty.

If on the other hand you allow your user to enter MasterID then I
suggest you to include another (for user hidden) field MasterNo and
make the primary key on that and use it for relation with detail.

Hope that helps.

tomi.

I just installed the ADO Patch from community.borland.com.  and my problem
was solved.

Thanks for your thoughts.
bob