ASP.NET return @@IDENTITY from SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ASP.NET return @@IDENTITY from SQL Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-14 : 10:45:26

Nick D writes "Hello

Ive just read your article on returning the newly created ID value back from sql server to asp (using .nextrecordset and @@IDENTITY).

I do that already and fine it preferable to using a stored proc because it allows you to change tables more easily etc.

How can I do the above in ASP.NET???

Any ideas - Im totally stuck and in a big hole, can the oracle shead any light? :)"

DryWater
Starting Member

10 Posts

Posted - 2002-05-14 : 11:02:33

Hi Nick,

What I've done is use an output parameter in my stored proc and output the SCOPE_IDENTITY() to my .NET application. Within the sproc I'll do my insert and then set a local variable to = SCOPE_IDENTITY(). Then within your app you have to define your SQL command as a stored procedure and then add the parameter direction as Output. Here's an example in VB that assumes the ID you're looking for is called siteID:

dim insCmd AS New SqlCommand("sp_insWs", objConn)
insCmd.CommandType = CommandType.StoredProcedure

dim siteIDP AS SqlParameter
siteIDP = insCmd.Parameters.Add("@siteID", SqlDbType.int)
siteIDP.Direction = ParameterDirection.Output

insCmd.ExecuteNonQuery()

You can then do whatever you need to with the value of the parameter by calling siteIDP.value

Hth,

])ry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources