Unable to insert second line output line in ODBC connection

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
Member_2_7966101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fanpagesCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_7966101Author Commented:
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.
Member_2_276102Commented:
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.
Member_2_7966101Author Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
QuickBooks

From novice to tech pro — start learning today.