DOCUMENT:Q124998  27-AUG-1999  [odbc]
TITLE   :INF: Change in Behavior of SQLSetPOS on Empty Result Set
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER:WINDOWS:2.1
OPER/SYS:
KEYWORDS:

======================================================================
2.10
WINDOWS
kbprg

-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Open Database Connectivity, version 2.1 
-------------------------------------------------------------------------------

SUMMARY
=======

This article applies only to those drivers that support the ODBC Cursor API and
SQLSetPos.

The ODBC API Specification prior to version 2.1 did not allow calling SQLSetPos
with fOption=SQL_ADD after SQLExtendedFetch returned SQL_NO_DATA_FOUND. This
will cause a problem with the use of a dynaset in Microsoft Foundation Classes
(MFC) version 3.0.

When using dynasets and trying to add a record to a table following an query that
produced an empty recordset, you will receive the following error:

   SQLSTATE 24000 - Invalid Cursor State

In other words, if you open a dynaset CRecordset object and the query returns an
empty recordset, a subsequent AddNew()/Update() call will cause the error.

The ODBC Specification version 2.1 removes this restriction.

MORE INFORMATION
================

An application can use block and scrollable cursors on a result set by calling
SQLSetConnectOption to specify SQL_CURSOR_TYPE, SQL_ROWSET_SIZE,
SQL_CONCURRENCY, and (optionally) SQL_KEYSET_SIZE. Result sets are generated by
executing a SELECT statement or by other operations, such as calling catalog
functions.

After the appropriate cursor type is specified and a result set is generated, the
application can use SQLExtendedFetch to fetch a rowset; within a rowset, it can
use SQLSetPos to move the cursor to a specified row. An application can also use
SQLSetPos to insert, delete, or update rows in the rowset and to refresh the
rows in the rowset. If the result set is empty or if the end of the result set
has been reached, SQLExtendedFetch returns SQL_NO_DATA_FOUND.

In ODBC API Specification prior to version 2.1, an application was not allowed to
call SQLSetPos with fOption=SQL_ADD to insert a row after SQLExtendedFetch had
returned SQL_NO_DATA_FOUND. In other words, an application could not insert at
the end of a result set or into an empty result set.

Doing so would produce the invalid cursor state (SQLSTATE 24000) error returned
from SQLSetPos. This situation is encountered in MFC 3.0 when using dynasets. If
you open a dynaset Crecordset object and the query returns an empty recordset, a
subsequent AddNew()/Update() will call SQLSetPos as described above and cause
the error.

In ODBC API Specification version 2.1, this restriction is removed. As a result,
SQLSetPos with fOption=SQL_ADD can be called even if SQLExtendedFetch returns
SQL_NO_DATA_FOUND. However, the application still needs to call SQLExtendedFetch
before it calls SQLSetPos.

The Driver Manager and Cursor Library for ODBC API version 2.1 are available on
the Internet, via anonymous ftp to ftp.microsoft.com, cd to
developr/odbc/public. The file name is ODBC21.EXE.

Additional query words: 2.10 MFC ODBC CURSOR DYNASET Windows NT

======================================================================
Keywords          :  
Technology        : kbAudDeveloper kbODBCSearch kbODBC210
Version           : WINDOWS:2.1

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

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