Search code examples
foxproiif

FoxPro immediate if (IIF) - Function name is missing ) error

OK, based on the error message, you would think this is simple (and it probably is...).

I'm using an IIF (immediate if) in a FoxPro 9.2 database to evaluate an expression and then perform one of two actions depending on how the expression evaluates (basically, if a record exists, update it; if not, insert it).

IIF(
    ((SELECT COUNT(*) FROM tblName 
    WHERE tblName.Fldname = 'FLDMy' AND tblName.rCode = '000004') > 0), 
    (UPDATE tblName SET tblName.Desc = 'Me' 
    WHERE tblName.Fldname = 'FLDMy' AND tblName.rCode = '000004'), 
    (INSERT INTO tblName(Fldname, rCode, Desc) 
    VALUES ('FLDMy','000004','Me'))
    )

This always returns error: "Function name is missing )." Note that all the parentheses are matched, so it's not missing a ")" anywhere. The individual UPDATE and INSERT pieces work fine when run separately, so I suspect it's the expression (first statement in the IIF) that's the problem.

I'm executing the SQL from a C# service, so I'd like to be able to do the check and action in a single call into the FoxPro database.

Solution

  • You're confusing VFP. "SELECT" is a (built-in) SQL command, a built-in command to change the current work area, and it's also a function which returns the currently selected work area number.

    In addition, IIF() is a function which takes expressions (something that evaluates to a single value) as its parameters. The SQL SELECT and UPDATE commands don't do that, even when you wrap them in parentheses. It's really for returning one of two values based on the value of the first parameter - it's really not for control flow logic. I suggest rewriting your code in the form of an IF .. ELSE.. ENDIF block.