Microsoft KB Archive/259492

= How To Use DBPROP_SERVERDATAONINSERT to Retrieve the Identity Value of a New Row =

Article ID: 259492

Article Last Modified on 7/13/2004

-

APPLIES TO

 Microsoft ActiveX Template Library 2.0, when used with:  Microsoft Visual C++ 6.0 Enterprise Edition

 Microsoft Visual C++ 6.0 Professional Edition

 Microsoft Visual C++ 6.0 Standard Edition  Microsoft ActiveX Template Library 2.1, when used with:  Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li> Microsoft ActiveX Template Library 3.0, when used with:  Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li> Microsoft OLE DB Provider for SQL Server 7.0</li> Microsoft OLE DB Provider for SQL Server 7.01</li> Microsoft Data Access Components 2.1</li> <li>Microsoft Data Access Components 2.5</li> <li>Microsoft Data Access Components 2.6</li></ul>

-

<div class="notice_section">

This article was previously published under Q259492

<div class="summary_section">

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

<div class="moreinformation_section">

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:

class CdboIdentAccessor { public: LONG m_myident; DBSTATUS m_identstatus; TCHAR m_name[11]; DBSTATUS m_namestatus;
 * 1) include &quot;atldbcli.h&quot;

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(&quot; \ SELECT \ myident, \ name \ FROM dbo.Ident&quot;)) 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(&quot;sa&quot;)); dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR(&quot;test&quot;)); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR(&quot;SERVER&quot;)); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); hr = db.Open(_T(&quot;SQLOLEDB.1&quot;), &dbinit); /hr = db.OpenWithServiceComponents(_T(&quot;SQLOLEDB.1&quot;), &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, &quot;New&quot;); 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; }

<div class="references_section">