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


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
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
Modification Type:MajorLast Reviewed:3/17/2005
Keywords:kbprb KB166137
©2004 Microsoft Corporation. All rights reserved.