SQL:
INSERT INTO BillExpenseLine (ExpenseLineAccountRefListID, ExpenseLineAmount, ExpenseLineMemo, ExpenseLineCustomerRefListID, FQSaveToCache) VALUES ('760000-929644518', 1.00, 'Expense Line Memo Test 1', '', 1)

INSERT INTO Bill (VendorRefListID, APAccountRefListID, TxnDate, RefNumber, TermsRefListID, DueDate, Memo) VALUES ('FD40000-1143654631','210001-930860374',{d'2006-04-17'}, 'POnumber', '', {d'2006-04-17'}, 'Expense Memo Test')

Error on 2nd insert:

[QODBC] Error: 3000 - The given object ID "" in the field "list id" is invalid.   Is this referring to the bill row list id?  Isn’t that auto generated?  If not, how do I know what to use?

Also, how do you trap errors from QODBC.  I had to run the inserts with DBQwikEdit to see the error returns.  My code below does not catch the error: 

expense="Direct Expense:Bulk Material - Install"   'qbname
set rse = CreateObject("ADODB.Recordset")
sqle = "select ListID from account where FullName = '" & expense & "'"
response.write sqle & "<br>"
rse.open sqle, DbConQB, 1
response.write rse("ListID") & " ID for Bulk Material - Install<br>"

vendor="Shemin Landscape Supply Company"
set rsv = CreateObject("ADODB.Recordset")
sqlv = "select ListID from vendor where CompanyName =  '" & vendor & "'"
response.write sqlv & "<br>"
rsv.open sqlv, DbConQB, 1
response.write rsv("ListID") & " ID for Shemin<br>"

 apaccount="Accounts Payable"
set rsa = CreateObject("ADODB.Recordset")
sqla = "select ListID from account where FullName =  '" & apaccount & "'"
response.write sqla & "<br>"
rsa.open sqla, DbConQB, 1
response.write rsa("ListID") & " ID for Accounts Payable<br>"

 sqliline = "INSERT INTO BillExpenseLine " _
 & "(ExpenseLineAccountRefListID, " _
 & "ExpenseLineAmount, " _
 & "ExpenseLineMemo, " _
 & "ExpenseLineCustomerRefListID, " _
 & "FQSaveToCache) " _
 & "VALUES ('" & rse("ListID") & "', 1.00, 'Expense Line Memo Test 1', '', 1)"

 response.write sqliline & "<br>"
err.clear
DbConQB.execute sqliline
if err.number then
 response.write err.Description
 response.end
end if

 sqlibill = "INSERT INTO Bill " _
 & "(VendorRefListID, " _
 & "APAccountRefListID, " _
 & "TxnDate, " _
 & "RefNumber, " _
 & "TermsRefListID, " _
 & "DueDate, " _
 & "Memo) VALUES ('" _
 & rsv("ListID") & "','" & rsa("ListID") & "',{d'2006-04-17'}, 'POnumber', '', {d'2006-04-17'}, 'Expense Memo Test')"
response.write sqlibill & "<br>"

DbConQB.execute sqlibill
if err.number then
 response.write err.Description
 response.end
end if

You must specifiy a value for ExpenseLineCustomerRefListID in your first statement.

This is found by doing something like:

select ListID, Fullname from customer where FullName like 'V%'

See: Empty BillExpenseLine Transaction?? for another example of the same problem

Thanks Tom. 

I am entering these transactions from another system and we don't necessarily have the exact matching customer name that is in quickbooks.  Can I just put the customer name I have in the 'FullName' field even if I don't have the exact match or should I just put null in the RefListID field and put the customer name I have in the memo field?

Also, do you know how to trap errors coming back from QODBC in asp code?

Tom,

1. Is the customer column a quickboods requirement or a qodbc requirement?  I don't have the actual QB customer information available and there are existing records in the QB tables with nulls in both of the customer fields.  Is there any way to insert the data without the customer fields?

2. How do I trap errors in asp code?

