Schreibzugriff auf DAtenbank mittels OLEDB

[AndyDD]

Ich habe mir folgendes Beispiel vorgenommen:
http://www.codeproject.com/KB/database/oledbconsumer2.aspx

Soweit funktioniert auch das ganze. Ich habe dann die Daten aus der Access-Datei auf meinen MS SQL-Server 2005 Express transferiert und die Accessor-Klasse wie folgt angepasst:

// Books.H : Declaration of the CBooks class

#ifndef __BOOKS_H_
#define __BOOKS_H_

class CBooksAccessor
{
public:
	TCHAR m_AuthorFirstName[51];
	TCHAR m_AuthorLastName[51];
	TCHAR m_Category[51];
	LONG m_ID;
	TCHAR m_price[51];
	TCHAR m_Title[51];

BEGIN_ACCESSOR_MAP(CBooksAccessor, 3)
	BEGIN_ACCESSOR(0, true) /// accessor is the default auto accessor
		COLUMN_ENTRY(1, m_ID)
		COLUMN_ENTRY(2, m_Title)
		COLUMN_ENTRY(3, m_AuthorFirstName)
		COLUMN_ENTRY(4, m_AuthorLastName)
		COLUMN_ENTRY(5, m_Category)
		COLUMN_ENTRY(6, m_price)
	END_ACCESSOR()
	BEGIN_ACCESSOR(1, false)
		COLUMN_ENTRY(1, m_AuthorFirstName)
		COLUMN_ENTRY(2, m_AuthorLastName)
	END_ACCESSOR()
	BEGIN_ACCESSOR(2, false)
		COLUMN_ENTRY(1, m_Category)
	END_ACCESSOR()
END_ACCESSOR_MAP()

	// 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 CBooks : public CTable<CAccessor<CBooksAccessor> >
{
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_CACHE_AUTHINFO, true);
		dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);
		dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);
		dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("blabla"));
		dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));
		//dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("testbooks.mdb"));
		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MYSERVER\\MYDB"));
		dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("Test_OLEDB"));
		dbinit.AddProperty(DBPROP_INIT_MODE, (long)19);
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
		dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
		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 CTable<CAccessor<CBooksAccessor> >::Open(m_session, _T("Books"), &propset);
	}
	CSession	m_session;
};

#endif // __BOOKS_H_

Das Problem dabei ist, dass ich nur die Daten lesen kann. Beim Versuch ein Delete, Insert oder Update liefert mir hResult in der entsprechenden Zugriffsmethode DB_E_ERRORSOCCURRED. Ich bin da schon auf einige KB-Artikel gestoßen, aber meist trafen die Annahmen auf meinen Fall nicht zu.
Hat jemand eine Idee, wo ich suchen muss?

[Martin Richter]

Hast Du auch den Rowset geöffnet? Hast Du auf einen Datensatz navigiert?

Zeige mal etwas mehr Code als diese Definition...

[AndyDD]

Martin Richter schrieb:

Hast Du auch den Rowset geöffnet? Hast Du auf einen Datensatz navigiert?

Zeige mal etwas mehr Code als diese Definition...

Also das Basisbeispiel griff auf eine Access-Datei zu. Ich habe nur den Accessor umgeschrieben, damit ich auf die SQL-Express-Instanz zugreifen kann. Deshabl denke ich, dass es nur damit was zu tun hat. Hier nochmal die Zugriffsmethoden:

void COLEDBConsumer2Dlg::MoveNext() 
{
	// TODO: Add your control notification handler code here

	if( m_bCommand == true )
	{
		CommandMoveNext();
		return;
	}

	HRESULT hResult = m_Books.MoveNext();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Unable to move any further forwards" ) );
	}

	CopyData();
}

void COLEDBConsumer2Dlg::MovePrev() 
{
	// TODO: Add your control notification handler code here

	if( m_bCommand == true )
	{
		CommandMovePrev();
		return;
	}

	HRESULT hResult = m_Books.MovePrev();
	if( FAILED( hResult ) )
	{
		if( hResult = -2147217879 ) /// rowset cannot scroll backwards
		{
			AfxMessageBox( _T( "This rowset cannopt scroll backwards" ) );
		}
		else
		{
			AfxMessageBox( _T( "Unable to move any further backwards" ) );
		}
	}

	CopyData();
}

void COLEDBConsumer2Dlg::Update() 
{
	// TODO: Add your control notification handler code here

	if( m_bCommand == true )
	{
		CommandUpdate();
		return;
	}

	UpdateData( TRUE );
	SaveData();

    HRESULT hResult = m_Books.SetData();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Unable to save the rowset" ) );
	}

}

void COLEDBConsumer2Dlg::Delete() 
{
	// TODO: Add your control notification handler code here
	if( m_bCommand == true )
	{
		CommandDelete();
		return;
	}

	HRESULT hResult = m_Books.Delete();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Unable to delete the record" ) );
	}
	else
	{
		hResult = m_Books.MoveNext();
		if( hResult != S_OK )
		{
			m_Books.MoveFirst();
		}

		CopyData();
	}
}

