Using QODBC 8.00.00.242 against QB Enterprise Solutions: Manufacturing & Wholesale Edition 8.0

When inserting multiple lines against a single object (PurchaseOrderLines, ReceiptLines, SalesOrdersLines, InvoiceLines, etc), I have often seen on this forum the suggestion to insert the first line, retrieve the TxnID and then use that TxnID to insert the subsequent lines so that all the lines end up being associated with a single object. This is exactly what I need to do in all these cases, but it simply does not work. Is there a bug in this version of the driver?

Latest Example:

INSERT INTO PurchaseOrderLine (RefNumber, PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc, PurchaseOrderLineQuantity, PurchaseOrderLineRate) VALUES ('1111', 'P102', '', 1000, 15)

sp_lastinsertid returns '3B9-122728037'

INSERT INTO PurchaseOrderLine (TxnID, RefNumber, PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc, PurchaseOrderLineQuantity, PurchaseOrderLineRate) VALUES ('3B9-122728037','1111', 'P101', '', 1000, 89)

When these are run through VBDemo, I get error: Incorrectly built XML from Update Start.

When these are run through my VB.Net program I get an error telling me that TxnID is not an insertable field. And sure enough the QODBC table documentation also indicates that it is not insertable.

So how is anyone getting this to actually work?

While this is just one example, I have also tried this method to insert receipt lines using exactly the fields specified in other posts where it is supposed to work (albeit with earlier versions of the driver). And I need to be able to do this for purchase order lines, receipt lines, sales order lines and invoice lines

I added VendorRefListID and the result was duplicate purchase order headers with one line each.

If I also add the FQSaveToCache = 1 on the first insert and = 0 on the second insert, I get the same error about TxnID not being an insertable field!

Ok, you need to use the FQSaveToCache method or the TxnID append method for multiple line transactions, NOT BOTH.

See: Using ClassRefListID when inserting invoice lines as this shows how to use either method.

I only used the TxnID the first time (not using the FQSaveToCache flag) - I inserted one PO line, retrieved the txn ID and then inserted a second line for the same PO. The result was duplicate entries in the PO table with the same TxnID.

That's when I added the FQSaveToCache in a futile attempt to fix that problem. I understand that was wrong - you can't use both. But using just the TxnID method did not work either!

I can use the FQSaveToCache method to create a multi-line PO (or Sales Order or Invoice or ItemReceipt).  But there does not appear to be any way to add lines to an existing PO because inserting a new line with an existing TxnID still creates a duplicate PO.

Incorrect, but you do need to post the actual SQL Statements you are using and any errors or a query screen dump proving you got a new purchase order  instead of an additional PO line. At a rough guess you most likely overlooked the fact that you can't use a VendorRefListID in the SQL Statements that use TxnID to append additional lines. Based on what you posted earlier it would look something like along these lines:-

INSERT INTO PurchaseOrderLine (VendorRefListID,RefNumber, PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc, PurchaseOrderLineQuantity, PurchaseOrderLineRate) VALUES ('10000-933272655','1111', 'P102', '', 1000, 15)

sp_lastinsertid returns '3B9-122728037'

INSERT INTO PurchaseOrderLine (TxnID, PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc, PurchaseOrderLineQuantity, PurchaseOrderLineRate) VALUES ('3B9-122728037', 'P101', '', 1000, 89)