Board index » delphi » Master/Detail exception with AutoInc Field

Clément Dos

Delphi Developer
Tue, 17 Jun 2003 04:26:53 GMT

Master/Detail exception with AutoInc Field

Hi,

I am using ADO to access a SQL server 7 database.
The master has a Counter field as primary key (CONT_ID).
The detail has a compound primary key : (CONT_ID + IDAD_ID)
The user must give me the value for IDAD_ID.

I made all the links between two TADODatasets.
The detail table is edited/display using a grid.
While testing the program under th Delphi IDE I get an exception.
If I try to insert a record "An Unknown error has occured"
If I moves through the columns, same error.
But when I post, the record is commited and fine. And
the master/detail relation is honored.

Outside the Delphi IDE (or turning off the exceptions) everything seems fine.

I dont know if I can deliver an application with such behaviour...
Is there anything I can do to avoid this? What am I missing?

Best regards,
Clment

 
Qi Liwe

Delphi Developer
Tue, 17 Jun 2003 10:39:42 GMT

Re:Master/Detail exception with AutoInc Field


I encountered the same problem.

I think the problem is that after the master table is posted, the auto inc
field value is not got by delphi/or ADO, in my case, the local copy of auto
inc field is always 0. And the local copy of the master record is different
with real record in db. Then, when a detail record should post, if you just
has a record with 0 CONT_ID, every thing will be OK except the detail record
is not in its right position, otherwise, it compliant that a record with
same foreign key should be exists.

I just close and reopen the master table and every thing will be right. I do
not know how to do it better.

Liwen.

Quote
Clment Doss <cd...@dhs.com.br> wrote in message

news:3A4BA20D.116D1897@dhs.com.br...
Quote
> Hi,

> I am using ADO to access a SQL server 7 database.
> The master has a Counter field as primary key (CONT_ID).
> The detail has a compound primary key : (CONT_ID + IDAD_ID)
> The user must give me the value for IDAD_ID.

> I made all the links between two TADODatasets.
> The detail table is edited/display using a grid.
> While testing the program under th Delphi IDE I get an exception.
> If I try to insert a record "An Unknown error has occured"
> If I moves through the columns, same error.
> But when I post, the record is commited and fine. And
> the master/detail relation is honored.

> Outside the Delphi IDE (or turning off the exceptions) everything seems
fine.

> I dont know if I can deliver an application with such behaviour...
> Is there anything I can do to avoid this? What am I missing?

> Best regards,
> Clment

Brian Bushay Team

Delphi Developer
Tue, 17 Jun 2003 12:26:15 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>I am using ADO to access a SQL server 7 database.
>The master has a Counter field as primary key (CONT_ID).
>The detail has a compound primary key : (CONT_ID + IDAD_ID)
>The user must give me the value for IDAD_ID.

Have you set the Tfield AutoGenerate property?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com
Engine Xia

Delphi Developer
Tue, 17 Jun 2003 16:38:37 GMT

Re:Master/Detail exception with AutoInc Field


Maybe before you enter the detail grid, add the post method to the master
dataset will be OK.

The other reason maybe lie in the lookup field if you have in your detail
dataset.

Quote
"Qi Liwen" <qili...@21cn.com> wrote in message

news:92gsve$njf1@bornews.inprise.com...
Quote
> I encountered the same problem.

> I think the problem is that after the master table is posted, the auto inc
> field value is not got by delphi/or ADO, in my case, the local copy of
auto
> inc field is always 0. And the local copy of the master record is
different
> with real record in db. Then, when a detail record should post, if you
just
> has a record with 0 CONT_ID, every thing will be OK except the detail
record
> is not in its right position, otherwise, it compliant that a record with
> same foreign key should be exists.

> I just close and reopen the master table and every thing will be right. I
do
> not know how to do it better.

> Liwen.

> Clment Doss <cd...@dhs.com.br> wrote in message
> news:3A4BA20D.116D1897@dhs.com.br...
> > Hi,

> > I am using ADO to access a SQL server 7 database.
> > The master has a Counter field as primary key (CONT_ID).
> > The detail has a compound primary key : (CONT_ID + IDAD_ID)
> > The user must give me the value for IDAD_ID.

> > I made all the links between two TADODatasets.
> > The detail table is edited/display using a grid.
> > While testing the program under th Delphi IDE I get an exception.
> > If I try to insert a record "An Unknown error has occured"
> > If I moves through the columns, same error.
> > But when I post, the record is commited and fine. And
> > the master/detail relation is honored.

> > Outside the Delphi IDE (or turning off the exceptions) everything seems
> fine.

> > I dont know if I can deliver an application with such behaviour...
> > Is there anything I can do to avoid this? What am I missing?

> > Best regards,
> > Clment

Clément Dos

Delphi Developer
Tue, 17 Jun 2003 21:16:56 GMT

Re:Master/Detail exception with AutoInc Field


Quote
> >I am using ADO to access a SQL server 7 database.
> >The master has a Counter field as primary key (CONT_ID).
> >The detail has a compound primary key : (CONT_ID + IDAD_ID)
> >The user must give me the value for IDAD_ID.
> Have you set the Tfield AutoGenerate property?

Yes to AutoInc (Master table CONT_ID field).
Brian Bushay Team

Delphi Developer
Wed, 18 Jun 2003 08:51:43 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>Yes to AutoInc (Master table CONT_ID field).

Then are you sure the error is coming from the Autoinc field?
Are there any lookup fields in the table?  Lookup fields can cause the error you
described.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Clément Dos

Delphi Developer
Thu, 19 Jun 2003 06:35:26 GMT

Re:Master/Detail exception with AutoInc Field


