Error Message using ADO

[Cap2010]

Using ADO connection,below error occurs and
The same commands works fine when executed from Access using
the query mode not ADO.

Question is it that total no of fields in the table of
select statement and total no of fields in insert
table be the same and also the fields name too.

if not what is the reason for error


ERROR MESSAGE
Number of query values and destination fields are not the same


The ADO Command is
cn.Execute ("insert into dchallan SELECT challan.challan_no, challan.challan_date, challan.party_id, challan.site, challan.truck_no, challan.material, challan.measurement, challan.driver , challan.entered_by , challan.time , challan.purch_ord_no , challan.Billed From challan, Sites WHERE (((challan.site)=[sites].[sid]) AND ((challan.Billed)='F') AND ((sites.Category)='A'))&quot

 

[Antzz]

ALad,
The total number of fields in the source and destination table need not be the same. If the destination table has more fields than the source table, then you in the select statement, you need to specify as nulls the fields you are missing out. If the source table has more fields and you are using these fields in the select statement, then obviously the database server will expect the fields to be present in the destination table too. In that case you need to have those fields created in the destination table

 

[Cap2010]

hi,
what I did was this

took source table names
and while selecting gave the field name aliases i.e

select date as dDate from SourceTable

secondly what I found was that destination table shouldn't have excess fields as against source table.

Thanks

 

[Antzz]

Alad,
The destination table MAY have more fields than the source table. It should not be a problem until you specify that the access fields are nulls.

 

[listener22]

How is ADO to interpret [sites].[sid]? That's access syntax, isn't it? Jim

oracle, vb

 

[Antzz]

Sorry I mean excess fields and not access..

 

[listener22]

I was trying to refer to the fact that in your first post,
it says that this query runs if it is entered directly into
access. And my question is, isn't the bracket syntax
in [sites].[sid], which you are trying to use in ADO,
actually access syntax, that is that in access this set
of bracketed expressions means something. It probably
doesn't mean the same thing in ADO. What you want
is to find out what [sites].[sid] evaluates to in access,
and get the corresponding info into the ADO query
in its place. Jim

oracle, vb

 

[Guest_imported]

HI,

I have removed [] from sites.sid. The above e.g was
the test made in access to check. After rectifying and testing in Access, transferred code to VB in ADO form, removing the brackets to work for ADO.

Thanks,

Lad