'Field' Phrase is not found. 3

[dantheinfoman]

Hi All,

I have a rowsource that used to work in vfp 7 and now in vfp 9 several things have gone wrong. Originally I had an issue with the GROUP BY error, so I removed the group by items from the statement (it was making it more than 255 characters when I was trying to fix).
Now the rowsource SQL statement will load into the combobox, but it says 'field' phrase is not found

The RowSourceType is SQL, the code is this:

m.abc ="select allt(name)+', '+allt(first_name) as NM,py_emplo.employee,training,date_taken from py_emplo,petrhist"+" where py_emplo.employee=petrhist.employee and py_emplo.classific<>'Term' order by NM,py_emplo.employee into cursor cEmp1"

*temp
*!*	SELECT (abc)
*!*	BROWSE

*WAIT WINDOW m.abc
this.rowsource = (abc)

I read somewhere online that you can't have references to multiple tables, but it works in vfp 7, so I question why they'd downgrade a combobox to only using data from one table in 9.0?

Thanks for any advice as my brain is mush from all the converting I've been doing and upgrades.

Yours,
Dan

 

[Olaf Doschke]

rowsourcetype has to be correct, intellisense and/or the property window will tell you which setting is for a query as rowsource.

Bye, Olaf.

 

[Mike Lewis]

Are you sure the Rowsourcetype is set to 3? In general, you get "'Field' phrase not found" if there RowSourcetype is 6 and the Rowsource is syntactically incorrect. I suggest you check the Rowsourcetype before going any further.

Also, regarding the limit of 255 characters: that is the maximimum size of a literal string. A SQL statement can be much longer than that, but you have to write it as smaller substrings and then join them together. Better still, use TEXT/ENDTEXT. That will make the whole thing more readable and help eliminate mistakes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

I agree with Mike, use Text-Endtext to "construct" your variable abc. This is how to do it that way:
[pre]

text to abc noshow pretext 7
select
allt(name) + ', ' + allt(first_name) as NM,
py_emplo.employee,
training,
date_taken
from py_emplo, petrhist
where py_emplo.employee = petrhist.employee
and py_emplo.classific <> 'Term'
order by NM,py_emplo.employee
into cursor cEmp1
EndText
[/pre]
One final note: to my eyes, abc is a terrible variable name, I would use something like lcSelectString instead. In this case "l" means it's a local variable, "c" means that it's a character string. You can read more about the most commonly used naming convention here:

https://msdn.microsoft.com/en-US/library/4k6dthew(v=vs.80).aspx

 

[dantheinfoman]

Thanks everybody. I have verified that the RowSourceType is 3 - SQL Statement. When I try this way:

text to lcSelectString noshow pretext 7
   select 
      allt(name) + ', ' + allt(first_name) as NM,
      py_emplo.employee,
      training,
      date_taken 
   from py_emplo, petrhist 
   where py_emplo.employee = petrhist.employee 
      and py_emplo.classific <> 'Term' 
   order by NM,py_emplo.employee 
   into cursor cEmp1
EndText

this.rowsource = (lcSelectString)

It says 'Table number is invalid' and then goes on to taunt me with the 'field' phrase is not found. It's being a real poo-head.

Thanks for all the replies thus far and thank you in advance for any advice.

Yours,
Dan

 

[Tore Bleken]

Remove the last ().

text to lcSelectString noshow pretext 7
   select 
      allt(name) + ', ' + allt(first_name) as NM,
      py_emplo.employee,
      training,
      date_taken 
   from py_emplo, petrhist 
   where py_emplo.employee = petrhist.employee 
      and py_emplo.classific <> 'Term' 
   order by NM,py_emplo.employee 
   into cursor cEmp1
EndText

this.rowsource = lcSelectString

 

[dantheinfoman]

Hi tbleken,

Thanks for the reply, unfortunately it still says 'Table number is invalid' and field phase is not found.

Thanks,
Dan

 

[Mike Lewis]

Dan,

What happens when you execute the SQL statement on its own? In other words, don't bother with the TEXT/ENDTEXT, and forget about the listbox. Just execute the SQL from the command window. Do you get the same error?

The reason I ask is that will tell you whether the problem lies with the SQL or with the listbox. Once we know that, it might be easier to figure out what's going wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

The query runs in the command window?

I'd find a way to ditch the Alltrim() in the query. If the first record encountered by the query happens to have both fields empty you'll get a column width of 0, which is an error condition. Even if they have data in them, all of the fields will be truncated to the width of the first row encountered by the query.

