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. 2 8114
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Gavin |
last post by:
Hi, I'm a newbie to programming of any kind. I have posted this to other
groups in a hope to get a response from anyone.
Can any one tell me how to make my VB program read the Bios serial number...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: Martin Lacoste |
last post by:
Ok, before I headbutt the computer... don't know why when I add
criteria in a query, I get an 'invalid procedure call'. I also don't
know why after searching the help in access, the various access...
|
by: cover |
last post by:
Is there a restriction to the number of fields you can work with in a
PHP to MySQL connection? I'd used as many as 15 quite a few times and
now I have a form with 34 fields and can't seem to get...
|
by: JDiamond |
last post by:
Hi,
I have a table called Hosts. The Hosts table contains the following
fields:
Each field represents a step in the project. The tech that completes
each step initials the respective...
|
by: Kosmos |
last post by:
I'm getting this error message: Number of query values and destination fields are not the same.
This is the code:
INSERT INTO tblDateDifference ( DateDifference )
SELECT tblContracts.EndDate...
|
by: cypriot |
last post by:
Hi. I am developing an application program in java. I use MsAccess for keeping data.
I had a problem with sql insert method.
public void AddPatient()
{
String...
|
by: liorjj |
last post by:
Hi All,
I'm trying to insert record to Access DB to a tbale that contains
its first field as AutoNumber field using the following command:
"INSERT INTO Threads VALUES (" + category + "')";
...
|
by: jaishu |
last post by:
Hi ,
I am using a form based on a query. The form is locked and the fields are not editable, but when i just tried typing something, the contents on the form didnt change, but when i closed and...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |