I am using QODBC version 6 and Quickbooks Pro 2006.  I am attempting to update multiple records at once using SQL statements through Microsft Query.

I run the following SQL statement to return the desired transaction set:

SELECT BillExpenseLine.ExpenseLineClassRefListID, BillExpenseLine.ExpenseLineClassRefFullName
FROM BillExpenseLine BillExpenseLine
WHERE (BillExpenseLine.ExpenseLineAccountRefFullName Like '%COGS:BFIS Statements:%')

It works.  I receive over 7000 records.

However, when I run the following SQL statement I receive an error.  The error is as follows:
ERROR 3200 - The Provided Edit Sequence "1155331557" is out-of-date

UPDATE BillExpenseLine SET ExpenseLineClassRefFullName='Statements' WHERE BillExpenseLine.ExpenseLineAccountRefFullName Like '%COGS:BFIS Statements:%'

I cannot determine the cause or solution.

Thank you for your help.
Carl

When doing updates to tranasaction tables a WHERE clause something like:

WHERE TxnID = '2A97-1071506491' AND ExpenseLineTxnLineID = '2A99-1071506491'

is required to correctly identifly the transaction. The TxnID allows for indexed jumpins (speed) and the TxnLineID allows you to update one line at a time in a loop getting the new EditSequence number each time.

Internally QODBC updates one line at a time but the edit sequence is listed in the header and gets updated with each update.

Can you please provide a specific example that would allow me to update the class for each bill already entered into QuickBooks?  Am I using the correct table in my attempt to populate the field ExpenseLineClassRefFullName?

Thanks

What I'm saying is that you can't update this as a batch update at the moment, ie:

UPDATE BillExpenseLine SET ExpenseLineClassRefListID = '40000-933272658'
WHERE ExpenseLineClassRefListID IS NULL

will get the "The provided edit sequence 1197761292 is out-of-date" error you see:

20060814.192214 I 1200 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3128).
20060814.192215 I 1200 SpecVersion Current version of qbXML in use: 5.0
20060814.192215 I 1200 QBSDKMsgSetHandler MODIFY: Bill
20060814.192215 E 1200 BillStorage::DoModify Invalid edit sequence: 1197761292
20060814.192215 I 1200 QBSDKMsgSetHandler Request 1 failed.
20060814.192215 I 1200 MsgSetHandler Finished.
20060814.192215 I 1200 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3128), ret = 0.

But if I get the TxnID and ExpenseLineTxnLineID for each of the records that I need to update first:

Select * from BillExpenseLine WHERE TxnID = '5BD4-1197757777'
AND ExpenseLineTxnLineID = '5BD6-1197757777'

Query Result:
1 5BD4-1197757777 2007-12-15 15:29:37.000 2007-12-15 15:29:37.000 1197757777 1264 640000-1071525538 Daigle Lighting C0000-933270541 Accounts Payable 2007-12-15 All 2007-12-30 100.00 900 20000-933272658 Net 15   0 1 5BD6-1197757777 580000-1071498064 Automobile:Insurance 100.00 Insurance Excess      0 5BD4-1197757777|5BD6-1197757777

I can update each record in a programming loop:

UPDATE BillExpenseLine SET ExpenseLineClassRefListID = '40000-933272658'
WHERE TxnID = '5BD4-1197757777' AND ExpenseLineTxnLineID = '5BD6-1197757777'

Query Result after update:
1 5BD4-1197757777 2007-12-15 15:29:37.000 2007-12-15 19:34:13.000 1197772453 1264 640000-1071525538 Daigle Lighting C0000-933270541 Accounts Payable 2007-12-15 All 2007-12-30 100.00 900 20000-933272658 Net 15   0 1 5BD6-1197757777 580000-1071498064 Automobile:Insurance 100.00 Insurance Excess   40000-933272658 Remodel  0 5BD4-1197757777|5BD6-1197757777

Note: You just need to update the ExpenseLineClassRefListID.

Thank you.

I applied your logic and used Microsoft Visual FoxPro because that is the language I am familiar with.  I created a "view" using the QODBC 6.0 driver.  I used the command CURSORSETPROP("SendUpdates",.t.) within my program to allow me to write back to the Quickbooks database.

I then processed the records one at a time updating ExpenseLineClassRefListID based on certain criteria in the other fields on the record being processed.

Your solution was very helpful.  Thank you for your prompt responses.