getting value at time of insert?

[Guest]

I'm performing a simple one row INSERT of some data into a table but need to
get that rows first column fields' value, its the first column which is the
Indentiy and Indentity Seed. If this is not in a SP (I'm using Sql Server 2k)
how can I accomplish this with a normal asp.net INSERT call
(.ExecuteNonQuery()). Returning that value back?
Column name = SurveyResponseHeaderID
of the row that was just inserted.

Thanx.

 

[Guest]

Chris,

One way that you can do this is by batching your Sql Statements. For example

INSERT INTO Test (MyColumn) VALUES (1);SELECT @@Identity

The use of the semi-colon allows you to "batch" the statements together and
the "SELECT @@Identity" calles the Sql Function/Global Variable to get the
identity value that you just inserted. Now if you execute this with
ExecuteScalar you will be able to access the identity that was just inserted.

I hope this helps.
-----------------------------

 

[Guest]

thanx! this must be a new addition to ado.net, I don't think ado batches like
this.

How do I access that new value?

thanx!

 

[Guest]

thanx figured it out!

 

[Miha Markic [MVP C#]]

Hi,

Actually you should use SELECT SCOPE_IDENTITY() instead of SELECT
@@IDENTITY.

 

[Guest]

Hi Miha,

I am glad that you brought this point up. I think that depends on exactly
what the application needs to do. If you want to return the last identity
without regard to scope but in the same session you should use @@IDENTITY.
If you want to return the last identity with regard to scope use
SCOPE_IDENTITY() and if you want to return the last identity without regard
to either use IDENT_CURRENT.

It has been my experience that a lot of the time other options to retreive
an identity value get overlooked and this is a great place to make others
aware that the options exist.

Thanks again.

 

[Guest]

what's the difference?

Hi,

Actually you should use SELECT SCOPE_IDENTITY() instead of SELECT
@@IDENTITY.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Chris,

One way that you can do this is by batching your Sql Statements. For
example

INSERT INTO Test (MyColumn) VALUES (1);SELECT @@Identity

The use of the semi-colon allows you to "batch" the statements together
and
the "SELECT @@Identity" calles the Sql Function/Global Variable to get the
identity value that you just inserted. Now if you execute this with
ExecuteScalar you will be able to access the identity that was just
inserted.

I hope this helps.
-----------------------------

 

[Guest]

Hi Chris,

Take a look at my previous post for the differences. I breifly explain the
differences there. If you need more specific examples please reply to this
post and I will try to help you out.

Hope this helps.