Creating an array with SQL

It's easy to create an array with SQL in FoxPro by using the INTO ARRAY clause:

SELECT Cust_ID ;
   FROM Customer ;
   INTO ARRAY laCust ;
   WHERE Country = 'England'

The SQL statement will create an array of the right size and shape to hold the records retrieved. Problems arise if no records match the criteria; nothing will be returned and the array will not be created. Any attempt to refer to this non-existent array will cause an error.

The solution is to use the _TALLY memory variable to test whether any records have been returned:

IF _TALLY = 0
   *-- The array will not have been created
   *-- Create a single-element array and put a ' ' in it
   DIMENSION laCust[1]
   laCust[1] = SPACE (1)
ENDIF

You don't get this problem when using SQL to create a cursor or table. If no records are selected then FoxPro can create an empty cursor or table but it cannot create an array of length zero.

FoxPro maintains the value in _TALLY automatically and updates it whenever the following commands are used:

APPEND FROM
AVERAGE
CALCULATE
COPY TO
COUNT
DELETE
INDEX
PACK
REINDEX
REPLACE
SELECT - SQL
SORT
SUM
TOTAL
UPDATE

The Site Search feature requires JavaScript. If your browser does not support Javascript then please use our Site Map instead.

Hints & tips

The textbox class in Visual FoxPro 9 has a new Autocomplete property which shows the user the previous values that have been entered in that textbox.
Autocomplete in VFP 9

Your Access database will look more impressive if you add custom toolbars...
Custom toolbars

FoxPro has always had functions to read and write files at a low level...
Foxpro low level file functions

More...
More pages of hints and tips for users of Microsoft FoxPro and Access databases.

Site Map