Today I bumped in another problem.

Some QuickBooks tables cannot properly be updated with QODBC. In my case it was the SalesOrderLine table. The first error I got was:

Error parsing complete XML return string (8)

I just couldn't be sure what that meant...

I had looked at the XML output closely and noticed that the identifier of a line was improperly shown in a separate tag.

The fact is, I just could not update multiple lines with FQSaveToCache flag set to 1.

My solution is rather simple... I now delete all the lines, then re-insert them. All the lines? Ah! No! I cannot delete all the lines because QuickBooks doesn't let me. So I delete all the lines except the first, then update the first and re-insert the others. Any problem with that? Yes. The BEGIN TRANSACTION only works on local Access tables. In other words, I may be destroying a table in the QuickBooks database without having even a remote possibility for protection here... (well... other than saving that data in an Access table first, of course.)

So, the steps are:

  DELETE FROM SalesOrderLine WHERE TxnID = '...' AND SalesOrderLineSeqNo > 1

  UPDATE SalesOrderLine SET ... WHERE TxnID = '...' AND SalesOrderLineSeqNo = 1

  INSERT INTO SalesOrderLine (...) VALUES (...) [repeat as required, 0 to n times]

  INSERT INTO SalesOrder (...) VALUES (...)

The last INSERT INTO is the trigger here.

Trying to do anything more than 1 UPDATE will mess up the statements in some ways that is hard to determine.

The problem is, at this time, shown as being specific to SalesOrderLine, it can most certainly happen to you with other similar tables.