SQL output columns must evaluate to a consistent width.

 

[dantheinfoman]

Hi Mike,

The SQL is fine, it's the combobox that's being a jerk. When I run the SQL and BROWSE NORM, it looks fine:

Thanks!
Dan

 

[dantheinfoman]

Hi danfreeman,

I took ALLT out of the statement, but it still says the error about table number isn't valid and the field phrase is not found.

Thanks all,
Dan

 

[Mike Lewis]

Good. So we know it's the listbox that's at fault. So the next step is to set all the listbox's properties back to their defaults, except ColumnCount (which needs to be 4), RecordSourceType (3) and RecordSource (your SQL statement). If that works, start changing the other properties back to their previous settings, one at a time, until it goes wrong again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Make sure that the cursor name, cEmp1 in your case, isn't used other places. It must be unique.

 

[dantheinfoman]

SOLUTION: I couldn't get it working this way, so I emulated my predecessor's method:

They put something like:

IF m.glUseCode
	*WAIT WINDOW 3
	SELECT ALLT(py_emplo.NAME)+', '+ALLT(py_emplo.first_name) AS NAME2, py_emplo.employee as hello1, py_emplo.classific,petrhist.training,petrhist.date_taken,;
		py_emplo.home_job;
		FROM py_emplo,petrhist;
		where py_emplo.classific<>"Term" AND py_emplo.employee = petrhist.employee;
		ORDER BY hello1,name2;
		INTO CURSOR cEmp1
ELSE
	*WAIT WINDOW 4
	SELECT ALLT(py_emplo.NAME)+', '+ALLT(py_emplo.first_name) AS NAME2,py_emplo.employee AS hello1,petrhist.training,petrhist.date_taken,;
		py_emplo.home_job;
		FROM py_emplo,PETRHIST;
		where py_emplo.classific<>"Term" AND py_emplo.employee = petrhist.employee;
		ORDER BY name2,EMPLOYEE;
		INTO CURSOR cEmp1
ENDIF

Into the LOAD event, then changed the rowsourcetype to ALIAS (2) and put something like this as the RowSource:

cEmp1.name2,hello1,training,date_taken

AND IT WORKS! You are all brilliant and thanks for your patience and advice!!
Dan

 

[Olaf Doschke]

Rowsourcetype 4 has to be a SQL Select in one line, no line breaks allowed. If at all, you need to use line continuation ; at the end of each line.
Executeing the SQL and using the cursorname as alias or fields rowsourcetype is the other solution, yes.

Your initial code only had a single line sql query, so that would work.

Edit: To be more to the point: The query you put into the TEXT..ENDTEXT section starts with a line only containing the word SELECT. Coincidence is SELECT also is a valid VFP command with a table number or alias, As you dont have semicolons or don't remove line breaks, you have a script, which start with the SELECT command instead of the SQL-SELECT command.

Bye, Olaf.

 

[Koen Piller]

Hi,

If you realy would like to get rid of alltrim you may use the '-' syntax:

Select py_emplo.Name-', '-py_emplo.first_name As NAME2, py_emplo.employee As hello1, py_emplo.classific,petrhist.training,petrhist.date_taken,;
	py_emplo.home_job;
	FROM py_emplo,petrhist;
	where py_emplo.classific<>"Term" And py_emplo.employee = petrhist.employee;
	ORDER By hello1,NAME2;
	INTO Cursor cEmp1

rgds,
Jockey2

 

[Mike Lewis]

Just to clarify the point about getting rid of the ALLTRIM() ....

When you run a SQL statement, VFP has to know how big to make each column in the result set. If the column is a simple field, then it takes the size of that field for the result column. If the column is based on an expression, it takes the size of that expression in the first record of the result set.

In this case, the expression is:

[tt]ALLT(py_emplo.NAME)+', '+ALLT(py_emplo.first_name)[/tt]

So, if the name and first_name fields are only one character in the first record, then the total width of that expression in the first record will be four characters (one each for the name and first_name, and two for the comma + space). So the entire column will only be four characters wide, and all subsequent names will be truncated.

The solution is to force the expression to be a reasonable size, for example:

[tt]PADR(ALLT(py_emplo.NAME)+', '+ALLT(py_emplo.first_name), 18)[/tt]

This wouldn't have solved your original problem, but it's a point that you will always need to keep in mind when writing SELECT statements.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads