Hello, I am using MS Access connected to Quickbooks via QODBC (just upgraded to v6.00.00.176).  I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is kept outside of QuickBooks, to make sure QB has the latest inventory data. All our products are Inventory Assemblies.

I have successfully inserted ItemInventoryAssemblyLine entries (thanks to help I found on this Forum - thanks, Tom!!), but now I'm trying to update the Quantity information on component parts for existing assemblies.

To do the update, I need to join to a local table that I've created from the external inventory data, which I've loaded with the ListID, ItemInventoryAssemblyLnItemInventoryRefListID, and FQPrimaryKey values from ItemInventoryAssemblyLine, in order to facilitate joins.

Here's my SQL (from MS Access Query Designer):

UPDATE ItemInventoryAssemblyLine INNER JOIN Assemblies_with_PK ON ItemInventoryAssemblyLine.FQPrimaryKey = Assemblies_with_PK.FQPrimaryKey SET ItemInventoryAssemblyLine.ItemInventoryAssemblyLnQuantity = [Qty];

"Qty" is a column in the local table that has the updated Quantity that I'd like to update into the Quickbooks table.

When I run this query, I get the dreaded "Error parsing complete XML return string." error.  I have tried alternatively joining on the combination of ListID and ItemInventoryAssemblyLnItemInventoryRefListID instead of FQPrimaryKey with the same result.

I have been able to update Custom Fields in the parent ItemInventoryAssembly table using the exact same approach with no problems.

Any help with this would be greatly appreciated!

Sorry this is a bug. The normal approach is to identify the ItemInventoryAssemblyLine you want to update, for example:

SELECT ListID, ItemInventoryAssemblyLnSeqNO,
ItemInventoryAssemblyLnItemInventoryRefListID, ItemInventoryAssemblyLnQuantity
FROM ItemInventoryAssemblyLine
where ListID='430001-1071511103' and ItemInventoryAssemblyLnSeqNO = 2
and ItemInventoryAssemblyLnItemInventoryRefListID='170000-933272656'

and then you would update it by doing:

UPDATE ItemInventoryAssemblyLine SET ItemInventoryAssemblyLnQuantity = 2
where ListID='430001-1071511103' and ItemInventoryAssemblyLnSeqNO = 2
and ItemInventoryAssemblyLnItemInventoryRefListID='170000-933272656'

However, the following error is received:

2006-07-26 17:16:43 QODBC Ver:  6.00.00.182 *********************************************************************************************************************
IsAService: False
SQL Statement: UPDATE ItemInventoryAssemblyLine SET ItemInventoryAssemblyLnQuantity = 2 where ListID='430001-1071511103' and ItemInventoryAssemblyLnSeqNO = 2 and ItemInventoryAssemblyLnItemInventoryRefListID='170000-933272656'
Error parsing complete XML return string (8)
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="5.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeNone">
        <ItemInventoryAssemblyModRq requestID = "1">
            <ItemInventoryAssemblyMod>
                <ListID>430001-1071511103</ListID>
                <EditSequence>1071511901</EditSequence>
                <ItemInventoryAssemblyLine>
                    <Quantity>2.00000</Quantity>
                </ItemInventoryAssemblyLine>
            </ItemInventoryAssemblyMod>
        </ItemInventoryAssemblyModRq>
    </QBXMLMsgsRq>
</QBXML>

20060726.171643 I 2060 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3244).
20060726.171643 E 2060 XercesSAXErrorHandler Element 'Quantity' is not valid for content model: '(ItemInventoryRef,Quantity?)' -- error at line 7, column 160 in XML data.
20060726.171643 I 2060 MsgSetHandler Parser error, finishing.
20060726.171643 I 2060 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3244), ret = 1.
20060726.171643 E 3244 RequestProcessor Process Request error = 80042500, There was an error when parsing the provided XML file.

I have reported the error.

This has new been fixed in QODBC v6.00.00.186 (or later):

UPDATE ItemInventoryAssemblyLine SET ItemInventoryAssemblyLnQuantity = 2
where ListID='430001-1071511103' and ItemInventoryAssemblyLnSeqNO = 2
and ItemInventoryAssemblyLnItemInventoryRefListID='170000-933272656'

See also: How do I create a Item Assembly (ItemInventoryAssembly)?