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

ID: Q120474

The information in this article applies to:

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
***** This first section creates a table called Temp that gets overwritten.

CLOSE DATABASES SET SAFETY OFF

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

   USE INVOICES IN 0
ENDIF

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

   USE CUSTOMER IN 0
ENDIF

IF ! USED("TEMP") 

   USE TEMP IN 0
ENDIF

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
@ 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 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
SELECT CUSTOMER SHOW GETS RETURN

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

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
CLOSE DATABASES SET SAFETY OFF

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

   USE INVOICES IN 0
ENDIF

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

   USE CUSTOMER IN 0
ENDIF

IF ! USED("TEMP") 

   USE TEMP IN 0
ENDIF SCATTER MEMVAR SELECT CUSTOMER

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
SCATTER MEMVAR SELECT CUSTOMER SHOW GETS RETURN

Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b 2.50c 2.60 2.60a KBCategory: kbenv kbprg kbprb kbcode KBSubcategory: FxenvMemory

Last Reviewed: June 27, 1995