Knowledge Base

The SELECT-SQL COUNT field function ignores the SET DELETED ON command in Visual FoxPro 5.0

Article ID: 166137

Article Last Modified on 3/17/2005


APPLIES TO


This article was previously published under Q166137

SYMPTOMS

In Microsoft Visual FoxPro 5.0, theSELECT-SQL COUNT field function does not work correctly when SET DELETED is ON. It returns the count of all records including the deleted records instead of just the non-deleted records. However, the SELECT-SQL COUNT field function works correctly in Visual FoxPro 3.x.

RESOLUTION

Use one of the following commands:
  • SELECT COUNT(*) FROM <table name> WHERE !DELETED()				
  • COUNT TO <memvar>				
However, using the COUNT TO <memvar> moves the record pointer while using the SQL-SELECT statement does not.

STATUS

This problem does not occur in Visual FoxPro 6.0 and later.

MORE INFORMATION

Steps to reproduce the behavior

Run the following code in a program file.
   CLEAR
   SET DELETED ON
   CREATE CURSOR Test ( cTest C(10))
   INSERT INTO Test VALUES ( "One")
   INSERT INTO Test VALUES ( "Two")
   DELETE

   LOCAL aRet[1]
   SELECT COUNT(*) FROM Test INTO ARRAY aRet
   ?aRet[1]       && Incorrectly returns 2

   SELECT COUNT(cTest) FROM Test INTO ARRAY aRet
   ?aRet[1]       && Incorrectly returns 2

   SELECT cTest FROM Test INTO CURSOR Temp
   ?_TALLY        && Incorrectly returns 2

   SELECT COUNT(cTest) FROM Test WHERE !DELETED() INTO CURSOR Temp
   ?_TALLY        && Correctly returns 1

   COUNT TO aRet
   ?aRet          && Correctly returns 1

   CLOSE ALL
				

Keywords: kbprb KB166137