• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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
0
Member_2_7966101
Asked:
Member_2_7966101
  • 2
2 Solutions
 
[ fanpages ]IT Services ConsultantCommented:
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
 
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.
0
 
tliottaCommented:
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
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now