Solved

Unable to insert second line output line in ODBC connection

Posted on 2016-11-04
4
113 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 250 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 250 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 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