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_LastInsertID QuestionSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 SP_LastInsertID Question 
 Author   Message 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-02-09 12:04:44

I read everything i could find in the forum about this subject but i am still having a problem getting this to work after inserting my sales receipt lines.

I am using an Excel VBA script to insert all my sales receipt lines. This process works without a problem. After the last sales receipt line has been added i issue SQL command

sSQLLastTransaction = "select top 1 RefNumber from SalesReceiptLine order by TimeModified DESC"

to get the last sales receipt reference number. I used this work around as I could not get the SP_LastInsertId SalesReceiptLine to work. The problem here is that is takes about 10 seconds to get the reference number. If I could get the SP_LastInsertID to work I would have the transaction id number immediately and then could get the reference number i am looking for.

Like other posters when i execute the SQL the returned record set has EOF immediately. No errors whatsoever.

I execute the SP_LastInsertID right after the last insert to the salesreceiptline.

Here is that portion of the script:

    ' Populate credit card line item
    sItem = "Credit Card"
    dItemRate = wWs.Cells(2, 12).Value * -1
   
    ' Add credit card
    sSQLSRLine = "INSERT INTO SalesReceiptLine " + _
                              "(CustomerRefFullName, TemplateRefListID, TxnDate, " + _
                              "DepositToAccountRefListID, CustomerMsgRefListID, " + _
                              "SalesReceiptLineItemRefFullName, " + _
                              "SalesReceiptLineRate," + _
                              "FQSaveToCache) " + _
                      "VALUES ('" & sCustomerName & "', '1A0000-1169996991', " & sTxnDate & ", " + _
                              "'740000-1085588439', 'B0000-1142114504', " + _
                              "'" & sItem & "', " & dItemRate & ", 0)"
   
    'Now add sales receipt line
    rsSRLine.Open sSQLSRLine, CnQB, adOpenStatic, adLockOptimistic
   
    'Get sales receipt number
    sQBLastInsertID = "SP_LastInsertID SalesReceiptLine"
    rsInsert.Open sQBLastInsertID, CnQB, adOpenStatic, adLockOptimistic
       
    If Not (rsInsert.EOF) Then
             sRefNumber = rsInsert.Fields("LastInsertId").Value
    End If

I thought that my execution of the SP_LastInsertId is withing the last Open, but still, i never get any records.

Would you mind having a look at my code if there is something wrong?

Thanks for your help.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-02-09 13:46:10
No, the second open starts another RECORDSET connection thread that is no longer connected to the RECORDSET thread that did the insert. 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-02-09 16:07:43

Well, i changed my script to use the rsSRLine, the same dataset used for the last insert, but again, when executing the SQL the rsSRLine table gets an EOF immediately.   

 'Now add sales receipt line
    rsSRLine.Open sSQLSRLine, CnQB, adOpenStatic, adLockOptimistic
   
    'Get sales receipt number
    sQBLastInsertID = "SP_LastInsertID SalesReceiptLine"
    rsSRLine.Open sQBLastInsertID, CnQB, adOpenStatic, adLockOptimistic
       
    If Not (rsSRLine.EOF) Then
            sRefNumber = rsLine.Fields("LastInsertId").Value
    End If

In another threat about this subject i found some code referring to an openqry statement in the SQL string. would that be what i have to do? Or what  code would I need to run right after the last insert and how would i get the field LastInsertID back?

or is there any other way to execute my SQL without using the .Open?

 

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

rsSRLine.Open sQBLastInsertID, CnQB, adOpenStatic, adLockOptimistic

still opens another connection thread. Normally DoCmds is used.

DoCmd.RunSQL "INSERT INTO SalesReceiptLine " + _
                              "(CustomerRefFullName, TemplateRefListID, TxnDate, " + _
                              "DepositToAccountRefListID, CustomerMsgRefListID, " + _
                              "SalesReceiptLineItemRefFullName, " + _
                              "SalesReceiptLineRate," + _
                              "FQSaveToCache) " + _
                      "VALUES ('" & sCustomerName & "', '1A0000-1169996991', " & sTxnDate & ", " + _
                              "'740000-1085588439', 'B0000-1142114504', " + _
                              "'" & sItem & "', " & dItemRate & ", 0)"

DoCmd.RunSQL "SP_LastInsertID SalesReceiptLine"

 

  Top 
  Gerd 
  
 Group: Members 
 Posts: 28 
 Joined: 2008-12-09 
 Profile
 Posted : 2009-02-10 03:02:25

Unfortunately DoCmd is not available in VBA Excel.

Are there any equivalents in Excel?

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to