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
|
|
Error inserting multiple SalesReceiptLine records |
Author |
Message |
|
Posted : 2008-02-28 05:41:50 |
Hello,
I'm experiencing a problem inserting multiple SalesReceiptLine Records. When inserting the line records using the VB-DEMO32, the transaction is written to cache as I using the FQSaveToCache. However when I insert the header I get an error, which I cannot decipher what is meant by the message.
The statements are the ones that follow and at the end I show an image with the error I get.
insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineSalesTaxCodeRefListID, SalesReceiptLineClassRefListID, SalesReceiptLineQuantity, FQSaveToCache) select I.ListID as SalesReceiptLineItemRefListID, Description as SalesReceiptLineDesc, SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, C.ListID as SalesReceiptLineClassRefListID, 1955 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item I, Class C where I.FullName = '1-002 A' and C.FullName = 'RIO 1'
insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineSalesTaxCodeRefListID, SalesReceiptLineClassRefListID, SalesReceiptLineQuantity, FQSaveToCache) select I.ListID as SalesReceiptLineItemRefListID, Description as SalesReceiptLineDesc, SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, C.ListID as SalesReceiptLineClassRefListID, 1955 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item I, Class C where I.FullName = '1-003 B' and C.FullName = 'RIO 1'
insert into "SalesReceipt" (CustomerRefListID, ClassRefListID, TemplateRefListID,TxnDate, BillAddressAddr1, IsPending, DueDate, ShipDate, ItemSalesTaxRefListID, IsToBePrinted, CustomerSalesTaxCodeRefListID, DepositToAccountRefListID) select K.ListID as CustomerRefListID, C.ListID as ClassRefListID, T.ListID as TemplateRefListID, {d'2008-02-26'} as TxnDate, BillAddressAddr1, 0 as IsPending, {d'2008-02-26'} as DueDate, {d'2008-02-26'} as ShipDate, ItemSalesTaxRefListID, 0 as IsToBePrinted, SalesTaxCodeRefListID as CustomerSalesTaxCodeRefListID, A.ListID as DepositToAccountRefListID from Customer K, Account A, Class C, Template T where K.Name = 'cash' and A.ListId like '300000-%' and C.FullName = 'RIO 1' and T.Name like 'Custom Sales%'
I've spent quiet some time trying to sort out what this could be. In my original statement for both the header and the details, I was also including the descriptions of the ListID's and when looking at an example I saw that these were left out. I take that these are automatically populated by QODBC, as are other fields.
Regards,
Alecio |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-28 07:43:36 |
Sorry the error message is missing and your subqueries use multiple tables without a join? You need to run and debug your select statements first before using them as a subquery in a insert.
See: How do I create a Sales Receipt using QODBC? for more. |
|
|
|
|
Posted : 2008-02-28 12:17:41 |
I did debug the select statements before running and I get the fields that I should get. I changed the select statement to have the join as per your suggestion and ran the statements with se same result.
These are the modified statements:
insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineSalesTaxCodeRefListID, SalesReceiptLineClassRefListID, SalesReceiptLineQuantity, FQSaveToCache) select I.ListID as SalesReceiptLineItemRefListID, Description as SalesReceiptLineDesc, SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, C.ListID as SalesReceiptLineClassRefListID, 1955 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item I inner join Class C on I.FullName = '1-002 A' and C.FullName = 'RIO 1'
insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineItemRefFullName, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineSalesTaxCodeRefListID, SalesReceiptLineSalesTaxCodeRefFullName, SalesReceiptLineClassRefListID, SalesReceiptLineClassRefFullName, SalesReceiptLineQuantity, FQSaveToCache) select I.ListID as SalesReceiptLineItemRefListID, I.FullName as SalesReceiptLineItemRefFullName, Description as SalesReceiptLineDesc, SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, SalesTaxCodeRefFullName as SalesReceiptLineSalesTaxCodeRefFullName, C.ListID as SalesReceiptLineClassRefListID, C.FullName as SalesReceiptLineClassRefFullName, 3845 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item I inner join Class C on I.FullName = '1-002 A' and C.FullName = 'RIO 1'
insert into "SalesReceipt" (CustomerRefListID, ClassRefListID, TemplateRefListID,TxnDate, BillAddressAddr1, IsPending, DueDate, ShipDate, ItemSalesTaxRefListID, IsToBePrinted, CustomerSalesTaxCodeRefListID, DepositToAccountRefListID) select K.ListID as CustomerRefListID, C.ListID as ClassRefListID, T.ListID as TemplateRefListID, {d'2008-02-26'} as TxnDate, BillAddressAddr1, 0 as IsPending, {d'2008-02-26'} as DueDate, {d'2008-02-26'} as ShipDate, ItemSalesTaxRefListID, 0 as IsToBePrinted, SalesTaxCodeRefListID as CustomerSalesTaxCodeRefListID, A.ListID as DepositToAccountRefListID from ((Customer K inner join Account A on K.Name = 'cash' and A.ListId like '300000-%') inner join Class C on C.FullName = 'RIO 1') inner join Template T on T.Name like 'Custom Sales%'
I still get the same result.
I'm appending the screen print again. In case it doesn't show, the error reads as follows:
[QODBC] Error: 3000 - The given object ID "cash" in the field "list id" is invalid. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-28 12:42:20 |
Sorry, I can't debug any values from your external oracle data. Check the QODBC Messages Log is the QODBC Setup Screen Messages Tab for the true error and how your insert statement actually executed. |
|
|
|
|
Posted : 2008-02-28 14:11:21 |
Hello Tom,
No values are being extracted from Oracle at this time. The values that should come from Oracle in this example are hard coded and are the values '1-002 A' , 'RIO 1', 1955 and 3845.
In order to illustrate the problem I'm showing the insert for the SalesReceipt and the log for both the SalesReceipt and the first SalesReceiptLine. The RED Bolded text shows the problem.
insert into "SalesReceipt" (CustomerRefListID, ClassRefListID, TemplateRefListID,TxnDate, BillAddressAddr1, IsPending, DueDate, ShipDate, ItemSalesTaxRefListID, IsToBePrinted, CustomerSalesTaxCodeRefListID, DepositToAccountRefListID) select K.ListID as CustomerRefListID, C.ListID as ClassRefListID, T.ListID as TemplateRefListID, {d'2008-02-26'} as TxnDate, BillAddressAddr1, 0 as IsPending, {d'2008-02-26'} as DueDate, {d'2008-02-26'} as ShipDate, ItemSalesTaxRefListID, 0 as IsToBePrinted, SalesTaxCodeRefListID as CustomerSalesTaxCodeRefListID, A.ListID as DepositToAccountRefListID from ((Customer K inner join Account A on K.Name = 'cash' and A.ListId like '300000-%') inner join Class C on C.FullName = 'RIO 1') inner join Template T on T.Name like 'Custom Sales%'
<SalesReceiptAdd defMacro = "TxnID:037A9D8A-3ECF-429A-8C40-BCE63E43A694"> <CustomerRef> <ListID>1170000-1193756233</ListID> </CustomerRef> <ClassRef> <ListID>70000-1193756180</ListID> </ClassRef> <TemplateRef> <ListID>60000-1193756287</ListID> </TemplateRef> <TxnDate>2008-02-26</TxnDate> <BillAddress> <Addr1>2008-02-26</Addr1> </BillAddress> <IsPending>0</IsPending> <DueDate>2008-02-26</DueDate> <ShipDate>2008-02-26</ShipDate> <ItemSalesTaxRef> <ListID>cash</ListID> </ItemSalesTaxRef> <IsToBePrinted>0</IsToBePrinted> <CustomerSalesTaxCodeRef> <ListID>0</ListID> </CustomerSalesTaxCodeRef> <DepositToAccountRef> <ListID>2008-02-26</ListID> </DepositToAccountRef> <SalesReceiptLineAdd> <ItemRef> <ListID>670000-1193756172</ListID> </ItemRef> <Desc>Regular</Desc> <Quantity>1955.00000</Quantity> <Rate>1.79600</Rate> <ClassRef> <ListID>70000-1193756180</ListID> </ClassRef> <SalesTaxCodeRef> <ListID>20000-1193755957</ListID> </SalesTaxCodeRef> </SalesReceiptLineAdd>
When I do a select I get "cash" in the field BillAddressAddr1.
I don't see how this can be wrong in the insert.
Thanks for your help.
Regards,
Alecio
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-28 14:20:13 |
You will need to debug this yourself, this forum is only a product support forum, not a FREE consulting service. Try reverting back to using a INSERT statement with only VALUES and let me know if the same problem presents itself or not. |
|
|
|
|
Posted : 2008-02-28 14:36:45 |
Tom,
It was never my intention to get free consultancy other than being pointed where to look at. I must say that you have helped me a long way making my search in the forum less combersome, for which I thank you. I've reported my findings so other first time users like me, might benefit from this.
Thanks again for your help and when I resolve the problem I will close this topic with the solution.
Regards,
Alecio |
|
|
|
|
Posted : 2008-02-28 14:41:20 |
With values it has worked from the beginning. It's when getting the values from the Quickbooks tables I get the problem with the BillAddressAddr1.
Regards,
Alecio |
|
|
|
|
Posted : 2008-02-28 15:45:58 |
Tom,
This is an excerpt of the QODBCmessages.log with on the left side the result of the select statement used in the SalesReceipt and at the right the XML extract of the QODBC message.log file.
select K.ListID as CustomerRefListID, C.ListID as ClassRefListID, T.ListID as TemplateRefListID, {d'2008-02-26'} as TxnDate, BillAddressAddr1, 0 as IsPending, {d'2008-02-26'} as DueDate, {d'2008-02-26'} as ShipDate, ItemSalesTaxRefListID, 0 as IsToBePrinted, SalesTaxCodeRefListID as CustomerSalesTaxCodeRefListID, A.ListID as DepositToAccountRefListID from ((Customer K inner join Account A on K.Name = 'cash' and A.ListId like '300000-%') inner join Class C on C.FullName = 'RIO 1') inner join Template T on T.Name like 'Custom Sales%'
Select output XML Output
<CustomerRef> 1170000-1193756233 <ListID>1170000-1193756233</ListID> </CustomerRef> <ClassRef> 70000-1193756180 <ListID>70000-1193756180</ListID> </ClassRef> <TemplateRef> 60000-1193756287 <ListID>60000-1193756287</ListID> </TemplateRef> 2008-02-26 <TxnDate>2008-02-26</TxnDate> <BillAddress> cash <Addr1>2008-02-26</Addr1> </BillAddress> 0 <IsPending>0</IsPending> 2008-02-26 <DueDate>2008-02-26</DueDate> 2008-02-26 <ShipDate>2008-02-26</ShipDate> <ItemSalesTaxRef> 0000-1193756077 <ListID>cash</ListID> </ItemSalesTaxRef> 0 <IsToBePrinted>0</IsToBePrinted> <CustomerSalesTaxCodeRef> 10000-1193755957 <ListID>0</ListID> </CustomerSalesTaxCodeRef> <DepositToAccountRef> 300000-1193756166 <ListID>2008-02-26</ListID> </DepositToAccountRef>
Analyzing this, my conclusion is that everything went haywire when literals were combined with the table fields (ListID). To me this seems to be a bug. I can't be selecting something which should be put sequentially in the insert fields and it puts whatever it wants of the literals.....
Please advice.
Alecio |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-29 09:24:28 |
Correct, QODBC doesn't access a database at all. It takes a SQL statement and parses it into a XML request that is sent to QuickBooks and/or the SQL is processed be the QODBC optimizer SQLite backend, where it's excuted and results are returned. QODBC assumes joins are actually "real" relationship joins and not a forced rowset join as you are using it.
In your case, when you use values the inserts work correctly, so you should proceed execute the select subquery directly with your Oracle tables and not attempt to force QODBC to double back on itself. QODBC isn't simply designed to do this this way. |
|
|
|
|