void COLEDBConsumer2Dlg::CopyData()
{
	char szBuffer[ 25 ];

	if( m_bCommand == true )
	{
		CommandCopyData();
		return;
	}

	m_strAuthorFirstName = m_Books.m_AuthorFirstName;
	m_strAuthorLastName = m_Books.m_AuthorLastName;
	m_strCategory = m_Books.m_Category;
	m_strID = ltoa( m_Books.m_ID, szBuffer, 10 );
	m_strPrice = m_Books.m_price;
	m_strTitle = m_Books.m_Title;

	UpdateData( FALSE );
}

void COLEDBConsumer2Dlg::SaveData()
{

	if( m_bCommand == true )
	{
		CommandSaveData();
		return;
	}

	_tcscpy( m_Books.m_AuthorFirstName, m_strAuthorFirstName );

	_tcscpy( m_Books.m_AuthorLastName, m_strAuthorLastName );

	_tcscpy( m_Books.m_Category, m_strCategory );

	m_Books.m_ID = atol( m_strID.GetBuffer( 0 ) );

	_tcscpy( m_Books.m_price, m_strPrice );

	_tcscpy( m_Books.m_Title, m_strTitle );
}

/// First example of using more than one accessor to access the data in a table
void COLEDBConsumer2Dlg::ViewAuthors()
{
	if( m_bCommand == true )
	{
		CommandViewAuthors();
		return;
	}

	CAccessorDlg dlg;

	dlg.m_strHeaderOne = _T( "Authors First Name" );

	dlg.m_strHeaderTwo = _T( "Authors Last Name" );

	/// GET THE AUTHOR INFORMATION FROM THE ACCESSOR
	m_Books.GetData( 1 );

	m_Books.MoveFirst();

	/// Although this is scrolling through the m_Books
	/// table the call to get data selects the second 
	/// accessor in the table
	do
	{
		CData *pData = new CData();

		pData->m_strFieldOne = m_Books.m_AuthorFirstName;
		pData->m_strFieldTwo = m_Books.m_AuthorLastName;

		dlg.m_DataList.AddTail( pData );

	}
	while( m_Books.MoveNext() == S_OK );

	dlg.DoModal();

	/// retrieve the original data
	/// we've gone past the end of the rowset so a call to GetData
	/// here will fail so reset the position first
	m_Books.MoveFirst();
	m_Books.GetData( 0 ); /// change the current accessor
	m_Books.MoveFirst();
}

/// Second example of using custom accessors in a table
/// see ViewAuthors for notes
void COLEDBConsumer2Dlg::ViewCategories()
{
	if( m_bCommand == true )
	{
		CommandViewCategories();
		return;
	}

	CAccessorDlg dlg;

	dlg.m_strHeaderOne = _T( "Categories" );

	m_Books.GetData( 2 );

	m_Books.MoveFirst();

	do
	{
		CData *pData = new CData();

		pData->m_strFieldOne = m_Books.m_Category;

		dlg.m_DataList.AddTail( pData );

	}
	while( m_Books.MoveNext() == S_OK );

	dlg.DoModal();

	m_Books.MoveFirst();
	m_Books.GetData( 0 );
	m_Books.MoveFirst();
}

/// Insert a new item into the database
void COLEDBConsumer2Dlg::Insert()
{
	if( m_bCommand == true )
	{
		CommandInsert();
		return;
	}

	UpdateData( TRUE );

	/// Note that the id is an incremental count therefore,
	/// if we try to insert with a value that already exists in the id
	/// it will error
	m_Books.MoveLast();
	int nCount = m_Books.m_ID;
	nCount++;
	SaveData();

	m_Books.m_ID = nCount;

	HRESULT hResult = m_Books.Insert(); /// specify the insert accessor
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Error inserting the current record" ) );
		return;
	}

	m_Books.GetData( 0 );
	m_Books.MoveFirst();

	CopyData();

}

/// Switch between a command accessor and a table accessor
void COLEDBConsumer2Dlg::OnCommandcheck() 
{
	// TODO: Add your control notification handler code here
	if( m_bCommand == true )
	{
		m_bCommand = false;
		m_Books.MoveFirst();
		CopyData();
	}
	else
	{
		m_bCommand = true;
		m_CommandBooks.MoveFirst();
		CommandCopyData();
	}
}

/// Copy data for the command accessor
void COLEDBConsumer2Dlg::CommandCopyData()
{
	char szBuffer[ 25 ];

	m_strAuthorFirstName = m_CommandBooks.m_AuthorFirstName;
	m_strAuthorLastName = m_CommandBooks.m_AuthorLastName;
	m_strCategory = m_CommandBooks.m_Category;
	m_strID = ltoa( m_CommandBooks.m_ID, szBuffer, 10 );
	m_strPrice = m_CommandBooks.m_price;
	m_strTitle = m_CommandBooks.m_Title;

	UpdateData( FALSE );
}

