Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

jlori...@hotpop.com (Jessica Loriena)

Delphi Developer

Fri, 05 Nov 2004 14:19:05 GMT

Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

I have a simple master-detail tables structure with ADO: ORDER and
ITEMS.

I can browse through the data without a problem, but when I issue the
command "ORDER.Insert", I get the error message: "Invalid Variant Type
Conversion".

What am I doing wrong here??? The detail table (ITEMS) have the
following properties:

MasterSource: ORDER
MasterField: ID
IndexFieldName: ITEM_ID

I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
INT identity field (auto-number).

I read about Variant Type to NULL conversion problems, and maybe it's
got something to do with primary and foreign keys being null on
insert, but how can I make it work???

TIA for your urgent replies!!!

Jessica

Menno Avegaar

Delphi Developer

Tue, 09 Nov 2004 03:44:43 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

When you put the ORDER table into insert mode the detail table will try to
display the items for ID field in the master table. In a new record this ID
field is probably NULL. To avoid this you could set ID to an unused value
(e.g. -1) in the OnNewRecord event of the master table.

"Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
news:9beb2b43.0205192219.4e300eae@posting.google.com...

Quote

> I have a simple master-detail tables structure with ADO: ORDER and
> ITEMS.

> I can browse through the data without a problem, but when I issue the
> command "ORDER.Insert", I get the error message: "Invalid Variant Type
> Conversion".

> What am I doing wrong here??? The detail table (ITEMS) have the
> following properties:

> MasterSource: ORDER
> MasterField: ID
> IndexFieldName: ITEM_ID

> I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
> INT identity field (auto-number).

> I read about Variant Type to NULL conversion problems, and maybe it's
> got something to do with primary and foreign keys being null on
> insert, but how can I make it work???

> TIA for your urgent replies!!!

> Jessica

Jessica Lorie

Delphi Developer

Fri, 19 Nov 2004 13:48:09 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

In my case, the Master ID field is an "identity" (auto-number) field
which results in a "Field ID cannot be modified" error message.

This is really starting to annoy me. Is it so complicated to do a
simple ADO Master-Detail form in Delphi??? When trying to insert a
detail record, it results in a NULL Field error because the detail's
FOREIGN key is not set, which is because there is no MASTER ID key
since it is an auto-number field and the master table hasn't been
posted yet... and no, i don't want to force the posting of the master
table before the detail records are inserted - I want to issue a POST
command on both master and detail tables after the entire form is
filled out.

Can ANYONE point out a solution to this?? Is this impossible to do
with Delphi???

Jessica

Quote

"Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message <news:acjd0n$1a2a$1@scavenger.euro.net>...
> When you put the ORDER table into insert mode the detail table will try to
> display the items for ID field in the master table. In a new record this ID
> field is probably NULL. To avoid this you could set ID to an unused value
> (e.g. -1) in the OnNewRecord event of the master table.

> "Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
> news:9beb2b43.0205192219.4e300eae@posting.google.com...
> > I have a simple master-detail tables structure with ADO: ORDER and
> > ITEMS.

> > I can browse through the data without a problem, but when I issue the
> > command "ORDER.Insert", I get the error message: "Invalid Variant Type
> > Conversion".

> > What am I doing wrong here??? The detail table (ITEMS) have the
> > following properties:

> > MasterSource: ORDER
> > MasterField: ID
> > IndexFieldName: ITEM_ID

> > I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
> > INT identity field (auto-number).

> > I read about Variant Type to NULL conversion problems, and maybe it's
> > got something to do with primary and foreign keys being null on
> > insert, but how can I make it work???

> > TIA for your urgent replies!!!

> > Jessica

Menno Avegaar

Delphi Developer

Sat, 20 Nov 2004 04:06:02 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

In that case you'll have to use local caching of the detail table (with the
TClientDataset component).
Instead of displaying the detail table you copy the data to the
TClientDataset. Any modifications to the TClientDataset can be stored AFTER
the master dataset is posted.

"Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
news:9beb2b43.0206022148.6728276f@posting.google.com...

Quote

> In my case, the Master ID field is an "identity" (auto-number) field
> which results in a "Field ID cannot be modified" error message.

> This is really starting to annoy me. Is it so complicated to do a
> simple ADO Master-Detail form in Delphi??? When trying to insert a
> detail record, it results in a NULL Field error because the detail's
> FOREIGN key is not set, which is because there is no MASTER ID key
> since it is an auto-number field and the master table hasn't been
> posted yet... and no, i don't want to force the posting of the master
> table before the detail records are inserted - I want to issue a POST
> command on both master and detail tables after the entire form is
> filled out.

> Can ANYONE point out a solution to this?? Is this impossible to do
> with Delphi???

> Jessica

> "Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message

<news:acjd0n$1a2a$1@scavenger.euro.net>...

