I'm having a problem with SP_LASTINSERTID not returning any value after I insert and invoice.

Here is my insert statement

insert into invoiceline (InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount,
         InvoiceLineSalesTaxCodeRefListID, CustomerRefListID, ARAccountRefListID, TemplateRefListID, TxnDate,
         BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode,
         IsPending, TermsRefListID, DueDate, ShipDate, IsToBePrinted)
     values ('Monthly Subscription', Sales Lead Subscription', 1, 39.95, 39.95,
       '20000-1018058558', '4B700000-1147461510', '5C0000-1017781260', '60000-1019762012', {d'2006-05-11'},
       'Joe Mama', '123 Main St.', 'Suite 2', 'Chicago', 'IL', '60602', 0, '40000-1017764133', {d'2006-05-11'}, {d'2006-05-11'}, 0)  

After the insert, I try both SP_LASTINSERTID invoice and SP_LASTINSERTID invoiceline and neither is giving any results.  Any idea.

Thanks.

The command to run here is:

SP_LASTINSERTID invoiceline

but it has to be run before any other query is executed and without the connection being closed after your INSERT. 

Unfortunately, I did try that and I'm still having the same problem.  What I'm trying to do is insert a customer, an invoice, and a payment for that invoice.  When I insert the customer, SP_LASTINSERTID customer works fine, but when I try to run it for invoiceline, I get blank / null.  I have then attempted to get the invoice txnid manually, and supply it in the payment, but then I get an error,  Error 3120 : Object "11A0D7-1147794642" (the invoice ID) specified in the request cannot be found.  The following is the code I'm using.  Please tell me what I'm doing wrong.

insert into customer (name, firstname, lastname, companyName, contact, accountNumber, BillAddressAddr1, BillAddressAddr2,
      BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email,
      TermsRefListID, SalesTaxCodeRefListID, PreferredPaymentMethodRefFullName, CreditCardInfoCreditCardNumber,
      CreditCardInfoExpirationMonth, CreditCardInfoExpirationYear, CreditCardInfoNameOnCard,
      CreditCardInfoCreditCardAddress, CreditCardInfoCreditCardPostalCode, JobStatus,
      JobStartDate, JobEndDate, JobDesc)
   values ('Joe Mama Inc 2', 'Joe', 'Mama', 'Joe Mama Inc 2', 'Joe Mama', '343453', 'Joe Mama Inc', '123 Main St.',
      'Suite 456', 'Chicago', 'IL', '60602', '555-555-5555', '666-666-6666', 'joe@mama.com',
      '40000-1017764133', '20000-1018058558', 'Visa', '4111111111111111', 06, 2006, 'Joe Mama',
      '123 Main St. Suite 450', '60707', 'None', {d'2006-05-11'}, null, '$39.95 Monthly Subscription - Bill CC Monthly'
     )
     
SP_LASTINSERTID customer == 4BDE0000-1147794581

insert into invoiceline (InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount,
         InvoiceLineSalesTaxCodeRefListID, CustomerRefListID, ARAccountRefListID, TemplateRefListID, TxnDate,
         BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode,
         IsPending, TermsRefListID, DueDate, ShipDate, IsToBePrinted)
     values ('Monthly Subscription', 'Sales Lead Subscription', 1, 39.95, 39.95,
       '20000-1018058558', '4BDE0000-1147794581', '5C0000-1017781260', '60000-1019762012', {d'2002-10-01'},
       'Joe Mama Inc 2', '123 Main St.', 'Suite 450', 'Chicago', 'IL', '60602',
       0, '40000-1017764133', {d'2006-05-11'}, {d'2006-05-11'}, 0)  

SP_LASTINSERTID invoiceline == blank / null
select txnid from invoiceline where customerreflistid = '4BDE0000-1147794581' == 11A0D7-1147794642

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)    
     Values ('4BDE0000-1147794581', '650000-1019162593', 39.95, '11A0D7-1147794642', 39.95, {d'2007-12-15'}, 'Payment for Invoice #5')

I have run your sequence using data that works with QuickBooks 2006 USA Sample Rock Castle Construction company file and I can also confirm sp_lastinsertid invoiceline doesn't currently return a value. This is a bug in QODBC v6.00.00.155

insert into customer (name, firstname, lastname, companyName, contact, accountNumber, BillAddressAddr1, BillAddressAddr2,
      BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email,
      TermsRefListID, SalesTaxCodeRefListID, PreferredPaymentMethodRefFullName, CreditCardInfoCreditCardNumber,
      CreditCardInfoExpirationMonth, CreditCardInfoExpirationYear, CreditCardInfoNameOnCard,
      CreditCardInfoCreditCardAddress, CreditCardInfoCreditCardPostalCode, JobStatus,
      JobStartDate, JobEndDate, JobDesc)
   values ('Joe Mama Inc 2', 'Joe', 'Mama', 'Joe Mama Inc 2', 'Joe Mama', '343453', 'Joe Mama Inc', '123 Main St.',
      'Suite 456', 'Chicago', 'IL', '60602', '555-555-5555', '666-666-6666', 'joe@mama.com',
      '10000-933272658', '10000-999022286', 'Visa', '4111111111111111', 06, 2008, 'Joe Mama',
      '123 Main St. Suite 450', '60707', 'None', {d'2006-05-11'}, null, '$39.95 Monthly Subscription - Bill CC Monthly'
     )

