FIX: SQLOLEDB: IRowset::SetData() Returns DB_E_ERRORSOCCURRED with Identity Column

ID: Q235332

The information in this article applies to:

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://support.microsoft.com/support/sql/.


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. <BR/>
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<CAccessor<CdboIdentTestTableAccessor> >
{
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<CAccessor<CdboIdentTestTableAccessor> >::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();


REFERENCES

Please see the following topics in MSDN online documentation:




For additional information about using an identity column with MSDASQL provider, please see the following KB article:

Article ID: Q194678 Title: "HOWTO: SQL Server Identity, OLE DB Templates and OLE DB for ODBC"

Additional query words: kbDSupport 


Keywords          : kbATL kbDatabase kbDTL kbOLEDB kbProvider kbSQLServ kbVC kbGrpVCDB kbGrpMDAC 
Version           : WINDOWS:7.0; winnt:6.0
Platform          : WINDOWS winnt 
Issue type        : kbbug

Last Reviewed: July 15, 1999