Hi! First post here, so please excuse me if I get any posting protocol wrong.

Running 2K(SP3) with Sql 2K(SP3a). Porting a single user application that was running against Access using ADO/OLEDB using the Microsoft.JET.OLEDB.4.0 provider. As far as we know, database-wise the application and ADO were working without a hitch.

Started porting the application's install program to use SQL server using 'sqloledb' as a provider. With a small amount of work, have all of the tables and stored procedures ported over, and using Query Analyzer and Enterprise Manager, everything seems great.

However (this is a question after all), we seem to have problems with the primary table, defined as such...


CREATE TABLE Objects (
ObjectId int IDENTITY( 1, 1 ) NOT NULL PRIMARY KEY
IsDeleted bit not null
DateCreated date not null default('getdate()')
)


Now, when I go to enter the "default state" objects into the database, I do the following sequence:

get object ready in memory
go to Objects table and fetch next id
set id into the Objects descended object
save the object

In order to grab the next id, I am trying to do the following (inside of a big try block, omitted for clarity) assuming m_pConnection is the connection object, already opened and verified:


// Allocate pointers
ADODB::_RecordsetPtr resultRecordSet = NULL;
ADODB::_CommandPtr localCommand = NULL;

// Create instances of the object
HRESULT hr = resultRecordSet.CreateInstance(__uuidof(ADODB::Rec ordset));
hr = localCommand.CreateInstance(__uuidof( ADODB::Command ));

// Set up the insert and execute it.
localCommand->ActiveConnection = m_pConnection;
localCommand->CommandText = "INSERT INTO Objects ( IsDeleted ) VALUES ( 0 );";
m_pConnection->Errors->Clear();
resultRecordSet = localCommand->Execute( NULL, NULL, ADODB::adCmdText );

// Set up the request to get the result
localCommand->CommandText = "SELECT SCOPE_IDENTITY() as col1;";
resultRecordSet = localCommand->Execute( NULL, NULL, ADODB::adCmdText );

// Get the results.
ADODB::FieldsPtr recordFields = resultRecordSet->GetFields();
ADODB::FieldPtr thisField = recordFields->GetItem( "col1" );
ADODB::DataTypeEnum dataType = thisField->GetType();
if( !resultRecordSet->EOF ) {
_variant_t fldVal = thisField->GetValue();
retCode = thisField->GetValue();
}


Now, when I do this, I get one of two behaviours. Most of the time, the result set thinks its at the EOF, and no records are in the result set. Every so often, it gets through, but fldVal is set to VT_NULL, which doesn't help.

I have tried the above 2 queries in sequence in Query Analyzer, and it works great. We also tried an approach with a ResultSet object bound to a AdoRecordBinding object, and the AddNew method worked fine, but any attempt to grab the object using Update, Refresh or Resync failed.

Any ideas? Things to check? Service packs that I need to install?