Question:
I’m trying to retrieve eight fields from four FoxPro tables via on-the-fly SQL from an ASP page… and I’m not familiar with FoxPro SQL.
The following doesn’t work, but represents what I’d like to pull off:
CPSQL = "SELECT Part.SSN, Part.Plan_ID, Part.FirstName & ' ' & Part.Last_Name AS Name, "CPSQL = CPSQL & "Fund.Fund_Desc, Holdings.Units, Unitval.Unit_Val, "CPSQL = CPSQL & "(Holdings.Units * Unitval.Unit_Val) AS Balance ; "CPSQL = CPSQL & "FROM Part, Holdings, Fund, UnitVal ; "CPSQL = CPSQL & "WHERE Holdings.SSN = Part.SSN AND Part.Plan_ID = Holdings.Plan_ID "CPSQL = CPSQL & "AND Holdings.Plan_ID = UnitVal.Plan_ID AND Holdings.Fund_ID = " CPSQL = CPSQL & "UnitVal.Fund_ID; AND Holdings.As_of_Date = Unitval.As_of_Date "CPSQL = CPSQL & "AND UnitVal.Fund_ID = Fund.Fund_ID;"
Sorry if this is formatted strangely; I hope you can widen the screen when you view it. What am I missing? Do I need to split this out a bit?
Answer:
VFP uses “&” to denote macro expansion, “+” for concatenation, and “;” to denote line continuation. The first line of code you provided is attempting to create a field called “name,” which is a concatenation of first name followed by a space followed by last name. Therefore you need to replace the “&”s with “+”s within the string.
You need to change the first line of code to this:
CPSQL = "SELECT Part.SSN, Part.Plan_ID, Part.FirstName + ' ' + Part.Last_Name AS Name, "
Your code also contains semicolons (;) in the string. Remove them.
