ADO using sqloledb refusing to cooperate against SqlServer

[wyldsider]

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::Recordset));
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 in out application with the ResultSet AddNew method, and we seem to have a similar problem getting the identity back out.

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

thanks,
wyldsider

Jack De Winter
Software Developer
MedTel Software

http://www.medtel.on.ca