VFPOledb9 - Microsoft ODBC Visual Foxpro Driver Function Name is Missing when using Delphi 7 ADO Query

Question

Using Delphi 7/AdoQuery

qry1.close;
qry1.sql.clear;
qry1.SQL.text:=**'UPDATE ardata SET saletodate = (SELECT sum(tot_rm_amt) from saledat where saledat.short_name=ardata.short_name )';**

//qry1.SQL.text:='UPDATE ardata SET saletodate =0' ; This works
qry1.execsql;

Nb. ardata=ardata.dbf/saledat=saledat.dbf,saletodate=ardata->saletodate,tot_rm_amt= saledat->tot_rm_amt

error : Microsoft ODBC Visual Foxpro Driver Function Name is Missing when using Delphi 7 ADO Query

What is causing the error msg ?

Answer 1

Your error should be that you messed up OLEDB and ODBC drivers. Use VFPOLEDB (OLEDB) driver and all should be OK. For example this works perfectly fine in C# (sample arData and saleDat are in d:\backyard\temp):

void Main()
{
    using (OleDbConnection cn = new OleDbConnection(@"Provider=VFPOLEDB;Data Source=d:\backyard\temp"))
    {
        string sql = @"UPDATE ardata 
            SET saletodate = (SELECT sum(tot_rm_amt) 
               from saledat 
               where saledat.short_name=ardata.short_name )";
        cn.Open();     
        var rowsAffected = new OleDbCommand(sql, cn).ExecuteNonQuery();
        cn.Close();
        
        Console.WriteLine(rowsAffected);
    }
}

EDIT: When there are no matching short_name in saleDat this would fail if saleToDate doesn't accept nulls. You can fix it in two ways:

Applying VFP specific nvl() to sum():

string sql = @"UPDATE ardata 
            SET saletodate = (SELECT nvl(sum(tot_rm_amt), 0)
               from saledat 
               where saledat.short_name=ardata.short_name )";

Rewriting the SQL in a more ANSI compatible manner:

string sql = @"UPDATE ardata 
            SET saletodate = tmp.totAmt 
               from (select short_name, sum(tot_rm_amt) as totAmt
                     from saleDat
                     group by short_name) tmp
               where tmp.short_name=ardata.short_name";

What the latter one does is: It gets all the sums for all the short_names available in saleDat into a tmp cursor (looks like):

short_name, totAmt
1, 10
2, 15

Then uses that tmp cursor as the source matching tmp.short_name = ardata.short_name.

Unlike the former, for the non-matching ones no update is applied (instead of setting to 0, they are left as is).