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 : Error inserting multiple SalesReceiptLine recordsSearch Forum

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

 New Topic 
 
 Post Reply 
[1]  
 Error inserting multiple SalesReceiptLine records 
 Author   Message 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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.

 

  Top 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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. 

  Top 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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.

 

  Top 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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

 

  Top 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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

 

  Top 
  Alecio 
  
 Group: Members 
 Posts: 10 
 Joined: 2008-02-19 
 Profile
 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

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to