/// save data for the command accessor
void COLEDBConsumer2Dlg::CommandSaveData()
{
	_tcscpy( m_CommandBooks.m_AuthorFirstName, m_strAuthorFirstName );

	_tcscpy( m_CommandBooks.m_AuthorLastName, m_strAuthorLastName );

	_tcscpy( m_CommandBooks.m_Category, m_strCategory );

	m_CommandBooks.m_ID = atol( m_strID.GetBuffer( 0 ) );

	_tcscpy( m_CommandBooks.m_price, m_strPrice );

	_tcscpy( m_CommandBooks.m_Title, m_strTitle );
}

/// move next for the command accessor
void COLEDBConsumer2Dlg::CommandMoveNext()
{
	HRESULT hResult = m_CommandBooks.MoveNext();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "The command accessor has reached the end of the rowset" ) );
		return;
	}

	CommandCopyData();
}

/// move prev for the command accessor
void COLEDBConsumer2Dlg::CommandMovePrev()
{
	HRESULT hResult = m_CommandBooks.MovePrev();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "The command accessor cannot move any further back" ) );
		return;
	}

	CommandCopyData();
}

/// Update for the command accessor
void COLEDBConsumer2Dlg::CommandUpdate()
{
	UpdateData( TRUE );
	CommandSaveData();

    HRESULT hResult = m_CommandBooks.SetData();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Unable to save the rowset" ) );
	}
}

/// delete for the command accessor
void COLEDBConsumer2Dlg::CommandDelete()
{
	HRESULT hResult = m_CommandBooks.Delete();
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Unable to delete the record" ) );
	}
	else
	{
		hResult = m_CommandBooks.MoveNext();
		if( hResult != S_OK )
		{
			m_CommandBooks.MoveFirst();
		}

		CommandCopyData();
	}
}

/// insert for the command accessor
void COLEDBConsumer2Dlg::CommandInsert()
{
	UpdateData( TRUE );

	/// Note that the id is an incremental count therefore,
	/// if we try to insert with a value that already exists in the id
	/// it will error
	m_CommandBooks.MoveLast();
	int nCount = m_CommandBooks.m_ID;
	nCount++;
	CommandSaveData();

	m_CommandBooks.m_ID = nCount;

	HRESULT hResult = m_CommandBooks.Insert(); /// specify the insert accessor
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Error inserting the current record" ) );
		return;
	}

	m_CommandBooks.GetData( 0 );
	m_CommandBooks.MoveFirst();

	CopyData();
}

/// viewcatergories for the command accessor
void COLEDBConsumer2Dlg::CommandViewCategories()
{
	CAccessorDlg dlg;

	dlg.m_strHeaderOne = _T( "Categories" );

	CComBSTR bstrQuery( _T( "SELECT distinct Category FROM Books" ) );
	HRESULT hResult = m_CommandBooks.Query( bstrQuery.m_str );
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Error running the query" ) );
		return;
	}

	CComBSTR bstrTemp;
	do
	{
		CData *pData = new CData();

		bstrTemp = static_cast< BSTR >( m_CommandBooks.m_QueryCommand.GetValue( 1 ) ); 
		pData->m_strFieldOne = bstrTemp.m_str;

		dlg.m_DataList.AddTail( pData );

	}
	while( m_CommandBooks.m_QueryCommand.MoveNext() == S_OK );

	dlg.DoModal();

	m_CommandBooks.m_QueryCommand.Close();

	m_CommandBooks.GetData( 0 );
	m_CommandBooks.MoveFirst();

}

/// view authors for the command accessor
void COLEDBConsumer2Dlg::CommandViewAuthors()
{

	CAccessorDlg dlg;

	dlg.m_strHeaderOne = _T( "Author's First Name" );
	dlg.m_strHeaderTwo = _T( "Author's Last Name" );

	CComBSTR bstrQuery( _T( "SELECT distinct Books.[Author Last Name], Books.[Author First Name]  FROM BOOKS" ) );
	HRESULT hResult = m_CommandBooks.Query( bstrQuery.m_str );
	if( FAILED( hResult ) )
	{
		AfxMessageBox( _T( "Error querying for the authors names" ) );
		return;
	}

	CComBSTR bstrTemp;
	do
	{
		CData *pData = new CData();

		bstrTemp = static_cast< BSTR >( m_CommandBooks.m_QueryCommand.GetValue( 2 ) );
		pData->m_strFieldOne = bstrTemp.m_str;
		bstrTemp = static_cast< BSTR >( m_CommandBooks.m_QueryCommand.GetValue( 1 ) );
		pData->m_strFieldTwo = bstrTemp.m_str;

		dlg.m_DataList.AddTail( pData );
	}
	while( m_CommandBooks.m_QueryCommand.MoveNext() == S_OK );

	dlg.DoModal();

	m_CommandBooks.m_QueryCommand.Close();

	m_CommandBooks.GetData();
	m_CommandBooks.MoveFirst();
}