Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual FoxPro
Expertise: Beginner
Nov 16, 1999

Basic FoxPro SQL

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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date