"Carlo Razzeto" <crazz***@hotmail.com> wrote in message
news:%23ynFWlpMGHA.916@TK2MSFTNGP10.phx.gbl...
> 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.
>

"Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message
news:ucMYgqpMGHA.3960@TK2MSFTNGP09.phx.gbl...
> Well, first of all, inventing your own connection pooling mechanism is a
> bad design for multiple reasons. They are written down in various places
> in my book, or search my blog for "Global SqlConnection is bad".
>
> Secondly, it's a bit difficult to comment without looking at your actual
> code. From the very little I know of your code, I'd recommend trying
> replacing ExecuteNonQuery with ExecuteScalar.
> Also, do you have any triggers defined on the table? What else depends on
> that table at the database level?
>
> - Sahil Malik [MVP]
> ADO.NET 2.0 book -
> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
> __________________________________________________________
>
>
> "Carlo Razzeto" <crazz***@hotmail.com> wrote in message
> news:%23ynFWlpMGHA.916@TK2MSFTNGP10.phx.gbl...
>> 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.
>>
>
>

"Carlo Razzeto" <crazz***@hotmail.com> wrote in message
news:%234B2ocqMGHA.516@TK2MSFTNGP15.phx.gbl...
> No triggers or anything like that, just a table with an auto-identity. I
> chose to seceratly populate the field when the user calls
> ExecuteNonQuery() because I beleive that this is when it most likely a new
> auto id will be generated. Internally right now I use ExecuteReader ATM,
> but execute scalar would work as well. If you are interested in looking at
> the code here is a link.
>
> http://euclid.nmu.edu/~crazzeto/code/DBProvider/
>
> "Sahil Malik [MVP C#]" <contactmethrumyblog@nospam.com> wrote in message
> news:ucMYgqpMGHA.3960@TK2MSFTNGP09.phx.gbl...
>> Well, first of all, inventing your own connection pooling mechanism is a
>> bad design for multiple reasons. They are written down in various places
>> in my book, or search my blog for "Global SqlConnection is bad".
>>
>> Secondly, it's a bit difficult to comment without looking at your actual
>> code. From the very little I know of your code, I'd recommend trying
>> replacing ExecuteNonQuery with ExecuteScalar.
>> Also, do you have any triggers defined on the table? What else depends on
>> that table at the database level?
>>
>> - Sahil Malik [MVP]
>> ADO.NET 2.0 book -
>> http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
>> __________________________________________________________
>>
>>
>> "Carlo Razzeto" <crazz***@hotmail.com> wrote in message
>> news:%23ynFWlpMGHA.916@TK2MSFTNGP10.phx.gbl...
>>> 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.
>>>
>>
>>
>
>

"Carlo Razzeto" wrote:

> 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.
>
>
>

"Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@comcast.netNoSpamM> wrote
in message news:379B07A8-04A3-45CF-B018-339763775F0F@microsoft.com...
> SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST, yes
> MUST, be called in the same call batch. This means you cannot
> ExecuteNonQuery() and come back and get the identity value.
>
> You CAN use @@IDENTITY, but prayer is good when you head this route, as
> someone else could come in between your call and your asking for identity
> value, unless you wrap it in a transaction that locks the table. If you
> think
> this one through, locking is a bad idea for a number of reasons.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> ***************************
> Think Outside the Box!
> ***************************
>
>
> "Carlo Razzeto" wrote:
>
>> 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.
>>
>>
>>

>
>
>> SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST,
>> yes MUST, be called in the same call batch. This means you cannot
>> ExecuteNonQuery() and come back and get the identity value.
>
>     Maybe I missed something.  The following code works for me w/ SQL
>Server 2000:
>
>using (SqlConnection cn = new SqlConnection(strConn))
>{
>    cn.Open();
>    SqlCommand cmd = cn.CreateCommand();
>    cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) " +
>                        " VALUES ('ALFKI', GetDate())";
>    cmd.ExecuteNonQuery();
>
>    cmd.CommandText = "SELECT SCOPE_IDENTITY()";
>    Console.WriteLine(cmd.ExecuteScalar());
>}
>
>     I hope this information proves helpful.
>
>David Sceppa
>Microsoft
>This posting is provided "AS IS" with no warranties,
>and confers no rights.  You assume all risk for your use.
>© 2006 Microsoft Corporation.  All rights reserved.
David,

news:CH2De60NGHA.668@TK2MSFTNGXA01.phx.gbl...
>
>
>> SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST,
>> yes MUST, be called in the same call batch. This means you cannot
>> ExecuteNonQuery() and come back and get the identity value.
>
>     Maybe I missed something.  The following code works for me w/ SQL
> Server 2000:
>
> using (SqlConnection cn = new SqlConnection(strConn))
> {
> cn.Open();
> SqlCommand cmd = cn.CreateCommand();
> cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) " +
>                        " VALUES ('ALFKI', GetDate())";
> cmd.ExecuteNonQuery();
>
> cmd.CommandText = "SELECT SCOPE_IDENTITY()";
> Console.WriteLine(cmd.ExecuteScalar());
> }
>
>     I hope this information proves helpful.
>
> David Sceppa
> Microsoft
> This posting is provided "AS IS" with no warranties,
> and confers no rights.  You assume all risk for your use.
> © 2006 Microsoft Corporation.  All rights reserved.
>

>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.
>