Sql Server SCOPE_IDENTITY()

Hello, I'm currenly in the process of writing a Database abstraction class
that will support MySql, MS-SQL and OLEDB. In the abstract base class, I
have a public read only property LastAutoID UInt64 deffined. The field
behind this property is updated every time the end program calls the
ExecuteNonQuery() method (directly or indirectly).

My problem is that the SqlServer specific class is failing to retrive the
auto id, for some reason the query keeps returning DB null when I try to
populate the auto id field, forcing me to set 0. So far I've used the Sql
Server Profiler utility to confirm the fact that indeed, when I execute the
SCOPE_IDENTITY query it is on the same connection as the INSERT query (which
makes sense as by default my class disables Connection Pooling and enables a
Singleton pattern, never closes a connection unless requested by the end
programmer and reference count on the connection buffer is 0).

Now, for the wierd part... If I use the @@IDENTITY method in the query, I
get the auto number. Now, here's everything else that might be important.

First, after doing some reasearch I found that it is adventagous to cast the
result of the SCOPE_IDENTITY method as an int, so I tried that and casting
it as a bigint with no results.

Next, I realized that in my testing I was not specificlly naming at table, I
was just opening a connection and specifying the table using
<database>..<table>, so I switched over to connecting specifically to the
database and not specifying the database in the insert query, however that
produced no better results.

I've also posted this question to
microsoft.public.sqlserver.programming and gotten good advice, however
nothing that helps.


.

Relevant Pages

  • Re: Ada DB bindings and APQ
    ... >>I didn't like the use of handles for database objects. ... > will ensure that the connection will not go away until at least one query ... > things which can be made with a connection. ... That is different to ODBC. ...
    (comp.lang.ada)
  • RE: Posible to speed up query update with Apllication.screenupdati
    ... Don't change anything on the database side; the only question is the most ... You create a connection by sending the connect string to the ODBC driver. ... (multiple queries). ... You now execute the query (there are several ways in ADO but I usually ...
    (microsoft.public.excel.programming)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... It got past looking for pub.mdb, and looks like it tried to run the query ... I also tried putting the whole thing in the connection string: ... The settings for the Merant ODBC driver for ... default connect string means a jet database. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Word 2003 Closes upon update of sort order of table using Acce
    ... data connection file created to the database folder and created a dummy user ... connection is much faster and seems far more stable. ... incorporate OLE DB, they probably found themselves with a lot more problems ... embed a table or query name. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
Quantcast