- Show quoted text -

Quote

> > When you put the ORDER table into insert mode the detail table will try
to
> > display the items for ID field in the master table. In a new record this
ID
> > field is probably NULL. To avoid this you could set ID to an unused
value
> > (e.g. -1) in the OnNewRecord event of the master table.

> > "Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
> > news:9beb2b43.0205192219.4e300eae@posting.google.com...
> > > I have a simple master-detail tables structure with ADO: ORDER and
> > > ITEMS.

> > > I can browse through the data without a problem, but when I issue the
> > > command "ORDER.Insert", I get the error message: "Invalid Variant Type
> > > Conversion".

> > > What am I doing wrong here??? The detail table (ITEMS) have the
> > > following properties:

> > > MasterSource: ORDER
> > > MasterField: ID
> > > IndexFieldName: ITEM_ID

> > > I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
> > > INT identity field (auto-number).

> > > I read about Variant Type to NULL conversion problems, and maybe it's
> > > got something to do with primary and foreign keys being null on
> > > insert, but how can I make it work???

> > > TIA for your urgent replies!!!

> > > Jessica

Jessica Lorie

Delphi Developer

Mon, 22 Nov 2004 13:45:58 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

Quote

> In that case you'll have to use local caching of the detail table (with the
> TClientDataset component).

I am using ADO. I don't think TClientDataset works with ADO.

Jessica

Quote

"Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message <news:adgec3$180r$1@scavenger.euro.net>...
> In that case you'll have to use local caching of the detail table (with the
> TClientDataset component).
> Instead of displaying the detail table you copy the data to the
> TClientDataset. Any modifications to the TClientDataset can be stored AFTER
> the master dataset is posted.

Kai Inge Buset

Delphi Developer

Tue, 23 Nov 2004 04:32:59 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

Hello Jessica,

I deal a lot with master detail tables, though using dBase tables.
I never use the native Master-Detail setup, but always filter the detail
table on a common field content with the Master table.
To allow full control through the whole prosess from creating a new record
in both master and detail and then post it or just throw it away, I use
separate tables as cache which is connected with the forms dbcomponents.
After a post I copy the contents to the main master and detail tables which
is connected with the forms DBGrids for display. In this way you don't have
an open record in the main tables for as long as it takes the user to fill
it out. It's only open the milliseconds it takes to transfer data from your
separate table to the main table. IMHO more secure data handling. To blank
the fields I use MyCacheMasterTable.ClearFields ready to fill inn a new
records content. Accordingly for the MyCacheDetailTable I use
MyCacheDetailTable.EmptyTable after transferring the data.

I must say that I never have tried the ADO as I'm told it's a slower
connection than the native connections?

:)
Kai Inge

Jessica Loriena <jlori...@hotpop.com> skrev i
meldingsnyheter:9beb2b43.0206052145.37a7a...@posting.google.com...

Quote

> > In that case you'll have to use local caching of the detail table (with
the
> > TClientDataset component).

> I am using ADO. I don't think TClientDataset works with ADO.

> Jessica

> "Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message

<news:adgec3$180r$1@scavenger.euro.net>...

Quote

> > In that case you'll have to use local caching of the detail table (with
the
> > TClientDataset component).
> > Instead of displaying the detail table you copy the data to the
> > TClientDataset. Any modifications to the TClientDataset can be stored
AFTER
> > the master dataset is posted.

Andy

Delphi Developer

Wed, 08 Dec 2004 00:58:42 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

Jessica,
It really has nothing to do with Delphi, basic RDBMS theory prohibits what
you say you want to do, you MUST have a master table ID to use in the detail
record. You will have to create and post the master first in order to get
it's ID. If your DBMS supports transaction processing , I believe you could
do this in the transaction and roll it back if the operation is abandoned.
Or you could use memory tables for the insert edit and then copy the records
to your db...

Quote

"Jessica Loriena" <jlori...@hotpop.com> wrote in message

news:9beb2b43.0206022148.6728276f@posting.google.com...

Quote

> In my case, the Master ID field is an "identity" (auto-number) field
> which results in a "Field ID cannot be modified" error message.

> This is really starting to annoy me. Is it so complicated to do a
> simple ADO Master-Detail form in Delphi??? When trying to insert a
> detail record, it results in a NULL Field error because the detail's
> FOREIGN key is not set, which is because there is no MASTER ID key
> since it is an auto-number field and the master table hasn't been
> posted yet... and no, i don't want to force the posting of the master
> table before the detail records are inserted - I want to issue a POST
> command on both master and detail tables after the entire form is
> filled out.

> Can ANYONE point out a solution to this?? Is this impossible to do
> with Delphi???

> Jessica

> "Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message

<news:acjd0n$1a2a$1@scavenger.euro.net>...

- Show quoted text -

Quote

