|
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
|
|
| Invoice Insert Failing |
| Author |
Message |
| Ian |
| |
| Group | : Members |
| Posts | : 8 |
| Joined | : 2007-02-08 |
|
| Profile |
|
| Posted : 2007-02-09 13:21:05 |
I'm using the following code to insert a line into the invoices table of Quickbooks:
InvoiceRst = CreateObject("ADODB.Recordset")
InvoiceRst.CursorLocation = adUseClient
InvoiceRst.Open("SELECT * FROM InvoiceLine WHERE TxnId = 'X'", odbcConn, adOpenStatic, adLockOptimistic)
GetOrders.CursorLocation = adUseClient
query = "Select ONum,FName,LName from [Orders] Where OStatus <> 'Awaiting Confirmation' and OStatus <> 'Confirmed & Processing' and OStatus <> 'Mfg Casting' and Invoice = 0 UNION Select ONum,FName,LName from [Orders] Where OStatus <> 'Awaiting Confirmation' and Invoice = 0 and PayType = 91"
GetOrders.open(query, sqlConn, adOpenStatic, adLockOptimistic)
Do While Not GetOrders.EOF
query = "SELECT ListID,SalesTaxCodeRefListID FROM Customer WHERE IsActive = 1 and FirstName = '" & GetOrders("FName").Value & "'
and LastName = '" & GetOrders("LName").Value & "'"
CustomerRst.CursorLocation = adUseClient
CustomerRst.Open(query, odbcConn, adOpenStatic, adLockOptimistic)
If (Not CustomerRst.Eof) Then
custListID = CustomerRst.Fields("ListID").Value
salesTax = CustomerRst.Fields("SalesTaxCodeRefListID").Value
Else
End If
CustomerRst.Close()
title = "18K White Gold Pavé Setting for Emerald or Radiant Cut Diamond, Ring Size: 6.00"
itemName= "R2734-5"
query = "SELECT ListID,SalesTaxCodeRefListID FROM ItemInventory WHERE FullName = '" & itemName & "'"
rst.CursorLocation = adUseClient
rst.Open(query, odbcConn, adOpenStatic, adLockOptimistic)
If (Not rst.Eof) Then
itemListID = rst.Fields("ListID").value
Else
End If
rst.Close()
InvoiceRst.AddNew()
InvoiceRst.Fields("CustomerRefListID").Value = custListID
InvoiceRst.Fields("InvoiceLineItemRefListID").Value = itemListID
InvoiceRst.Fields("InvoiceLineDesc").Value = title
InvoiceRst.Fields("InvoiceLineAmount").Value = Price
InvoiceRst.Fields("InvoiceLineSalesTaxCodeRefListID").Value = salesTax
InvoiceRst.Fields("FQSaveToCache").Value = True
InvoiceRst.Update()
In the "InvoiceRst.AddNew()" line, I am getting the following error:
Empty row cannot be inserted. Row must have at least one column value set.
The QODBC messages log says the following:
IsAService: False
SQL Statement: INSERT INTO "QODBC"."InvoiceLine" ("CustomerRefListID","InvoiceLineItemRefListID","InvoiceLineDesc") VALUES (?,?,?)
3240 - Object "2E2D0000-1170373224" specified in the request cannot be found.
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="3.0"?>
<QBXML>
<QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
<InvoiceAddRq requestID = "1">
<InvoiceAdd defMacro = "TxnID:E2FB12B2-6415-416F-B77B-9ECE49358D75">
<CustomerRef>
<ListID>2E2D0000-1170373224</ListID>
</CustomerRef>
<InvoiceLineAdd>
<ItemRef>
<ListID>A0B0000-1163047822</ListID>
</ItemRef>
<Desc>18K White Gold Pavé Setting for Emerald or Radiant Cut Diamond, Ring Size: 6.00</Desc>
</InvoiceLineAdd>
</InvoiceAdd>
</InvoiceAddRq>
</QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
<QBXMLMsgsRs>
<InvoiceAddRs requestID="1" statusCode="3240" statusSeverity="Error" statusMessage="Object "2E2D0000-1170373224" specified in the request cannot be found. " />
</QBXMLMsgsRs>
</QBXML>
Any help with this would be appreciated. Also, if you need anymore info to help, just ask |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-09 13:39:43 |
| The error is saying the custListID "2E2D0000-1170373224" could not be found in the Customer table of the company file you are using but I also see that InvoiceLineAmount is missing in the QODBC Message log. Your code sample is incomplete. |
|
|
|
| Ian |
| |
| Group | : Members |
| Posts | : 8 |
| Joined | : 2007-02-08 |
|
| Profile |
|
| Posted : 2007-02-10 07:07:44 |
I had shortened the code sample for brevity's sake but here is the entire thing:
Dim InvoiceRst
Dim GetOrders
Dim CustomerRst
Dim ItemRst
Dim rst
Dim query
Dim custListID
Dim salesTax
Dim salesTax2
Dim Ta
Dim Price
Dim itemName
Dim itemTemp
Dim title
Dim finder
Dim itemListID
InvoiceRst = CreateObject("ADODB.Recordset")
GetOrders = CreateObject("ADODB.Recordset")
CustomerRst = CreateObject("ADODB.Recordset")
ItemRst = CreateObject("ADODB.Recordset")
rst = CreateObject("ADODB.Recordset")
InvoiceRst.CursorLocation = adUseClient
InvoiceRst.Open("SELECT * FROM InvoiceLine WHERE TxnId = 'X'", odbcConn, adOpenStatic, adLockOptimistic)
'Check order status and select the orders where invoice has not been generated
GetOrders.CursorLocation = adUseClient
query = "Select ONum,FName,LName from [Orders] Where OStatus <> 'Awaiting Confirmation' and OStatus <> 'Confirmed & Processing' and OStatus <> 'Mfg Casting' and Invoice = 0 UNION Select ONum,FName,LName from [Orders] Where OStatus <> 'Awaiting Confirmation' and Invoice = 0 and PayType = 91"
GetOrders.open(query, sqlConn, adOpenStatic, adLockOptimistic)
lblStatus.Text = "Generating Invoices for Customers"
Do While Not GetOrders.EOF
query = "SELECT ListID,SalesTaxCodeRefListID FROM Customer WHERE IsActive = 1 and FirstName = '" & GetOrders("FName").Value & "' and LastName = '" & GetOrders("LName").Value & "'"
CustomerRst.CursorLocation = adUseClient
CustomerRst.Open(query, odbcConn, adOpenStatic, adLockOptimistic)
If (Not CustomerRst.Eof) Then
custListID = CustomerRst.Fields("ListID").Value
salesTax = CustomerRst.Fields("SalesTaxCodeRefListID").Value
Else
End If
CustomerRst.Close()
ItemRst.CursorLocation = adUseClient
ItemRst.Open("Select * from [Active Items] where TableAcro <> 'H' and OID = " & GetOrders("ONum").Value, sqlConn, adOpenStatic, adLockOptimistic)
'For each item in Active Items corresponding to the order number add entry in the invoice
Do While Not ItemRst.EOF
Ta = ItemRst.Fields("TableAcro").Value
Price = ItemRst.Fields("Price").Value
title = ""
If Ta = "D" Or Ta = "A" Then
itemName = "Diamond"
title = ItemRst.Fields("DStat").Value & " GIA#:" & ItemRst.Fields("Title").Value
Else
itemTemp = GetRealID(Ta, ItemRst.Fields("INum").Value)
If Ta = "R" Or Ta = "N" Or Ta = "B" Or Ta = "E" Then
title = MetalDecode(CStr(ItemRst.Fields("Metal").Value)) & " "
End If
title = title & ItemRst.Fields("Title").Value()
If Ta = "R" Then
title = title & ", Ring Size: " & ItemRst.Fields("RSize").Value
finder = InStr(itemTemp, "RB")
If finder <> 0 Then
itemName = "WB"
Else
finder = InStr(itemTemp, "R")
If finder <> 0 Then
itemName = itemTemp
Else
itemName = "Misc"
End If
End If
ElseIf Ta = "E" Then
finder = InStr(itemTemp, "ES")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "E")
If finder <> 0 Then
itemName = itemTemp
Else
itemName = "Misc"
End If
End If
ElseIf Ta = "B" Then
finder = InStr(itemTemp, "TB")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "BC")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "B")
If finder <> 0 Then
itemName = itemTemp
Else
itemName = "Misc"
End If
End If
End If
ElseIf Ta = "N" Then
finder = InStr(itemTemp, "SB")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "SP")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "SG")
If finder <> 0 Then
itemName = "Misc"
Else
finder = InStr(itemTemp, "P")
If finder <> 0 Then
itemName = itemTemp
Else
finder = InStr(itemTemp, "N")
If finder <> 0 Then
itemName = itemTemp
Else
finder = InStr(itemTemp, "S")
If finder <> 0 Then
itemName = itemTemp
Else
itemName = "Misc"
End If
End If
End If
End If
End If
End If
ElseIf Ta = "P" Then
itemName = "Appraisal"
title = ItemRst.Fields("Title").Value
ElseIf Ta = "X" Then
itemName = "Expedite"
title = ItemRst.Fields("Title").Value
ElseIf Ta = "V" Then
itemName = "Service"
title = ItemRst.Fields("Title").Value
ElseIf Ta = "S" Then
itemName = "SO"
title = ItemRst.Fields("Title").Value
Else
itemName = "Misc"
title = ItemRst.Fields("Title").Value
End If
End If
If Ta = "P" Or Ta = "X" Or Ta = "V" Then
query = "SELECT ListID,SalesTaxCodeRefListID FROM ItemService WHERE FullName = '" & itemName & "'"
Else
query = "SELECT ListID,SalesTaxCodeRefListID FROM ItemInventory WHERE FullName = '" & itemName & "'"
End If
rst.CursorLocation = adUseClient
rst.Open(query, odbcConn, adOpenStatic, adLockOptimistic)
If (Not rst.Eof) Then
itemListID = rst.Fields("ListID").value
If Ta = "P" Or Ta = "X" Or Ta = "V" Then
salesTax2 = rst.Fields("SalesTaxCodeRefListID").Value
End If
Else
End If
rst.Close()
InvoiceRst.AddNew()
InvoiceRst.Fields("CustomerRefListID").Value = custListID
InvoiceRst.Fields("InvoiceLineItemRefListID").Value = itemListID
InvoiceRst.Fields("InvoiceLineDesc").Value = title
InvoiceRst.Fields("InvoiceLineAmount").Value = Price
If Ta = "P" Or Ta = "X" Or Ta = "V" Then
InvoiceRst.Fields("InvoiceLineSalesTaxCodeRefListID").Value = salesTax2
Else
InvoiceRst.Fields("InvoiceLineSalesTaxCodeRefListID").Value = salesTax
End If
InvoiceRst.Fields("FQSaveToCache").Value = True
InvoiceRst.Update()
ItemRst.MoveNext()
Loop
ItemRst.Close()
query = "SELECT * FROM [Active Items] Where TableAcro = 'H' and OID = " & GetOrders("ONum").Value
ItemRst.CursorLocation = adUseClient
ItemRst.Open(query, sqlConn, adOpenStatic, adLockOptimistic)
If (Not ItemRst.Eof) Then
title = ItemRst("Title").Value
Price = ItemRst("Price").Value
Else
title = "Shipping, Handling and Insurance"
Price = "0"
End If
ItemRst.close()
query = "SELECT ListID,SalesTaxCodeRefListID FROM ItemOtherCharge WHERE FullName = 'S&H'"
rst.CursorLocation = adUseClient
rst.Open(query, odbcConn, adOpenStatic, adLockOptimistic)
If (Not rst.Eof) Then
itemListID = rst.Fields("ListID").Value
salesTax = rst.Fields("SalesTaxCodeRefListID").Value
Else
End If
rst.Close()
Try
InvoiceRst.AddNew()
InvoiceRst.Fields("CustomerRefListID").Value = custListID
InvoiceRst.Fields("InvoiceLineItemRefListID").Value = itemListID
InvoiceRst.Fields("InvoiceLineDesc").Value = title
InvoiceRst.Fields("InvoiceLineAmount").Value = Price
InvoiceRst.Fields("InvoiceLineSalesTaxCodeRefListID").Value = salesTax
InvoiceRst.Fields("FQSaveToCache").Value = False
InvoiceRst.Update()
'Set the invoice flag for that order number
query = "Update [Orders] Set Invoice = 1 Where ONum = " & GetOrders("ONum").Value
sqlConn.Execute(query)
txtInvoice.Text = txtInvoice.Text & "Generated Invoice for " & GetOrders("FName").Value & " " & GetOrders("LName").Value & ", Order # " & GetOrders("ONum").Value & vbNewLine
Catch myException As Exception
txtInvoice.Text = txtInvoice.Text & "Error Generating Invoices for order #" & GetOrders("ONum").Value & myException.Message.ToString() & vbNewLine
End Try
GetOrders.MoveNext()
Loop
GetOrders.Close()
InvoiceRst.Close()
lblStatus.Text = "Done Generating Invoices"
txtInvoice.Text = txtInvoice.Text & "Done Generating Invoices"
You said that if was not able to find the custListID but I'm not sure what the solution would be for that. I wasn't the original author of the program, I'm just trying to debug it but have limited experience with QODBC. Again, if there is any more info you need, just ask. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-02-10 09:51:28 |
| Sorry, this is a FREE QODBC support forum, to debug someone else's code is chargeable at A$154/hour. Click here for more about this service. |
|
|
|
|