|
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 customer" returns no values in VB6 program |
| Author |
Message |
|
|
| Posted : 2006-09-22 04:56:14 |
Even though it does work in VB Demo program, following code returns an empty recordset. How can I correct my code or do something else to retrieve the ListID please?
Public Function addCustomer(ByVal i_consortium_id As Integer, ByVal cCompany_name As String, _
cQBListID As String, cErrMsg As String) As Boolean
'Add a customer to a company book
'i_consortium_id = Consortium ID per IMMA
'cCompanyName = IMAA Company Name == QB Customer Name
'
Dim cSQL As String
Dim objQBRecordSet As New ADODB.Recordset
On Error GoTo ErrorHandler
addCustomer = True
cQBListID = "" 'Initialize it !
cCompany_name = Replace(cCompany_name, "'", "")
objQBDataCon.Open "DSN=IMAA_QB_Int_HomePlug;OLE DB Services=-2;"
cSQL = "INSERT INTO customer (Name) Values (" & "'" & cCompany_name & "'" & ")"
objQBDataCon.Execute (cSQL)
Set objQBRecordSet = New ADODB.Recordset
objQBRecordSet.Open "SP_LASTINSERTID Customer", objQBDataCon, adOpenStatic, adLockOptimistic
If Not objQBRecordSet.EOF Then 'EOF is True here
cQBListID = objQBRecordSet(0) ' First column is the ListID
End If
objQBRecordSet.Close
Exit Function
ErrorHandler:
addCustomer = False
cErrMsg = "Error occured in addCustomer(): Consortium ID = " & i_consortium_id & " Company Name = " & cCompany_name & " Desr: " & Err.Description
AppendLog (cErrMsg)
End Function |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-09-22 07:56:30 |
sp_lastinsertid only works when executed within the same and unbroken connection thread. objQBDataCon.Open and objQBRecordSet.Open open two different connection threads. |
|
|
|
|
|
| Posted : 2006-09-26 09:59:16 |
Thank you very much for the reply.
How can I achieve it using QODBC, VB 6 and ADODB please? If not, is there any other way? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-09-26 10:22:47 |
Well I would try finding the ListID for the new company by using the FullName which is a jump-in for the Customer table, ie:
select ListID from customer where FullName='Intuit'
or if you're the only person adding customers you can use:
select Top 1 ListID, Name from customer order by TimeModified Desc
and check that the Name matches the new entry.
|
|
|
|
| Paul |
| |
| Group | : Members |
| Posts | : 1 |
| Joined | : 2007-09-07 |
|
| Profile |
|
| Posted : 2007-09-07 01:13:45 |
Piyush Varma, Tom
To excute SP_LASTINSERTID in the same connection you can use the Command object in the same connection and get the ID in the second execute as follows:
Dim recset2 As ADODB.Recordset
Set recset2 = New ADODB.Recordset
Set oMultipleRecCMD = New Command
With oMultipleRecCMD
.ActiveConnection = oQBConn
.CommandText = "INSERT INTO Invoice (""Memo"") VALUES ('Entered Automatically - " & FormatDateTime(Now()) & "')"
.CommandType = adCmdText
.Execute
.CommandText = "SP_LASTINSERTID Invoice"
.CommandType = adCmdText
Set recset2 = .Execute
End With
Set oMultipleRecCMD = Nothing
Sure that this will be more than a little late for your implementation, but it is answer to this question that i was trying to find today.
|
|
|
|
|