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
|
|
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. |
|
|
|
Tom |
|
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. |
|
|
|
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? |
|
|
|
Tom |
|
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" |
|
|
|
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? |
|
|
|
|