Board index » delphi » Basic master-detail question

Basic master-detail question

Hi,

Ever made a form containing two tables that have a master-detail
relationship?
Ok, than this one must be easy as cake for you...

An application has a form that is an invoice editor.
The editor form always displays just 1 invoice at a time.

The editor form is devided in two parts:
1) At the top half of the form the data aware fields of the master record
are placed.
   Here the user can enter things like invoice nr., customer nr., invoice
date etc.
2) At the bottom half of the form the lines of the invoice are displayed in
a DbGrid.

When a new invoice has to be made, I call the MasterTable.Insert method.
Then I show the editor form, so it displays the new empty master record and
no lines.

When a user decides to add a line, a database error occurs, saying that the
foreign key in the master table does not exist.
Of course this is because the master record has not been posted yet.

I don't want the user to be obliged to manualy update the master record
first, in order to be able to append lines. I think this would be very
cumbersome. (Isn't it?)
Besides, I think it is confusing when the user can't add lines until the
entire master record is filled in and posted. At least it will not be very
intuitive.

I've thought about an automatic post of the master record just before a new
line is inserted. But this is not an option since some fields in the master
record MUST have a valid value before the record can be posted. This is the
case for the CustNo field, which is a foreign key in the Customer table. It
is not possible to have a default here...

Maybe somebody has an elegant solution?
Any ideas are very welcome.

TIA

Marz

___nospam___...@xs4all.nl

 

Re:Basic master-detail question


Look at cached updates.

John

Re:Basic master-detail question


Marz,

I have found that the best (most reliable) way to achieve this is via a
stored procedure. Use unbound components on your form, and when the user is
ready to post, simply collect your data from the data entry components and
send your it to the stored procedure via TStoredProc parameters.

Although it takes a bit more coding than TQuery + cached updates, you have
absolute control over what is going on, and you can wrap the entire
serverside processing in a transaction, so you guarantee data integrity. It
may sound like a step backwards from the simple functionality of TQueries,
but when you have done it once you will find it is quite straight forward
and works like a charm. In my opinion this is the way most SQL database
processing should be done. I use TQueries for read only inquiries, but not
for transactions.

Regards,
Richard

Re:Basic master-detail question


Richard,

Sounds interesting. Could you post a small code example?

Quote
> I have found that the best (most reliable) way to achieve this is via a
> stored procedure. Use unbound components on your form, and when the user
is
> ready to post, simply collect your data from the data entry components and
> send your it to the stored procedure via TStoredProc parameters.

TIA
Ole Willy Tuv

Re:Basic master-detail question


Ole,

Below is an outline for a master detail stored procedure. I hope it makes
sense to you.

In Delphi:
1. Place a TStoredProc on your form
2. Point it to the database
3. Select the stored proc name (remove the ';1')
4. Open the Params and check the Parameter Type and Data Type are correct
5. Place unbound data entry components on your form with plenty of error
checking to prevent invalid values being passed to TStoredProc
6. Somewhere in your code (eg Button 'Post New Record') insert the values on
your form into the TStoredProc Params:

    TStoredProc.ParamByName(@A).AsString := TDBEdit1.Text;
    If not (TDBEdit2.Text = '') then
       TStoredProc.ParamByName(@B).AsSmallInt := StrToInt(TDBEdit2.Text);
   else TStoredProc.ParamByName(@B).AsSmallInt := 0;
   etc...
   TStoredProc.ExecProc;

I think that's about it.

Regards,
Richard

CREATE PROCEDURE NewMasterDetailRecord

     /* Fields in MasterTable */
    @A varchar(11),
    @B smallint,
    @C smalldatetime,
    @D char(2),

     /* Fields in DetailTable */
     @E int,
     @F int,
     @G int,
     @H int,
     @K int,
     @M int

AS

DECLARE
    @ThisMaster int

BEGIN TRANSACTION

INSERT INTO MasterTable(A, B, C, D)
    VALUES(@A, @B, @C, @D)

 SELECT @ThisMaster = @@Identity

 /* Test for some condition to decide if detail record to be entered - if
you need to*/
    if @B = 0
    begin

        /* Insert first detail record in DetailTable for @B = 0 */
        INSERT INTO DetailTable(MasterID, E, F)
            VALUES(@ThisMaster, @E , @F)

        /* Insert second detail record in DetailTable for @B = 0 */
        INSERT INTO DetailTable(MasterID, E, F)
            VALUES(@ThisMaster, @E , @G)

    end

    /* Test for some other condition to decide if detail record to be
entered */
    if @B = 1
    begin

        /* Insert first detail record in DetailTable for @B = 1 */
        INSERT INTO DetailTable(MasterID, H, K)
            VALUES(@ThisMaster, @H , @K)

        /* Insert second detail record in DetailTable for @B = 1 */
        INSERT INTO DetailTable(MasterID, H, M)
            VALUES(@ThisMaster, @H , @M)

    end

    /* etc, etc */
    /* also add error trapping to meet the needs of your code */

COMMIT TRANSACTION

Re:Basic master-detail question


I should have mentioned that both the master and detail tables should have
identity (autoincrement) fields for this to work (that's where @@Identity
gets its value in the Stored Procedure). I have an identity field as the
primary key in every table I use. Alternatively, you can assign your own
incrementing field but this is all too hard in my opinion.

Regards,
Richard

Quote
Richard Proudfoot wrote in message <8c61it$r...@bornews.borland.com>...
>Ole,

>Below is an outline for a master detail stored procedure. I hope it makes
>sense to you.

>In Delphi:
>1. Place a TStoredProc on your form
>2. Point it to the database
>3. Select the stored proc name (remove the ';1')
>4. Open the Params and check the Parameter Type and Data Type are correct
>5. Place unbound data entry components on your form with plenty of error
>checking to prevent invalid values being passed to TStoredProc
>6. Somewhere in your code (eg Button 'Post New Record') insert the values
on
>your form into the TStoredProc Params:

>    TStoredProc.ParamByName(@A).AsString := TDBEdit1.Text;
>    If not (TDBEdit2.Text = '') then
>       TStoredProc.ParamByName(@B).AsSmallInt := StrToInt(TDBEdit2.Text);
>   else TStoredProc.ParamByName(@B).AsSmallInt := 0;
>   etc...
>   TStoredProc.ExecProc;

>I think that's about it.

>Regards,
>Richard

>CREATE PROCEDURE NewMasterDetailRecord

>     /* Fields in MasterTable */
>    @A varchar(11),
>    @B smallint,
>    @C smalldatetime,
>    @D char(2),

>     /* Fields in DetailTable */
>     @E int,
>     @F int,
>     @G int,
>     @H int,
>     @K int,
>     @M int

>AS

>DECLARE
>    @ThisMaster int

>BEGIN TRANSACTION

>INSERT INTO MasterTable(A, B, C, D)
>    VALUES(@A, @B, @C, @D)

> SELECT @ThisMaster = @@Identity

> /* Test for some condition to decide if detail record to be entered - if
>you need to*/
>    if @B = 0
>    begin

>        /* Insert first detail record in DetailTable for @B = 0 */
>        INSERT INTO DetailTable(MasterID, E, F)
>            VALUES(@ThisMaster, @E , @F)

>        /* Insert second detail record in DetailTable for @B = 0 */
>        INSERT INTO DetailTable(MasterID, E, F)
>            VALUES(@ThisMaster, @E , @G)

>    end

>    /* Test for some other condition to decide if detail record to be
>entered */
>    if @B = 1
>    begin

>        /* Insert first detail record in DetailTable for @B = 1 */
>        INSERT INTO DetailTable(MasterID, H, K)
>            VALUES(@ThisMaster, @H , @K)

>        /* Insert second detail record in DetailTable for @B = 1 */
>        INSERT INTO DetailTable(MasterID, H, M)
>            VALUES(@ThisMaster, @H , @M)

>    end

>    /* etc, etc */
>    /* also add error trapping to meet the needs of your code */

>COMMIT TRANSACTION

Re:Basic master-detail question


Richard,

Tks for the example. I'll try it.

Regards
Ole

Re:Basic master-detail question


Quote
"Richard Proudfoot" <richa...@acay.com.au> wrote:
> Ole,
> Below is an outline for a master detail stored procedure. I hope it makes
> sense to you.

> In Delphi:
> 1. Place a TStoredProc on your form
> 2. Point it to the database
> 3. Select the stored proc name (remove the ';1')

Why do you have to do this? I use stored procedures and I don't do
this.

TIA.

Paul...

D4 Pro SP3 - NT 4, SP5

Database - MS SQL Server 7.0

Seo mo sini!

Re:Basic master-detail question


Paul,

Sorry, I didn't understand your question. What don't you do?

Regards,
Richard

Paul Linehan (Paul Linehan) wrote in message
<38e8df53.7963...@forums.inprise.com>...

Quote

>"Richard Proudfoot" <richa...@acay.com.au> wrote:

>> Ole,

>> Below is an outline for a master detail stored procedure. I hope it makes
>> sense to you.

>> In Delphi:
>> 1. Place a TStoredProc on your form
>> 2. Point it to the database
>> 3. Select the stored proc name (remove the ';1')

>Why do you have to do this? I use stored procedures and I don't do
>this.

>TIA.

>Paul...

>D4 Pro SP3 - NT 4, SP5

>Database - MS SQL Server 7.0

>Seo mo sini!

Re:Basic master-detail question


Paul,

Now I come back to this I think you are saying "Why delete the ';1' ".

The ';1' seems to be redundant, and depending how you access the SP in
MSSQLServer7 you can have problems accessing the parameters.  If you remove
it you have no problems so I make a practice of always removing it. There
are a number of posts on this subject.

Regards,
Richard

Paul Linehan (Paul Linehan) wrote in message
<38e8df53.7963...@forums.inprise.com>...

Quote

>"Richard Proudfoot" <richa...@acay.com.au> wrote:

>> Ole,

>> Below is an outline for a master detail stored procedure. I hope it makes
>> sense to you.

>> In Delphi:
>> 1. Place a TStoredProc on your form
>> 2. Point it to the database
>> 3. Select the stored proc name (remove the ';1')

>Why do you have to do this? I use stored procedures and I don't do
>this.

>TIA.

>Paul...

>D4 Pro SP3 - NT 4, SP5

>Database - MS SQL Server 7.0

>Seo mo sini!

Other Threads