Q166137: PRB: SELECT-SQL COUNT() Field Function Ignores SET DELETED ON

Article: Q166137
Product(s): Microsoft FoxPro
Version(s): WINDOWS:5.0,5.0a
Operating System(s): 
Keyword(s): kbvfp
Last Modified: 20-OCT-2000

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

Additional query words:

======================================================================
Keywords          : kbvfp 
Technology        : kbVFPsearch kbAudDeveloper kbVFP500 kbVFP500a
Version           : WINDOWS:5.0,5.0a
Issue type        : kbprb

=============================================================================

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.

Copyright Microsoft Corporation 1986-2002.