Q259492: HOWTO:Use DBPROP_SERVERDATAONINSERT to Retrieve Identity Value

Article: Q259492
Product(s): Microsoft C Compiler
Version(s): 2.0,2.1,2.5,2.6,3.0,6.0,7.0,7.01
Operating System(s): 
Keyword(s): kbATL kbATL200 kbATL210 kbDTL kbOLEDB kbSQLServ kbVC kbATL300 kbConsumer kbGrpDSVCDB kb
Last Modified: 23-AUG-2001

-------------------------------------------------------------------------------
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 1986-2002.