> > When you put the ORDER table into insert mode the detail table will try
to
> > display the items for ID field in the master table. In a new record this
ID
> > field is probably NULL. To avoid this you could set ID to an unused
value
> > (e.g. -1) in the OnNewRecord event of the master table.

> > "Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
> > news:9beb2b43.0205192219.4e300eae@posting.google.com...
> > > I have a simple master-detail tables structure with ADO: ORDER and
> > > ITEMS.

> > > I can browse through the data without a problem, but when I issue the
> > > command "ORDER.Insert", I get the error message: "Invalid Variant Type
> > > Conversion".

> > > What am I doing wrong here??? The detail table (ITEMS) have the
> > > following properties:

> > > MasterSource: ORDER
> > > MasterField: ID
> > > IndexFieldName: ITEM_ID

> > > I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
> > > INT identity field (auto-number).

> > > I read about Variant Type to NULL conversion problems, and maybe it's
> > > got something to do with primary and foreign keys being null on
> > > insert, but how can I make it work???

> > > TIA for your urgent replies!!!

> > > Jessica

Dan Brenna

Delphi Developer

Wed, 08 Dec 2004 02:04:04 GMT

Re:Still Looking!!! ADO Insert causes Invalid Variant Type Conversion error.

Here is an alternate way to approach this. The auto-number field is at
the root of the problem. Don't use it. Use a regular long integer
field and supply your own id numbers. That way you know the number at
the start and won't have to post the master record in order to find
out what the id will be.

1. Create a small table [IDno] to hold the current value of the master
ID number. Initialize it to whatever makes sense in your app. [e.g.
zero]

2. Add a function "GetNextID" which will open the IDno table in
exclusive mode. It will get the current value, add one, and post it
back. This new number is returned to the calling procedure to use as
the MasterID. Its a good idea to put the IDno.Open in a Try-Except
loop so that if another user has it open at the moment it's needed the
function will try again several times.

3. Now your code for the data entry form can build all of the update
statements to post when the operator is happy with the data.

4. If the operator cancels the whole operation after starting, you
could back out the id number, but it would be better to let it just
disappear.

HTH,
Dan

On 2 Jun 2002 22:48:09 -0700, jlori...@hotpop.com (Jessica Loriena)
wrote:

Quote

>In my case, the Master ID field is an "identity" (auto-number) field
>which results in a "Field ID cannot be modified" error message.

>This is really starting to annoy me. Is it so complicated to do a
>simple ADO Master-Detail form in Delphi??? When trying to insert a
>detail record, it results in a NULL Field error because the detail's
>FOREIGN key is not set, which is because there is no MASTER ID key
>since it is an auto-number field and the master table hasn't been
>posted yet... and no, i don't want to force the posting of the master
>table before the detail records are inserted - I want to issue a POST
>command on both master and detail tables after the entire form is
>filled out.

>Can ANYONE point out a solution to this?? Is this impossible to do
>with Delphi???

>Jessica

>"Menno Avegaart" <avega...@NOSPAMwanadoo.nl> wrote in message <news:acjd0n$1a2a$1@scavenger.euro.net>...
>> When you put the ORDER table into insert mode the detail table will try to
>> display the items for ID field in the master table. In a new record this ID
>> field is probably NULL. To avoid this you could set ID to an unused value
>> (e.g. -1) in the OnNewRecord event of the master table.

>> "Jessica Loriena" <jlori...@hotpop.com> schreef in bericht
>> news:9beb2b43.0205192219.4e300eae@posting.google.com...
>> > I have a simple master-detail tables structure with ADO: ORDER and
>> > ITEMS.

>> > I can browse through the data without a problem, but when I issue the
>> > command "ORDER.Insert", I get the error message: "Invalid Variant Type
>> > Conversion".

>> > What am I doing wrong here??? The detail table (ITEMS) have the
>> > following properties:

>> > MasterSource: ORDER
>> > MasterField: ID
>> > IndexFieldName: ITEM_ID

>> > I'm using SQL Server 2000 on Win2K and ID (master ORDER table) is an
>> > INT identity field (auto-number).

>> > I read about Variant Type to NULL conversion problems, and maybe it's
>> > got something to do with primary and foreign keys being null on
>> > insert, but how can I make it work???

>> > TIA for your urgent replies!!!

>> > Jessica

Other Threads

1. ADO Insert causes Invalid Variant Type Conversion error

2. ADO Insert causes Invalid Variant Type Conversion error

3. Invalid variant type conversion - ADO and Delphi

4. Invalid variant type conversion error

5. Help: Invalid Variant Type Conversion Error

6. ADOTable open - error: Invalid variant type conversion.

7. Invalid Variant Type Conversion Error - This is where it stops

8. Invalid Variant Type conversion error

9. Invalid Variant Type Conversion error

10. Invalid variant type conversion