Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unable to insert second line output line in ODBC connection

Posted on 2016-11-04
4
Medium Priority
?
136 Views
Last Modified: 2016-11-05
I am using QODBCto connect Excel to Quickbooks. I have created an insert line that works correctly but I am unable to add a second line. From my reading of the  documentation, adding the value, "FQSaveToCache" as true (1)  should tell Quickbooks to continue the lines but I have not been successful (The multilines are for a purchase order)

Here is my original insert which successfully produces a single line:

sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, TemplateRefListId, VendorRefListId, FOB, CustomFieldOther1, VendorMsg,) VALUES ('" & PO_Description & "', " & PO_Qty & ", '" & PO_Template & "' , '" & PO_VendorListID & "', '" & PO_FOB & "', '" & PO_Buyer & "', '" & PO_VendorMessage & "' )"

Here is my attempt at 2 lines which fails:


sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, TemplateRefListId, VendorRefListId, FOB, CustomFieldOther1, VendorMsg, FQSaveToCache) VALUES ('" & PO_Description & "', " & PO_Qty & ", '" & PO_Template & "' , '" & PO_VendorListID & "', '" & PO_FOB & "', '" & PO_Buyer & "', '" & PO_VendorMessage & "', 1)"


sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, TemplateRefListId, VendorRefListId, FOB, CustomFieldOther1, VendorMsg, FQSaveToCache) VALUES ('" & PO_Description & "', " & PO_Qty & ", '" & PO_Template & "' , '" & PO_VendorListID & "', '" & PO_FOB & "', '" & PO_Buyer & "', '" & PO_VendorMessage & "', 0)"

Any help on this would be greatly appreciated

Thank you
0
Comment
Question by:Member_2_7966101
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 1000 total points
ID: 41874485
Are any of the columns in your INSERT statement set as Primary Keys in the [PurchaseOrderLine] table?

That is, for example, are the values for the [TemplateRefListId] column &/or the value of [VendorRefListId] also static;  PO_Template, & PO_VendorListID, respectively, & are either of these columns expected to be unique key values?
0
 

Author Comment

by:Member_2_7966101
ID: 41874588
Thank you for replying. That's what has me confused. Since its a multi line purchase order, I can't think of any unique value that would have to change in a new line. The only thing that would possibly change on a new line would be the description. I did try changing the description but that didn't work. However, something just jogged my memory; I did this about a year ago with an estimate and it might be that I'm missing a necessary key that is needed for multiple entry.I'm away from my desk right now but when I return, I'm going to look at the table and see if something looks familiar to me.
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 1000 total points
ID: 41875097
A Line_Number column would be needed with Line_Number = 1 for the first line and Line_Number = 2 for the second line. (Such a column should be named according to what it means. I used "Line_Number" as an example name.)

Changing that column's value would distinguish the two lines. The column would be one part of a compound key. A compound key is going to be necessary unless you add some kind of identity column that might be auto-generated and assign it as the unique primary key.
0
 

Author Comment

by:Member_2_7966101
ID: 41875344
Thank you both  for the replies

I actually tried line number column before I posted this question. There is a column in the PurchaseOrderLIne table titled, "PurchaseOrderLineSeqNumber"  However, when I tried to include it in the string, I got a "not available"  error.

I did finally find the key I needed which was to install the boolean "FQSaveToCache" key which signals that the lines are ongoing. I had tried using this key earlier but it failed because I was executing the lines into one send instead of sending individually. The solution is to:

1) Install the FQSaveToCache boolean
2) Execute on each line

Successful code (Simplified for troubleshooting):

sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, RefNumber, FQSaveToCache ) VALUES ('" & PO_Desc & "', " & PO_Qty & ", '" & PO_RefNum & "', 1)"
oConnection.Execute (sSQL)


sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, RefNumber, FQSaveToCache ) VALUES ('" & PO_Desc & "', " & PO_Qty & ", '" & PO_RefNum & "', 1)"
oConnection.Execute (sSQL)


sSQL = "INSERT INTO PurchaseOrderLine (PurchaseOrderLineDesc, PurchaseOrderLineQuantity, RefNumber, FQSaveToCache ) VALUES ('" & PO_Desc & "', " & PO_Qty & ", '" & PO_RefNum & "', 0)"
oConnection.Execute (sSQL)

I couldn't really figure out which of your two replies were the best solution so I chose the first one simply because it was first and I've split the amounts equally. Thank you for your help.
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question