PRB: Error "No Table is in Use" with Screen Based on SQL

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6, 2.6a
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6, 2.6a
• Microsoft FoxPro for Macintosh, version 2.6a

SYMPTOMS

The SHOW GETS command generates the error, "No Table is in Use" when a table is in use in the current work area.

CAUSE

The table involved in the current read was overwritten while it was open. For example, if you performed a query on a table and then sent the results back to the same open table, you may encounter this problem. Even though the two tables have the same structure, when the original structure is overwritten, the link between the GET field and the table is broken. Therefore, a SHOW GETS command issued against the current READ fails because the original table involved in the READ is no longer in existence.

MORE INFORMATION

Code to Reproduce Behavior

The Following program demonstrates how the "No Table is in Use" error message can be generated. This example places a get field on a screen. Then it performs a query against a table, and writes the results into the same table. This causes the original table structure to be rewritten on top of the currently open structure.

To duplicate this scenario create a program with the following code, then run it.

**************************

SELECT CUSTOMER.CNO ;

   FROM CUSTOMER, INVOICES ;
   WHERE INVOICES.CNO = CUSTOMER.CNO ;
   INTO TABLE TEMP

CLOSE DATABASES SET SAFETY OFF

IF ! USED("INVOICES") && Use the Invoices table from tutorial directory

   USE INVOICES IN 0

IF ! USED("CUSTOMER") && Use the Customer table from tutorial directory

   USE CUSTOMER IN 0

IF ! USED("TEMP")

   USE TEMP IN 0

SELECT CUSTOMER

DEFINE WINDOW TEST FROM 1,1 TO 15,40 ACTIVATE WINDOW TEST @ 2,2 SAY "CUSTOMER #" GET CNO

@ 5,2 EDIT TEMP.CNO     SIZE 3.375,15.286 DEFAULT " " SCROLL

READ CYCLE

RELEASE WINDOW TEST

** The following procedure queries the Customer & Invoices tables, and then ** places the results back into the Temp table, overwriting the table ** with a new copy of Temp.

PROCEDURE QRYPROC SELECT CUSTOMER.CNO ;

   FROM CUSTOMER, INVOICES ;
   WHERE INVOICES.CNO = CUSTOMER.CNO ;
   INTO TABLE TEMP

**************************

Code to Work Around Behavior

To avoid overwriting the table, use memory variables instead of get fields linked to a specific table. The following program is a modified version of the previous program to demonstrate this workaround.

SELECT CUSTOMER.CNO ;

   FROM CUSTOMER, INVOICES ;
   WHERE INVOICES.CNO = CUSTOMER.CNO ;
   INTO TABLE TEMP

IF ! USED("INVOICES") && Use the Invoices table from tutorial directory

   USE INVOICES IN 0

IF ! USED("CUSTOMER") && Use the Customer table from tutorial directory

   USE CUSTOMER IN 0

IF ! USED("TEMP")

   USE TEMP IN 0

DEFINE WINDOW TEST FROM 1,1 TO 15,40 ACTIVATE WINDOW TEST @ 2,2 SAY "CUSTOMER #" GET CNO @ 5,2 EDIT M.CNO SIZE 3.375,15.286 DEFAULT " " SCROLL @ 10,2 GET MCHOICE FUNCTION '*N QUERY' DEFAULT 1 VALID QRYPROC()

READ CYCLE

RELEASE WINDOW TEST

** The following procedure queries the Customer & Invoices tables, and then ** places the results back into the Temp, essentially overwriting the table ** with a new copy of Temp.

PROCEDURE QRYPROC SELECT CUSTOMER.CNO ;

   FROM CUSTOMER, INVOICES ;
   WHERE INVOICES.CNO = CUSTOMER.CNO ;
   INTO TABLE TEMP