PRB: REPLACE Command Replaces Field in Selected Table & Cursor

ID: Q120459

The information in this article applies to:

SYMPTOMS

If you create a cursor from a SELECT-SQL statement and then select the original table and issue a REPLACE command against the original table, the replacement takes place against both the original table and the cursor.

For example, issue the following commands in the Command window: 

   USE customer    && Use the CUSTOMER table in the TUTORIAL subdirectory
   SELECT * FROM customer INTO CURSOR temp
   SELECT customer
   REPLACE ALL customer.company WITH " "
The REPLACE command works as expected on the CUSTOMER table; however, every COMPANY field for each record in the TEMP cursor now also has no values. In essence, the one REPLACE command takes place on both the table and the cursor.

CAUSE

Because the original query did not specify any criteria that would cause the results to return a structure different than the original table, FoxPro just returns a "phantom" cursor that is really a pointer or reference to the original table.

RESOLUTION

If you SELECT the fields from the original table into another table, rather than a cursor, this behavior will not occur.

For example, the following sequence of commands will behave correctly: 

   USE customer
   SELECT * FROM customer INTO TABLE temp
   SELECT customer
   REPLACE ALL customer.company WITH " "

MORE INFORMATION

There are other similar sequences of commands that can duplicate this behavior. For example: 

   USE customer
   SELECT * FROM customer INTO CURSOR temp
   SELECT customer
   ZAP
When you issue these commands, FoxPro will generate an error message from the ZAP command saying the "File is open in another work area." This message is referring to the original table.

Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b 2.50c 2.60 database KBCategory: kbprg kbprb KBSubcategory: FxprgSql

Last Reviewed: August 28, 1995