No, you can actually leave out ExpenseLineCustomerRefListID all together, you just can't say you are going to use it, and then try to insert a null. 

The following example also works:

INSERT INTO "BillExpenseLine" ("ExpenseLineAccountRefListID", "ExpenseLineAmount", "ExpenseLineMemo", "FQSaveToCache") VALUES ('120000-933270541', 436.07, 'Expense Line Memo Test 100', 1)

INSERT INTO "Bill" ("VendorRefListID", "APAccountRefListID", "TxnDate", "RefNumber", "TermsRefListID", "DueDate", "Memo") VALUES ('720000-1071512482', 'C0000-933270541', {d'2002-10-01'}, '100', '20000-933272658', {d'2002-10-31'}, 'Expense Memo Test')

Error trapping is a function of your programming interface, not QODBC. When debugging OLE DB Provider-specific ADO problems, you can dump the contents of the Errors collection to the immediate window with the following code:

For Each objError in objConn.Errors
            Debug.Print objError.Description
Next objError

The question now is how to we "trap" QODBC Insert errors? Well, after any QODBC insert, we will want to put the following lines:

If Err.number <> 0 then
  TrapError Err.description
End If

You will want to put this after all ADO calls that communicate directly with QODBC. This includes ConnectionObject.Open, ConnectionObject.Execute, and RecordsetObject.Open. Now, you may be wondering where the sub TrapError is defined: we're about to do that. Create a file called ErrorHandler.asp and put it in your /include or /scripts directory. In ErrorHandler.asp, we will have the following subs:

TrapError
ProcessErrors

Let's look at the code for ErrorHandler.asp:

<%
Dim strErrorMessage
Dim bolErrors

'Initialize variables
strErrorMessage = "" 'The error messages for tech. support
bolErrors = False 'Have we found any errors yet?

'Now our two subs
sub TrapError(strError)
  bolErrors = True 'We've found an error!

  strErrorMessage = strErrorMessage & strError & ", "
end sub

'If there are any errors, this function will show the error
sub ProcessErrors()
  if bolErrors then
    'Show the Error

    Response.Write "There has been a QODBC error. " & _
                    "At " & Now & " the following errors occurred on " & _
      "the page " & Request.ServerVariables("SCRIPT_NAME") & _
      ": " & _
                    chr(10) & chr(10) & strErrorMessage
  end if
end sub 

%>

That's all there is to it! Now, at the top of all your pages that you want to use the error handling routines, you'll need to include ErrorHandler.asp like so:

<!--#include virtual="/include/ErrorHandler.asp"-->

My problem is that the second insert does not return to the script to allow me to test the error code.  I am testing with a 1 line bill.  The insert into BillExpenseLine works fine.  The insert to Bill hangs and never executes the next line of asp code after the DbConQB.Execute instruction.  If I insert completely valid data, the bill is created correctly and can be viewed in Quickbooks correctly.  If I change the AP Account List ID value to an invalid value, then the Bill insert hangs.  I put a response.end immediately after the DbConQB.Execute for the second insert to see if the insert or the error checking code was failing and the page never completed, thus the insert is hanging and not returning.  I open the connection with:

DbConQB.open("DSN=Quickbooks Data;OLE DB Services=-2")  as indicated in the documentation.  Is there any setting in IIS that can cause a bad insert to hang?  If I cause a deliberate error with a select, it returns as expected and the error values are set.  FYI, a failed select returns 0 in err.number, but the description and help context are set.

What can cause the insert to hang in the driver or how can I determine what the odbc driver is doing?  Is there any setting for a debug trace or log in the driver?

Tom, is the above error routine still valid for v9 and QB 2008 Prem?

I added the include as the first line in my sub but got already a compile error. Then I added the 2 subs of the include to my VBA. I also added the if err.number <> 0 right after executing the SQL but i got a system error instead of calling the error routines of your include.

I sure would like to get this working to catch all the SQL errors from QODBC.

Understood.

Thanks for your reply