SP_LASTINSERTID customer   ====>  AC0000-1197757899

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('AC0000-1197757899', 'QODBCcip', '250000-933272656', 'Bin Permit Renovations',
200.00000, 200.00, '20000-999022286', 0) 

SP_LASTINSERTID invoiceline ====>   blank / null, no value returned  <==== This is a bug

To workaround this, you need to query the TxnID of the new invoice:

select txnid, refnumber from invoiceline where customerreflistid = 'AC0000-1197757899'

Once the TxnID is determined, the payment was successfull:-

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount,
AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)  
Values ('AC0000-1197757899', '80000-933270541', 200.00, '5BF9-1197759389', 200.00,
{d'2007-12-15'}, 'Payment for Invoice #QODBCcip')

Thanks for the detailed reply, any idea when that bug might be fixed?  will there be any notification when it is fixed?

Also, I'm still having the same problem with the payment.  I run the following query to get the invoice txnid and refnumber.

select top 1 txnid, refnumber from invoiceline where customerreflistid = '4BDE0000-1147794581' and isPaid = 0 order by TimeCreated desc

which returns

11A0D7-1147794642, 928940010

then I run

INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)    
     Values ('4BDE0000-1147794581', '650000-1019162593', 39.95, '11A0D7-1147794642', 39.95, {d'2007-12-15'}, 'Payment for Invoice #928940010')

and I'm still getting the error

QODBC Error 3120 - Object ''11A0D7-1147794642' specified in the request cannot be found.

Might this have something to do with the optimizer?  Do I need to run the optimizer before the txnid will be recognized, I have the optimizer set to update at the start of every query.

Thanks.

Check that you are running QODBC v6.00.00.155 and that the DepositToAccountRefListID: '650000-1019162593' is for your Undeposited Funds account.

Yes, I did confirm both the version and the list id for the account.

Here is the chunk from the driver error log from when I tried to do the payment insert, maybe this will shed some light on the problem.

2006-05-17 10:42:54 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO ReceivePaymentLine (CustomerRefListID, DepositToAccountRefListID, TotalAmount, AppliedToTxnTxnID, AppliedToTxnPaymentAmount, TxnDate, Memo)    

     Values ('4BDE0000-1147794581', '650000-1019162593', 39.95, '11A0D7-1147794642', 39.95, {
d'2007-12-15'}, 'Payment for Invoice #928940010')
3120 - Object "11A0D7-1147794642" specified in the request cannot be found.
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="4.1"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <ReceivePaymentAddRq requestID = "1">
            <ReceivePaymentAdd defMacro = "TxnID:3E950C5D-EAD2-4A11-A51C-B98E4F1141EB">
                <CustomerRef>
                    <ListID>4BDE0000-1147794581</ListID>
                </CustomerRef>
                <TxnDate>2007-12-15</TxnDate>
                <TotalAmount>39.95</TotalAmount>
                <Memo>Payment for Invoice #928940010</Memo>
                <DepositToAccountRef>
                    <ListID>650000-1019162593</ListID>
                </DepositToAccountRef>
                <AppliedToTxnAdd>
                    <TxnID>11A0D7-1147794642</TxnID>
                    <PaymentAmount>39.95</PaymentAmount>
                </AppliedToTxnAdd>
            </ReceivePaymentAdd>
        </ReceivePaymentAddRq>
    </QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
    <QBXMLMsgsRs>
        <ReceivePaymentAddRs requestID="1" statusCode="3120" statusSeverity="Error" statusMessage="Object &quot;11A0D7-1147794642&quot; specified in the request cannot be found. " />
    </QBXMLMsgsRs>
</QBXML>

Thanks.

I am using QB Enterprise 5.0, so I don't have that sample company file. 

Is there anything else I can try to get this working.  I am intereted in buying the product assuming it will work for out needs.

I don't understand how that txnid is not being found when it is clearly there, and can be queried with the vb demo.

Any ideas?

Thanks.

Try going through another transaction.

If your billing operation is going to be create a customer, create the invoice and reveive the payment at the same time, then you should be using sales receipts instead.

Please also try receiving the payment for the invoice you created using the QuickBooks User Interface itself to see if there are any problems with your invoice or your setup.

Here is a work around to fix this bug. By the way it also occurs for Estimate and EstimateLines.

1. Do an insert into the InvoiceLine table with FQSaveToCache to true or 1.

2. Then do an insert into the Invoice table with some random header information, it can be same information as your invoiceLine table.

3. Then run sp_LastInsertID Invoice

Setting the cache to true and then inserting some header information fixes the bug.

When is the next version of QODBC comming out to fix all the bugs?