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. If it returns a valid result set, 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?