Microsoft KB Archive/235332

= FIX: SQLOLEDB: IRowset::SetData Returns DB_E_ERRORSOCCURRED with Identity Column =

Article ID: 235332

Article Last Modified on 5/5/2006

-

APPLIES TO


 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft Visual C++ 6.0 Enterprise Edition
 * Microsoft Visual C++ 6.0 Professional Edition
 * Microsoft Visual C++ 6.0 Standard Edition
 * Microsoft Data Access Components 2.1 Service Pack 2

-



This article was previously published under Q235332



SYMPTOMS
When using the SQL Server OLE DB provider and OLE DB consumer Templates with a SQL Server database, calling CRowset::SetData on the CCommand object to update a record returns DB_ERRORSOCCURRED when the table contains an identity column and the status for the identity column is set to DBSTATUS_S_IGNORE prior to calling SetData. The description information for the error is "Errors Occurred" no additional error information is available.



CAUSE
This is a bug in SQL Server OLE DB provider version 07.00.623



RESOLUTION
SQLOLEDB provider version 07.01.069x included with SQL 7.0 SP1 fixes the problem.

Alternatively, create multiple accessors and perform GetData calls using an accessor with the identity column to read the record and update using another accessor which doesn't include the identity column.



STATUS
Microsoft has confirmed this to be a problem in SQL Server OLEDB provider version 7.00.623. This problem has been corrected in SQLOLEDB provider version 07.01.069x included with U.S. Service Pack 1 for Microsoft SQL Server version 7.0.

For information about downloading and installing the latest SQL Server Service Pack, see http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b.



MORE INFORMATION
The following sample code demonstrates the problem with Visual C++ 6.0 OLE DB Consumer Templates. First create the table using the following SQL statements:

Sample Code
-- Create the following table in SQL Server database CREATE TABLE [dbo].[identtesttable] (   [id] [int] IDENTITY (1, 1) NOT NULL,    [test] [char] (10) NULL ) go insert into [dbo].[identtesttable] values ("Test") go

Here is the ATL code which demonstrates the problem:

/* Use the ATL database wizard to generate a command and accessor for the above table which has an identity column.  Modify the wizard generated accessor to bind field status as well as data.

Use the COLUMN_ENTRY_STATUS macro instead of the COLUMN_ENTRY macro. class CdboIdentTestTableAccessor { public: TCHAR m_id[11]; TCHAR m_test[11]; DBSTATUS m_idStatus; DBSTATUS m_TestStatus; BEGIN_COLUMN_MAP(CdboIdentTestTableAccessor) COLUMN_ENTRY_STATUS(1,m_id,m_idStatus) COLUMN_ENTRY_STATUS(2, m_test,m_TestStatus) END_COLUMN_MAP

DEFINE_COMMAND(CdboIdentTestTableAccessor,                       _T("SELECT id, test FROM dbo.IdentTestTable"))

// You may wish to call this function if you are inserting a record and wish to   // initialize all the fields, if you are not going to explicitly set all of them. void ClearRecord {       memset(this, 0, sizeof(*this)); } };

class CdboIdentTestTable : public CCommand > { 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_PERSIST_SENSITIVE_AUTHINFO, false);     dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));   dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("yourdatabase"));    dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("yourserver"));   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);

hr = db.Open(_T("SQLOLEDB.1"), &dbinit); if (FAILED(hr)) return hr;

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

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

// Test Code, attempt to update the rowset by calling CRowset::SetData on the CCommand object

CdboIdentTestTable rs;

HRESULT hr = rs.Open; hr = rs.MoveNext;

// Update the test field strcpy(rs.m_test,"test3"); rs.m_idStatus = DBSTATUS_S_IGNORE; hr=rs.SetData; // --->> will return DB_ERRORSOCCURRED

// insert a new reoord rs.ClearRecord; strcpy ( rs.m_test, "New"); rs.m_idStatus = DBSTATUS_S_IGNORE; hr=rs.Insert;

rs.Close;

