HOWTO: Retrieving Calculated Fields from SQL Server 7.0 (219029)

MORE INFORMATION

The following code assumes that a connection has been made using the SQL Server native provider, and that Initialize() has been called on the IDBInitialze interface.

NOTE: DBPROP_SERVERDATAONINSERT only applies to server side cursors, and will not function with disconnected recordsets.

struct MyBuffer
{
	int f1;
	DWORD dwStatus;
	char f2[21];
};

void MyInsert()
{
IDBCreateCommand * pIDBCreateCommand;
ICommandText * pICommandText;
ICommandProperties * pICommandProperties;
IRowsetChange * pIRowsetChange;
IAccessor * pIAccessor1;
HACCESSOR hAccessor1;
const ULONG nCmdProps = 4;
ULONG cCmdPropertySets = 1;
DBPROP CmdProperties[ nCmdProps ];
DBPROPSET rgCmdPropSet;
MyBuffer Buffer;
DBBINDSTATUS DBBindStatus[2];
DBBINDING DBBindings[] =
{
	{
		1,
		offsetof( MyBuffer, f1 ),
		0,
		offsetof( MyBuffer, dwStatus ),
		NULL,
		NULL,
		NULL,
		DBPART_VALUE | DBPART_STATUS,
		DBMEMOWNER_CLIENTOWNED,
		DBPARAMIO_NOTPARAM,
		4,
		0,
		DBTYPE_I4,
		0,
		0
	},
	{
		2,
		offsetof( MyBuffer, f2 ),
		0,

		0,
		NULL,
		NULL,
		NULL,
		DBPART_VALUE,
		DBMEMOWNER_CLIENTOWNED,
		DBPARAMIO_NOTPARAM,
		21,
		0,
		DBTYPE_STR,
		0,
		0
	}
};

	CmdProperties[ 0 ].dwPropertyID = DBPROP_SERVERDATAONINSERT;
	CmdProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 0 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 0 ].colid = DB_NULLID;
	CmdProperties[ 0 ].vValue.vt = VT_BOOL;
	CmdProperties[ 0 ].vValue.iVal = VARIANT_TRUE;

	CmdProperties[ 1 ].dwPropertyID = DBPROP_IRowsetChange;
	CmdProperties[ 1 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 1 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 1 ].colid = DB_NULLID;
	CmdProperties[ 1 ].vValue.vt = VT_BOOL;
	CmdProperties[ 1 ].vValue.boolVal = VARIANT_TRUE;

	CmdProperties[ 2 ].dwPropertyID = DBPROP_CANFETCHBACKWARDS;
	CmdProperties[ 2 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 2 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 2 ].colid = DB_NULLID;
	CmdProperties[ 2 ].vValue.vt = VT_BOOL;
	CmdProperties[ 2 ].vValue.boolVal = VARIANT_TRUE;

	CmdProperties[ 3 ].dwPropertyID = DBPROP_SERVERCURSOR;
	CmdProperties[ 3 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 3 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 3 ].colid = DB_NULLID;
	CmdProperties[ 3 ].vValue.vt = VT_BOOL;
	CmdProperties[ 3 ].vValue.boolVal = VARIANT_TRUE;

	rgCmdPropSet.guidPropertySet = DBPROPSET_ROWSET;
	rgCmdPropSet.cProperties = nCmdProps;
	rgCmdPropSet.rgProperties = CmdProperties;

	pIDBCreateCommand->CreateCommand( NULL,
		IID_ICommandText,
		( IUnknown ** ) & pICommandText );

	pICommandText->SetCommandText( DBGUID_DBSQL, wSQLString );

	pICommandText->QueryInterface( IID_ICommandProperties,
		( void ** ) & pICommandProperties );

	pICommandProperties->SetProperties( cCmdPropertySets, & rgCmdPropSet );

	pICommandText->Execute( NULL, IID_IRowset, NULL,
		& cRowsAffected, ( IUnknown ** ) & pIRowset );

	pIRowset->QueryInterface( IID_IRowsetChange, ( void ** ) & IRowsetChange );

	pIRowset->QueryInterface( IID_IAccessor, ( void ** ) & pIAccessor1 );

	pIAccessor1->CreateAccessor( DBACCESSOR_ROWDATA, 
		2, 
		DBBindings, 
		sizeof( MyBuffer ), 
		& hAccessor1, 
		DBBindStatus );

	strcpy( Buffer.f2, "test test test" );

	pIRowsetChange->InsertRow( DB_NULL_HCHAPTER,
		hAccessor1,
		& Buffer,
		& hRow );

	pIRowset->GetData( hRow, hAccessor1, & Buffer );
}

				

In Microsoft ActiveX Data Objects (ADO) the code would be as follows:

Note You must change uid=<username> and pwd=<strong password> to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.ConnectionString = 
"Provider=SQLOLEDB;Server=rrbman;Database=testdb;uid=<username>;pwd=<strong password>;"
cn.Open

rs.ActiveConnection = cn
rs.Properties("Server Data On Insert").Value = True
rs.Open "SELECT * FROM x", , adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields(1) = "Test"
rs.Update

Debug.Print rs.Fields(0)

rs.Close
cn.Close


The table x is defined as;

create table x
(
f1 integer identity primary key,
f2 varchar(20)

)