Microsoft KB Archive/120474

{| = PRB: Error "No Table is in Use" with Screen Based on SQL =
 * width="100%"|

ID: Q120474

The information in this article applies to:


 * 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.

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

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
 * }