Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
 Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : SP_LASTINSERTIDSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC SQL Sample Scripts Forum

 New Topic 
 
 Post Reply 
[1]  
 SP_LASTINSERTID 
 Author   Message 
  mark 
  
 Group: Members 
 Posts: 9 
 Joined: 2006-05-08 
 Profile
 Posted : 2006-05-08 23:07:34
SP_LASTINSERTID Invoice returns "Invalid Cursor State" when used.

I need the last Invoice record to retrieve the RefNumber so I can generate a new one. Our company has a special format for RefNumber that must be followed.

-Mark
 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-05-09 08:22:19

You can actually insert the RefNumber in the first place, but for the purpose of the execise, I just ran the following test using QODBC v6.00.00.155 without breaking the connection without any problems :

INSERT INTO "InvoiceLine" ("InvoiceLineItemRefListID", "InvoiceLineDesc",
"InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache") VALUES ('250000-933272656', 'Building permit A', 3.00000,
3.00, '20000-999022286', 1)

INSERT INTO "Invoice" ("CustomerRefListID", "ARAccountRefListID",
"TxnDate", "RefNumber", "BillAddressAddr1", "BillAddressAddr2",
"BillAddressCity", "BillAddressState", "BillAddressPostalCode",
"BillAddressCountry", "IsPending", "TermsRefListID", "DueDate", "ShipDate",
"ItemSalesTaxRefListID", "Memo", "IsToBePrinted",
"CustomerSalesTaxCodeRefListID") VALUES ('470001-1071525403',
'40000-933270541', {d'2002-10-01'}, 'QODBC1A', 'Brad Lamb', '1921 Appleseed
Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '10000-933272658',
{d'2002-10-31'}, {d'2002-10-01'}, '2E0000-933272656', 'Memo Test', 0,
'10000-999022286')

SP_LASTINSERTID Invoice

Select RefNumber, BillAddressAddr1 from Invoice where TxnID='5BEE-1197757041'

 

  Top 
  mark 
  
 Group: Members 
 Posts: 9 
 Joined: 2006-05-08 
 Profile
 Posted : 2006-05-10 05:51:12
Thanks for your  response,  I'm glad to see the product  is supported well. 

Perhaps I didn't explain my problem well, I'm using QODBC in conjuction with users manually inputting invoices into Quickbooks. Our refernce numbers are very custom and we cannot rely on QB to auto gen them correctly. I was hoping I could use the Stored Proc to get me the last Invoice inputted into Quickbooks so I could lookup the RefNumber and base the new one from that, regardless if it was an Invoioce entered manually or auto-gen'd.  However it appears the SP_LASTINSERTID only works if QODBC was the last to insert an Invoice. Below is the result I get when I connect to the qb db and run the stored proc (without running an INSERT statement before it).

I get the "Invalid Cursor State" below using the JdbcOdbc connector by Sun and have not run an INSERT statement prior.

Is there another work around for this problem I have?



 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-05-10 08:33:31
SP_LASTINSERTID is a QODBC stored procedure that retrieves the ListID or TxnID of the last QODBC insert and not the last new post in QuickBooks. If you leave the RefNumber out of the insert, QuickBooks will auto-increment the invoice number on inserts for you instead 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to