How To Use DBPROP_SERVERDATAONINSERT to Retrieve the Identity Value of a New Row (259492)

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