Yes there is a Lookup field.
But all work fine if I break the Master/Detail with autoinc field.
I will try to build a master/detail without the autoinc and see if
the problems still persists.
I appreciate you help
Quote
Brian Bushay TeamB wrote:
> >Yes to AutoInc (Master table CONT_ID field).

> Then are you sure the error is coming from the Autoinc field?
> Are there any lookup fields in the table?  Lookup fields can cause the error you
> described.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Avy

Delphi Developer
Fri, 20 Jun 2003 03:55:49 GMT

Re:Master/Detail exception with AutoInc Field


I'm getting the same error with lookup fields when the key field value (being used
for lookup) is NULL.  I DO NOT have any auto increment field, and I'm using Oracle.
Any explanations/suggestions (BTW - the BDE handled
the same code just fine.  It had other sever drawbacks, however... :-(  )
Avy
Quote
Brian Bushay TeamB wrote:
> >Yes to AutoInc (Master table CONT_ID field).

> Then are you sure the error is coming from the Autoinc field?
> Are there any lookup fields in the table?  Lookup fields can cause the error you
> described.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Brian Bushay Team

Delphi Developer
Fri, 20 Jun 2003 08:31:42 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>I'm getting the same error with lookup fields when the key field value (being used
>for lookup) is NULL.  I DO NOT have any auto increment field, and I'm using Oracle.
>Any explanations/suggestions

Set the lookup field lookupCache to true.  
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com
Avy

Delphi Developer
Sat, 21 Jun 2003 02:50:22 GMT

Re:Master/Detail exception with AutoInc Field


Thanks, it did it!
The only question is, why?  The documentation mentions that lookupCashe should be set to
true for performance reasons when the lookupset includes relatively small number of
records.  It does not say, by any means, that this is required.  Or that you will get an
'unknown error' exception, and only in design time.  (and, in my case, there are some
cases when the lookup set is pretty big - several hundred records).  Ids there any
explanation for this 'black magic'?

Thanks again
Avy

Quote
Brian Bushay TeamB wrote:
> >I'm getting the same error with lookup fields when the key field value (being used
> >for lookup) is NULL.  I DO NOT have any auto increment field, and I'm using Oracle.
> >Any explanations/suggestions

> Set the lookup field lookupCache to true.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Brian Bushay Team

Delphi Developer
Sat, 21 Jun 2003 11:30:56 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>The only question is, why?  The documentation mentions that lookupCashe should be set to
>true for performance reasons when the lookupset includes relatively small number of
>records.  It does not say, by any means, that this is required.  Or that you will get an
>'unknown error' exception, and only in design time.  (and, in my case, there are some
>cases when the lookup set is pretty big - several hundred records).  Ids there any
>explanation for this 'black magic'?

I have not tried to figure out the source of this problem.  My opinion is that
if the lookup field values are  too large to cache you should not be using it.
Instead retrieve the value with your SQL command.  Lookup fields are Delphi
creations not native ADO when not cached they have a higher overhead then just
fetching the value in an SQL join

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Avy

Delphi Developer
Sun, 22 Jun 2003 06:01:17 GMT

Re:Master/Detail exception with AutoInc Field


In general I agree, but lookup fields have the added benefit that the SQL remains live, since
it does not do a join, so the fields can be updated in a DBGrid.  If you take the SQL join
approach you win in performance, but lose in program complexity.
Avy
Quote
Brian Bushay TeamB wrote:
> >The only question is, why?  The documentation mentions that lookupCashe should be set to
> >true for performance reasons when the lookupset includes relatively small number of
> >records.  It does not say, by any means, that this is required.  Or that you will get an
> >'unknown error' exception, and only in design time.  (and, in my case, there are some
> >cases when the lookup set is pretty big - several hundred records).  Ids there any
> >explanation for this 'black magic'?

> I have not tried to figure out the source of this problem.  My opinion is that
> if the lookup field values are  too large to cache you should not be using it.
> Instead retrieve the value with your SQL command.  Lookup fields are Delphi
> creations not native ADO when not cached they have a higher overhead then just
> fetching the value in an SQL join

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Brian Bushay Team

Delphi Developer
Sun, 22 Jun 2003 11:39:17 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>In general I agree, but lookup fields have the added benefit that the SQL remains live, since
>it does not do a join,

What database are you using?  ADO is designed for databases that return an
updateable Join.
MS SQL server and Access both give you updateable joins under ADO.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com
Avy

Delphi Developer
Mon, 23 Jun 2003 03:39:28 GMT

Re:Master/Detail exception with AutoInc Field


We are using Oracle
Avy
Quote
Brian Bushay TeamB wrote:
> >In general I agree, but lookup fields have the added benefit that the SQL remains live, since
> >it does not do a join,

> What database are you using?  ADO is designed for databases that return an
> updateable Join.
> MS SQL server and Access both give you updateable joins under ADO.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Brian Bushay Team

Delphi Developer
Mon, 23 Jun 2003 13:07:33 GMT

Re:Master/Detail exception with AutoInc Field


Quote
>We are using Oracle

Ok I can't help you with oracle specific problems
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com
Go to page: [1] [2]

Other Threads

1. link master autoInc field to Detail Integer field

2. Master detail autoinc fields with ADO Sql Server?

3. Master/detail, new record, autoinc field?

4. D2: Master-Detail problem using TQuery and Autoinc link field

5. D2: Master(TTable)/Detail(TQuery) link field type autoinc - HELP

6. AutoInc Field and Master/Detail relation

7. Master-Detail Query w/Cached Updates requeries Detail when updating single fields in Master

8. Master/Detail Master to 3 fields?

9. Master/Detail lookup - losing detail field

10. Master-detail + lookup field in detail