Solved

Unable to insert second line output line in ODBC connection

Posted on 2016-11-04
4
28 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
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now