Unanswered: ADO usage woes against SQLServer with identity column
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:
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?