SQL statement too long for RowSource property.... 
Author Message
 SQL statement too long for RowSource property....

Help!

I have a listbox in which I am attempting to use a SQL statement for the
RowSource (RowSourceType is set to 3 - SQL statement), but my SQL statement
is 527 characters long and it will only let me put 256 characters in the
RowSource property.

What can I do to fix this or get around this?

TIA,

Jeremy Domingue



Sat, 19 Aug 2000 03:00:00 GMT  
 SQL statement too long for RowSource property....

Jeremy,

You'll have to assign it in method code, like in the Init(). The VFP prop sheet uses memo
fields and the still semi-braindead only allowing 256 characters to be put in for an item.

df    - (Microsoft FoxPro MVP) http://www.geocities.com/ResearchTriangle/9834/

Quote:

>I have a listbox in which I am attempting to use a SQL statement for the
>RowSource (RowSourceType is set to 3 - SQL statement), but my SQL statement
>is 527 characters long and it will only let me put 256 characters in the
>RowSource property.



Sat, 19 Aug 2000 03:00:00 GMT  
 SQL statement too long for RowSource property....

Set it in the listbox Init() Method.

Quote:

>Help!

>I have a listbox in which I am attempting to use a SQL statement for the
>RowSource (RowSourceType is set to 3 - SQL statement), but my SQL statement
>is 527 characters long and it will only let me put 256 characters in the
>RowSource property.

>What can I do to fix this or get around this?

>TIA,

>Jeremy Domingue




Sat, 19 Aug 2000 03:00:00 GMT  
 SQL statement too long for RowSource property....

Okay, I think that method worked, but a new problem has arised.

When the list init's, it errors out telling me that the wrong parameters
were passed to the converter and that file 'varchar.prg' does not exist.

The following is my fox code:

THISFORM.List1.RowSource = 'SELECT RTRIM(CONVERT(varchar(6),
MasterAccounts.CustomerID))';
    + ', (MasterAccounts.LastName + SPACE(20 -
DATALENGTH(MasterAccounts.LastName))';
    + MasterAccounts.FirstName + SPACE(15 -
DATALENGTH(MasterAccounts.FirstName)) ;
    + SUBSTRING(MasterAccounts.Company, 1, 19);
    + SPACE(20 - DATALENGTH(SUBSTRING(MasterAccounts.Company, 1, 19)));
    + SubAccounts.Login)' + ' ;
        FROM MasterAccounts, SubAccounts;
        WHERE MasterAccounts.CustomerID = SubAccounts.CustomerID;
        AND MasterAccounts.LastName LIKE ' + '"%' +
ALLTRIM(THISFORM.Text1.Value) + '%"';
         + ' ORDER BY MasterAccounts.Lastname, SubAccounts.Login;
        INTO Cursor Maintemp';

I know the code is messy, but the problem (I think) is that Fox seems to be
trying to evaluate the SQL Convert function on the first line. I thought
that if code was contained in single quotes it wouldn't evaluate what was in
the middle? I know there must be another way to do this, but I can't seem to
find it....

TIA,

Jeremy Domingue

Quote:

>Jeremy,

>You'll have to assign it in method code, like in the Init(). The VFP prop
sheet uses memo
>fields and the still semi-braindead only allowing 256 characters to be put
in for an item.

>df    - (Microsoft FoxPro MVP)

http://www.geocities.com/ResearchTriangle/9834/
Quote:


>>I have a listbox in which I am attempting to use a SQL statement for the
>>RowSource (RowSourceType is set to 3 - SQL statement), but my SQL
statement
>>is 527 characters long and it will only let me put 256 characters in the
>>RowSource property.



Sat, 19 Aug 2000 03:00:00 GMT  
 SQL statement too long for RowSource property....

Jeremy,

Yes, Fox sees varchar() and convert() as functions that it should be able to evaluate, if
these are SQL functions provided by a backend then you might need to be doing a
SQL-PassThrough, and I've never done it so I don't know how else to direct you to a
solution other than to look at SQLExec() in the help file. In this case I think you'd call
SQLExec() with the SQL statement and set the ListBox.RowSource = "MainTemp".

df    - (Microsoft FoxPro MVP) http://www.geocities.com/ResearchTriangle/9834/

Quote:

>Okay, I think that method worked, but a new problem has arised.

>When the list init's, it errors out telling me that the wrong parameters
>were passed to the converter and that file 'varchar.prg' does not exist.

>The following is my fox code:

>THISFORM.List1.RowSource = 'SELECT RTRIM(CONVERT(varchar(6),
>MasterAccounts.CustomerID))';
>    + ', (MasterAccounts.LastName + SPACE(20 -
>DATALENGTH(MasterAccounts.LastName))';
>    + MasterAccounts.FirstName + SPACE(15 -
>DATALENGTH(MasterAccounts.FirstName)) ;
>    + SUBSTRING(MasterAccounts.Company, 1, 19);
>    + SPACE(20 - DATALENGTH(SUBSTRING(MasterAccounts.Company, 1, 19)));
>    + SubAccounts.Login)' + ' ;
>        FROM MasterAccounts, SubAccounts;
>        WHERE MasterAccounts.CustomerID = SubAccounts.CustomerID;
>        AND MasterAccounts.LastName LIKE ' + '"%' +
>ALLTRIM(THISFORM.Text1.Value) + '%"';
>         + ' ORDER BY MasterAccounts.Lastname, SubAccounts.Login;
>        INTO Cursor Maintemp';

>I know the code is messy, but the problem (I think) is that Fox seems to be
>trying to evaluate the SQL Convert function on the first line. I thought
>that if code was contained in single quotes it wouldn't evaluate what was in
>the middle? I know there must be another way to do this, but I can't seem to
>find it....



Sun, 20 Aug 2000 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. SQL statement too long for RowSource property....

2. Referring to property within select statement in list/combo box rowsource

3. Referring to property within select statement in list/combo box rowsource

4. ComboBox with RowSource=SQL Statement popping up Browse (data) window

5. Appropiate event for reruning a combos rowsource′s SQL-statement

6. Views - SQL Statement too long

7. Statement to long error in SQL

8. Qu. re: SQL statement too long

9. Simple SQL select statement takes way too long

10. SQL Statement Still to Long?

11. SQL-statement too long

12. [ODBC Visual FoxPro Driver]SQL: Statement too long.

 

 
Powered by phpBB® Forum Software