PRB: SELECT-SQL COUNT0 Field Function Ignores SET DELETED ON

 Last reviewed: April 2, 1997
Article ID: Q166137
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, versions 5.0, 5.0a

SYMPTOMS

In Visual Foxpro, the 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 SQL COUNT field function is working correctly in Visual FoxPro 3.x.

RESOLUTION

Use one of the following commands: 

   SELECT COUNT(*) FROM <table name> WHERE !DELETED()

   -or-

   COUNT TO <memvar>
However, using the COUNT TO <memvar> moves the record pointer while using the SELECT statement does not.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce 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 : FxprgSql vfoxwin
Version : 5.0 5.0a
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.