Error using SQL to populate grid

I have a 9 column grid that I'm trying to populate using an SQL statement in the RecordSource property of the grid. The statement works fine if I only use 8 fields, but when I try to add the ninth field, in any order, I get a "Command contains unrecognized phrase/keyword" error.

This statement works -
    thisform.grdmatching.RecordSource="select main.main_id, main.prepdate, ;
    main.reqby, act.desc, main.episode, vendors.vendor, ;
    detail.desc, detail.estprice ;
    from main,act,vendors,detail ;
    where act.act_id=main.act_id AND ;
    vendors.vendor_id=main.vendor_id AND ;
    detail.main_id=main.main_id AND ;
    "+m.filterit+" INTO CURSOR temp"

As does this statement-
    thisform.grdmatching.RecordSource="select main.main_id, main.prepdate, ;
    main.reqby, act.desc, main.episode, vendors.vendor, ;
    detail.desc, main.status ;
    from main,act,vendors,detail ;
    where act.act_id=main.act_id AND ;
    vendors.vendor_id=main.vendor_id AND ;
    detail.main_id=main.main_id AND ;
    "+m.filterit+" INTO CURSOR temp"

But if I try to make the select list 9 fields, I get the error. The below statement will give me the error -
    thisform.grdmatching.RecordSource="select main.main_id, main.prepdate, ;
    main.reqby, act.desc, main.episode, vendors.vendor, ;
    detail.desc, detail.estprice, main.status ;
    from main,act,vendors,detail ;
    where act.act_id=main.act_id AND ;
    vendors.vendor_id=main.vendor_id AND ;
    detail.main_id=main.main_id AND ;
    "+m.filterit+" INTO CURSOR temp"

The variable m.filterit is a filter condition that is built programatically, but it is constant throughout all three examples above.

What am I missing here

Answer this question

Error using SQL to populate grid

Bob Walter
Robert,
In VFP character literals are limited to 255 characters. However you can use character variables instead. ie:

text to m.lcSQL textmerge noshow
select main.main_id, main.prepdate, ;
    main.reqby, act.desc, main.episode, vendors.vendor, ;
    detail.desc, detail.estprice, main.status ;
    from main,act,vendors,detail ;
    where act.act_id=main.act_id AND ;
    vendors.vendor_id=main.vendor_id AND ;
    detail.main_id=main.main_id AND <<m.filterit>> ;
    INTO CURSOR temp
endtext
thisform.grdmatching.RecordSource = ;
   Chrtran(m.lcSQL,Chr(9)+';'+Chr(13)+Chr(10),Space(2))

Your code might also be written like this:

m.filterit = iif(m.SomeCondition, "where main.prepdate < date()", "")

text to m.lcSQL textmerge noshow
select main.main_id, main.prepdate, ;
    main.reqby, act.desc, main.episode, vendors.vendor, ;
    detail.desc, detail.estprice, main.status ;
    from main ;
    inner join act       on act.act_id=main.act_id ;
    inner join vendors on vendors.vendor_id=main.vendor_id ;
    inner join detail    on detail.main_id=main.main_id ;
    <<m.filterit>> ;
    INTO CURSOR temp
endtext
thisform.grdmatching.RecordSource = ;
   Chrtran(m.lcSQL,Chr(9)+';'+Chr(13)+Chr(10),Space(2))
Kev Matthews
Robert,

Maybe the number of characters you have in your select statement is more than the maximum characters allowed for an sql statement... to minimize your 'Select' statement you do like this, instead of specifing the whole table name store it in a variable

ie. select m.reqby, a.desc, m.episode, from main m, act a ......

Hope i got your problem right.
Shaurya Anand
That was the problem! I initially thought it was some sort of character limit but only investigated SQL character limits, which is something like 3000. Never dawned on me about the character literal limit.

The TEXT TO statement worked great, and with an added PRETEXT 15 argument, all that's left to strip out with CHRTRAN is the semicolons.

Thanks much.
Error using SQL to populate grid

Answer this question