|
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
|
|
| MS Access ODBC fails to Insert Estimate. |
| Author |
Message |
|
|
| Posted : 2006-12-06 02:00:25 |
Using...
stSQL = "INSERT INTO EstimateLine (EstimateLineItemRefListID, EstimateLineDesc, EstimateLineRate, EstimateLineAmount, EstimateLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('A0001-1165288123', 'Floppy Disk', 40.00000, 45.00, '10000-1165253603', 1)"
Set rst = .Execute(stSQL)
stSQL = "INSERT INTO Estimate (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID) VALUES ('40000-1165288828', '2', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '50000-1165253855', '50000-1165253944', 'Memo Test', '10000-1165253603')"
Set rst = .Execute(stSQL)
QODBCLog shows:
2006-12-05 20:57:31 QODBC Ver: 7.00.00.199 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO Estimate (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID) VALUES (
'40000-1165288828', '2', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '50000-1165253855', '50000-1165253944', 'Memo Test', '10000-1165253603')
Child Record must be inserted before Header Record in BuildXMLHeader
Any ideas on how we can make it work?
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-06 09:19:32 |
The error is saying that the two statements are being executed as seperate connections, so the first cached insert was lost. In order to cache inserts using FQSaveToCache, the connection must not be broken between the insert statements. In your case, your example can be done as s single insert like this instead:
stSQL = "INSERT INTO EstimateLine (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID, EstimateLineItemRefListID, EstimateLineDesc, EstimateLineRate, EstimateLineAmount, EstimateLineSalesTaxCodeRefListID) VALUES ('40000-1165288828', '2', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '50000-1165253855', '50000-1165253944', 'Memo Test', '10000-1165253603', 'A0001-1165288123', 'Floppy Disk', 40.00000, 45.00, '10000-1165253603')"
Set rst = .Execute(stSQL)
without the need to use the Estimate header table. |
|
|
|
|
|
| Posted : 2006-12-07 03:32:08 |
Hi Tom,
Thanks for you speedy reply...
When I try this:
stSQL = "INSERT INTO EstimateLine (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID, EstimateLineItemRefListID, EstimateLineDesc, EstimateLineRate, EstimateLineAmount, EstimateLineSalesTaxCodeRefListID) VALUES ('40000-1165288828', 'TEST01', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '50000-1165253855', '50000-1165253944', 'Memo Test', '10000-1165253603', 'A0001-1165288123', 'Floppy Disk', 40.00000, 45.00, '10000-1165253603');"
Set rst = .Execute(stSQL)
I now get an application error as follows:
Run-time error '-2147217900' (80040e14)':
Syntax error in INSERT INTO statement.
The code above is copied out of th VBA module.
For other commands like this,
stSQL = "SELECT Estimate.RefNumber, Estimate.TxnDate, Estimate.CustomerRefFullName FROM Estimate WHERE (((Estimate.TxnDate)>Now()-Month(1))) ORDER BY Estimate.TxnDate DESC;"
It works perfectly.
Wondering if you may be help to assist further. Thank you. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-07 08:41:52 |
Try using VB Demo and this INSERT statement:
INSERT INTO EstimateLine (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID, EstimateLineItemRefListID, EstimateLineDesc, EstimateLineRate, EstimateLineAmount, EstimateLineSalesTaxCodeRefListID) VALUES ('40000-1165288828', '2', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '50000-1165253855', '50000-1165253944', 'Memo Test', '10000-1165253603', 'A0001-1165288123', 'Floppy Disk', 40.00000, 45.00, '10000-1165253603')
I've used your data here, so I cannont test it. VB Demo will show the text of Error 80040e14 so you should be able to see what element is incorrect.
My test INSERT that worked without error with the Sample Rock Castle Construction company file with QuickBooks 2006 Premier Edition (USA Version) was:
INSERT INTO EstimateLine (CustomerRefListID, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, TermsRefListID, ItemSalesTaxRefListID, Memo, CustomerSalesTaxCodeRefListID, EstimateLineItemRefListID, EstimateLineDesc, EstimateLineRate, EstimateLineAmount, EstimateLineSalesTaxCodeRefListID) VALUES ('4C0000-1071523181', '2', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', '30000-933272658', '2E0000-933272656', 'Memo Test', '10000-999022286', '250000-933272656', 'Building permit 1', 40.00000, 45.00, '20000-999022286') |
|
|
|
|