Trying to get Primary Key using RowUpdated but get System.DBNull instead

[Greg]

Hello.

I'm trying to get the primary key for a newly inserted row using
ADO.NET and SqlServer. The convention seems to be to do this with the
RowUpdated event, i.e.

_sqlConnectionToTickerTextDb.Open();
SqlCommand cmdGetPK = new SqlCommand("SELECT @@IDENTITY",
_sqlConnectionToTickerTextDb);
object pkObject = cmdGetPK.ExecuteScalar();
int pkNunber = Int32.Parse((pkObject).ToString());
e.Row[_tickerIDColumnName] = pkNunber;
e.Row.AcceptChanges();
_sqlConnectionToTickerTextDb.Close();
However, I am getting the value System.DBNull instead of the primary
key.
I''ve tried replacing @@IDENTITY with SCOPE_IDENTITY() but the same
happens.
Is there a setting that I need to alter in the database or am I
approaching this in the wrong way?

I've also noticed that most people dont use .Open and .Close() in
coding the above, yet my app complains if I don't.

Is there an easier more practical way of getting the new primary key
e.g. by looking at the attributes of the SqlRowUpdatedEventArgs
parameter?

Greg.

[Peter Bromberg [C# MVP]]

It looks from your code sample like you are trying to get @@IDENTITY
independently of the query or stored proc context it was produced in.
@@IDENTITY goes out of scope when the query or stored proce that inserts a
new record is done. So if you want to get this, you can either have the
@@IDENTITY Value be a return value from the sproc, or you can get it as a
value from ExecuteScalar with Select @@IDENTITY

Hope that helps.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Greg" wrote:

Hello.

I'm trying to get the primary key for a newly inserted row using
ADO.NET and SqlServer. The convention seems to be to do this with the
RowUpdated event, i.e.

_sqlConnectionToTickerTextDb.Open();
SqlCommand cmdGetPK = new SqlCommand("SELECT @@IDENTITY",
_sqlConnectionToTickerTextDb);
object pkObject = cmdGetPK.ExecuteScalar();
int pkNunber = Int32.Parse((pkObject).ToString());
e.Row[_tickerIDColumnName] = pkNunber;
e.Row.AcceptChanges();
_sqlConnectionToTickerTextDb.Close();
However, I am getting the value System.DBNull instead of the primary
key.
I''ve tried replacing @@IDENTITY with SCOPE_IDENTITY() but the same
happens.
Is there a setting that I need to alter in the database or am I
approaching this in the wrong way?

I've also noticed that most people dont use .Open and .Close() in
coding the above, yet my app complains if I don't.

Is there an easier more practical way of getting the new primary key
e.g. by looking at the attributes of the SqlRowUpdatedEventArgs
parameter?

Greg.

[Greg]

Thanks a lot Peter.

Slightly confused though - your second suggestion of "you can get it
as a
value from ExecuteScalar with Select @@IDENTITY" - I thought that this
is what I had done. What do I need to change?

Thanks for your help.

Greg.

[Peter Bromberg [C# MVP]]

Greg,
Let's make it even clearer, then:
This SQL that you supplied :

sqlConnectionToTickerTextDb.Open();
SqlCommand cmdGetPK = new SqlCommand("SELECT @@IDENTITY",
_sqlConnectionToTickerTextDb);
object pkObject = cmdGetPK.ExecuteScalar();
int pkNunber = Int32.Parse((pkObject).ToString());

--Appears not to insert any new row at all, therefore there is no @@IDENTITY
in the context to return. If it was a stored proc:

CREATE PROC InsertTickerText
@TickerText varchar(500)
AS
INSERT INTO myTickerText
(TickerText) VALUES (@TickerText)
SELECT @@IDENTITY
-- then you would have an @@ IDENTITY To return.

Hope that makes sense.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Greg" wrote:

Thanks a lot Peter.

Slightly confused though - your second suggestion of "you can get it
as a
value from ExecuteScalar with Select @@IDENTITY" - I thought that this
is what I had done. What do I need to change?

Thanks for your help.

Greg.

[Greg]

Great, thanks a lot!