By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

'Number of query values and destination fields are not same' error

P: n/a
When i execute the following insert query, the above mentioned error
is coming. Anything wrong in the query?
INSERT INTO ECN_1 (Old_SbPartNo, Old_PartDesc, Old_ManPartNo,
Old_Manuf, Old_Vendor, Old_RefDesi, Old_Qty,
New_SbPartNo, New_PartDesc, New_ManPartNo,New_Manuf, New_Vendor,
New_RefDesi, New_Qty)
select * from (select SB0014.SbPartNo, SB0014.partDesc,
SB0014.mANpARTnO, SB0014.manufacturer,
SB0014.vendor, sb0014.refdesi, sb0014.qty,
SB0014_2.SbPartNo, SB0014_2.partDesc, SB0014_2.mANpARTnO,
SB0014_2.manufacturer, SB0014_2.vendor,
sb0014_2.refdesi, sb0014_2.qty
from SB0014 LEFT OUTER JOIN SB0014_2
on SB0014.SBPARTNO = SB0014_2.SBPARTNO
where sb0014_2.SbPartNo is null
or sb0014.partDesc <> sb0014_2.partdesc
or sb0014.manpartno <> sb0014_2.manpartno
or sb0014.manufacturer <> sb0014_2.manufacturer
or sb0014.vendor <> sb0014_2.vendor
or sb0014.refdesi <> sb0014_2.refdesi
or sb0014.qty <> sb0014_2.qty
union all
select null, null, null, null, null, null,null,
sb0014_2.SbPartNo, SB0014_2.partDesc, SB0014_2.mANpARTnO,
SB0014_2.manufacturer,
SB0014_2.vendor, sb0014_2.refdesi, sb0014_2.qty
from sb0014
right outer join sb0014_2 on sb0014.SbPartNo = sb0014_2.SbPartNo
where sb0014.SbPartNo is null) as u

When i execute it, it is giving me following error.

"java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]
Number of query v
alues and destination fields are not the same."

I checked the number of fields in table ECN_1 and query values which
seems to be same ie. 14. Pls let me know if there is anything wrong in
the above query.
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I think it would be easier for you to create your union query first and
then create your insert into query. My code for one that works for me
is below, you definately want your fields to match up otherwise you'll
get an error, they don't have to be the same name but they have to
match up column wise.

My Insert Into Code (this is inserting using my union query)

INSERT INTO test ( ParaLine, [Position], [Section], MOSAOC, ASI1, ASI2,
Grade, Auth, MaleFemale )
SELECT qryMTOE.ParaLine, qryMTOE.Position, qryMTOE.Section,
qryMTOE.MosAoc, qryMTOE.ASI1, qryMTOE.ASI2, qryMTOE.Grade,
qryMTOE.Auth, qryMTOE.MaleFemale
FROM qryMTOE;

My Union Query Code:

SELECT qryMTOEenlisted.ParaLine, qryMTOEenlisted.Position,
qryMTOEenlisted.Section, qryMTOEenlisted.MosAoc, qryMTOEenlisted.ASI1,
qryMTOEenlisted.ASI2, qryMTOEenlisted.Grade, qryMTOEenlisted.Auth,
qryMTOEenlisted.MaleFemale
FROM qryMTOEenlisted;
UNION

SELECT qryMTOEofficer.ParaLine, qryMTOEofficer.Position,
qryMTOEofficer.Section, qryMTOEofficer.MosAoc, qryMTOEofficer.ASI1,
qryMTOEofficer.ASI2, qryMTOEofficer.Grade, qryMTOEofficer.Auth,
qryMTOEofficer.MaleFemale
FROM qryMTOEofficer

UNION SELECT qryMTOEwarrant.ParaLine, qryMTOEwarrant.Position,
qryMTOEwarrant.Section, qryMTOEwarrant.MosAoc, qryMTOEwarrant.ASI1,
qryMTOEwarrant.ASI2, qryMTOEwarrant.Grade, qryMTOEwarrant.Auth,
qryMTOEwarrant.MaleFemale
FROM qryMTOEwarrant;

Notice how everything lines up with all my columns, make sure yours
does. I'm not an expert I just know how to get a lot of it to work.

Nov 13 '05 #2

P: n/a
Thank you for the answer. I eliminated 'select * ' after Insert
statement and now that error is gone. But facing java.sql.SQLException
: General error

Don't know how to fix. I don't know how to run this query in Ms Access
2000. Else i might have first checked there whether it's working or not
and then included in my JSP code.

My query looks like this.

INSERT INTO ECN_1 (Old_SbPartNo, Old_PartDesc, Old_ManPartNo,
Old_Manuf, Old_Vendor, Old_RefDesi, Old_Qty,
New_SbPartNo, New_PartDesc, New_ManPartNo,New_Manuf, New_Vendor,
New_RefDesi, New_Qty)

select SB0014.SbPartNo, SB0014.partDesc, SB0014.mANpARTnO,
SB0014.manufacturer, SB0014.vendor, sb0014.refdesi, sb0014.qty,
SB0014_2.SbPartNo, SB0014_2.partDesc, SB0014_2.mANpARTnO,
SB0014_2.manufacturer, SB0014_2.vendor, sb0014_2.refdesi, sb0014_2.qty
from SB0014 LEFT OUTER JOIN SB0014_2
on SB0014.SBPARTNO = SB0014_2.SBPARTNO
where sb0014_2.SbPartNo is null
or sb0014.partDesc <> sb0014_2.partdesc
or sb0014.manpartno <> sb0014_2.manpartno
or sb0014.manufacturer <> sb0014_2.manufacturer
or sb0014.vendor <> sb0014_2.vendor
or sb0014.refdesi <> sb0014_2.refdesi
or sb0014.qty <> sb0014_2.qty

union all

select null, null, null, null, null, null,null,
sb0014_2.SbPartNo, SB0014_2.partDesc, SB0014_2.mANpARTnO,
SB0014_2.manufacturer, SB0014_2.vendor, sb0014_2.refdesi, sb0014_2.qty

from sb0014
right outer join sb0014_2
on sb0014.SbPartNo = sb0014_2.SbPartNo
where sb0014.SbPartNo is null

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.