FireDAC Master/Detail Issue (SQLBindParameter Has Not Been Called For Parameter #2)

[Nathan Wild 3]

I have had issues before with master/detail relationships with FireDAC.

 

Here is my situation:

RadStudio 10.1-Berlin, connecting to Pervasive SQL via ODBC (although I have had similar problems with MSSQL-Native, and Access via ODBC.

Master dataset is a simple TFDMemTable with a few fields, the important one being an invoice number named "InvNmbr".

Detail  dataset is an equally simple TFDQuery .  In this case I only need ONE field in the query

 

 

If I set the  detail query SQL to a simple single-table "SELECT InvNmbr, TaxAmount1 FROM XTOTAL WHERE InvNmbr=:INVNMBR;", set MasterSource to my FDMemTable's datasource and set MaterFields to 'InvNmbr', I get the error in the subject line.

 

HOWEVER, if I get the detail query SQL to include a join on two related tables (which returns an identical recordset) "SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR", and set the MasterSource/MasterFields the same way, it works properly?!

 

I am at a loss to explain this?

 

Of note:

- There is only the one parameter so why is FireDAC even messing about with a parameter #2?

- I thought maybe the InvNmbr field and INVNMBNR parameters might be muddling things, so I tried fully qualifying it in the single table query (as "XTOTAL.InvNmbr") and it does the same thing.

- In my ODBC database the XMASTER and XTOTAL tables are both keyed on InvNmbr, and one matching record per InvNmbr exists in each table.

 

 

Code that does not work:

          fdqStatementMaster.SQL.Text := 'SELECT XTOTAL.InvNmbr, TaxAmount1 FROM XTOTAL WHERE XTOTAL.InvNmbr=:INVNMBR;';
          fdqStatementMaster.MasterSource := dsCustomerStatement;
          fdqStatementMaster.MasterFields := 'InvNmbr';
          fdqStatementMaster.Open();
 

 

Code that does work:       

         PrepTempQuery(fdqStatementMaster, 'SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR';
          fdqStatementMaster.MasterSource := dsCustomerStatement;
          fdqStatementMaster.MasterFields := 'InvNmbr';
          fdqStatementMaster.Open();

 

I am baffled and frustrated and will be extremely grateful for a nudge in the right direction.

[Guest]

I would not try to even think about it without traceable sources, you have the sources? If I remember correctly they are not in professional. That is why I use a 3rd party alternative. 

 

HTH