Hello all,

I have reached my wits end with this one. I am developing a small application for tracking orders and preparing invoices. I am using VFP9 and I am connecting to a vfp database using SPT (for scalability purposes down the road). The way the application works each order has a record in the order table and each order is connected to one firm(customer). Once an order has been completed the user will generate an invoice that will create a record in the order_invoice table for that Order. The following query is used to generate a list of all orders, invoice and non-invoiced, that I then use to help the user navigate to the next step (generate invoice or edit invoice).

tcInvNumber = ALLTRIM("")+'%'
tcJobNumber = ALLTRIM("05APR0001")+'%'
tcCaseNumber = ALLTRIM("")+'%'
tcFirmName = ALLTRIM("")+'%'
SELECT o.order_id, o.jobNumber, o.casenumber, o.Status, f.firm_name, oi.inv_num ;
  FROM order AS o ;
  JOIN firm AS f ;
    ON o.firm_id = f.firm_id ;
  LEFT JOIN (SELECT * FROM order_invoice WHERE TRANSFORM(inv_num) LIKE ?tcInvNumber) AS oi ;
    ON o.order_id = oi.order_id;
 WHERE o.JobNumber LIKE ?tcJobNumber ;
   AND o.CaseNumber LIKE ?tcCaseNumber; 
   AND f.firm_name LIKE ?tcFirmName 

This query produces the results that I want when I execute through foxpro directly however when I execute the statement through SPT as follows I get a generic syntax error. Connectivity error: [Microsoft][ODBC Visual FoxPro Driver]Syntax error.

tcInvNumber = ALLTRIM("")+'%'
tcJobNumber = ALLTRIM("05APR0001")+'%'
tcCaseNumber = ALLTRIM("")+'%'
tcFirmName = ALLTRIM("")+'%'
lcSql = []
lcSql = lcSql + [SELECT o.order_id, o.jobNumber, o.casenumber, o.Status, f.firm_name, oi.inv_num ]
lcSql = lcSql + [  FROM order AS o ]
lcSql = lcSql + [  JOIN firm AS f ]
lcSql = lcSql + [    ON o.firm_id = f.firm_id ]
lcSql = lcSql + [  LEFT JOIN (SELECT * FROM order_invoice WHERE TRANSFORM(inv_num) LIKE ?tcInvNumber) AS oi ]
lcSql = lcSql + [    ON o.order_id = oi.order_id]
lcSql = lcSql + [ WHERE o.JobNumber LIKE ?tcJobNumber ]
lcSql = lcSql + [   AND o.CaseNumber LIKE ?tcCaseNumber ]
lcSql = lcSql + [   AND f.firm_name LIKE ?tcFirmName ]
lnExec = SQLEXEC(THIS.nConnHandle,lcSql,tcCursor)

What am I missing?

Matt