DOCUMENT:Q259492  23-AUG-2001  [visualc]
TITLE   :HOWTO:Use DBPROP_SERVERDATAONINSERT to Retrieve Identity Value
PRODUCT :Microsoft C Compiler
PROD/VER::2.0,2.1,2.5,2.6,3.0,6.0,7.0,7.01
OPER/SYS:
KEYWORDS:kbATL kbATL200 kbATL210 kbDTL kbOLEDB kbSQLServ kbVC kbATL300 kbConsumer kbGrpDSVCDB kb

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

 - The Microsoft Active Template Library (ATL), versions 2.0, 2.1, 3.0, used with:
    - Microsoft Visual C++, 32-bit Enterprise Edition, version 6.0 
    - Microsoft Visual C++, 32-bit Professional Edition, version 6.0 
    - Microsoft Visual C++, 32-bit Learning Edition, version 6.0 
 - Microsoft OLE DB Provider for SQL Server, versions 7.0, 7.01 
 - Microsoft Data Access Components versions 2.1, 2.5, 2.6 
-------------------------------------------------------------------------------

SUMMARY
=======

To retrieve the value of a newly inserted Identity field, set the Open rowset
DBPROP_SERVERDATAONINSERT property to True.

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

The DBPROP_SERVERDATAONINSERT property allows the provider to update the local
row cache as soon as the server commits the insert of the Identity field.

NOTE: The Microsoft OLE DB Programmer's Reference states that setting
DBPROP_SERVERDATAONINSERT is potentially expensive and may not be supported for
certain types of rowsets. You must have a Primary Key selected for the table
before using the DBPROP_SERVERDATAONINSERT property.

The following code demonstrates how to retrieve the value after you insert the
new row:

   #include "atldbcli.h"
   class CdboIdentAccessor
   {
   public:
   	LONG m_myident;
           DBSTATUS m_identstatus;
   	TCHAR m_name[11];
           DBSTATUS m_namestatus;

   BEGIN_COLUMN_MAP(CdboIdentAccessor)
   	COLUMN_ENTRY_STATUS(1, m_myident,m_identstatus)
   	COLUMN_ENTRY_STATUS(2, m_name,m_namestatus)
   END_COLUMN_MAP()

   DEFINE_COMMAND(CdboIdentAccessor, _T(" \ 
   	SELECT \ 
   		myident, \ 
   		name  \ 
   		FROM dbo.Ident"))
   	
   	void ClearRecord()
   	{
   		memset(this, 0, sizeof(*this));
   	}
   };

   class CdboIdent : public CCommand<CAccessor<CdboIdentAccessor> >
   {
   public:
   	HRESULT Open()
   	{
   		HRESULT		hr;

   		hr = OpenDataSource();
   		if (FAILED(hr))
   			return hr;

   		return OpenRowset();
   	}
   	HRESULT OpenDataSource()
   	{
   	HRESULT		hr;
   	  CDataSource db;
   	  CDBPropSet	dbinit(DBPROPSET_DBINIT);

   	  dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));
   	  dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("test"));
              dbinit.AddProperty(DBPROP_INIT_DATASOURCE,
    OLESTR("SERVER"));
   	  dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
   	  dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
   	  hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
   	  /hr = db.OpenWithServiceComponents(_T("SQLOLEDB.1"),
    &dbinit);
   	  if (FAILED(hr))
   	  return hr;

             return m_session.Open(db);
   	}
   	HRESULT OpenRowset()
   	{
   	  // Set properties for open
              // Notice the DBPROP_SERVERDATAONINSERT set to true
   	     CDBPropSet propset(DBPROPSET_ROWSET);
   	     propset.AddProperty(DBPROP_SERVERCURSOR, true );
   	     propset.AddProperty(DBPROP_SERVERDATAONINSERT, true );
   	     propset.AddProperty(DBPROP_IRowsetChange, true);
   	     propset.AddProperty(DBPROP_UPDATABILITY, 
                 DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT 
   |             DBPROPVAL_UP_DELETE );

             return CCommand<CAccessor<CdboIdentAccessor> 
   >::Open(m_session, NULL, &propset);
   	}
   	CSession	m_session;
   };

   int main(void)
   {
   	HRESULT hr;
   	CdboIdent test;

   	// Connect the database, session, and accessors
   	CoInitialize(NULL);
   	hr = test.Open();

   	if (FAILED(hr))
   	{
   		IErrorInfo* pErrInfo;
   		BSTR bstrDesc = NULL;
   		GetErrorInfo(0,&pErrInfo);
   		pErrInfo->GetDescription(&bstrDesc);
   		SysFreeString(bstrDesc);
   	}

   	test.ClearRecord();
      	strcpy ( test.m_name, "New");
      	test.m_identstatus = DBSTATUS_S_IGNORE;
      	hr = test.Insert(0,true);   // Insert new Row

   	test.GetData();            // Get the inserted row
            cout << test.m_myident << endl;  // Output the 
   value for Identity field
   	test.Close( );
   	return S_OK;
   }

REFERENCES
==========

For additional information, click the article numbers below to view the articles
in the Microsoft Knowledge Base:

   Q194678 HOWTO: SQL Server Identity, OLE DB Templates and OLE DB for ODBC

   Q219029 HOWTO: Retrieve Calculated Fields from SQL Server 7.0

For more information about the BPROP_SERVERDATAONINSERT property, please refer to
the Microsoft OLE DB Programmer's Reference.

Additional query words: autonumber add

======================================================================
Keywords          : kbATL kbATL200 kbATL210 kbDTL kbOLEDB kbSQLServ kbVC kbATL300 kbConsumer kbGrpDSVCDB kbGrpDSMDAC kbDSupport MSGRAPH kbMDAC250 kbMDAC260 
Technology        : kbVCsearch kbAudDeveloper kbATLsearch
Version           : :2.0,2.1,2.5,2.6,3.0,6.0,7.0,7.01
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.

Copyright Microsoft Corporation 2001.