Return of Autonumber in .NET & SQL Server

[otherside]

Hello guys,

 

I've had this problem before with Access databases and i never bothered to solve because there isn't a real solution with Access databases. Now i'm using SQL server and i know that it can be done but i don't know how.

 

I have a table with one autonumer column for indexing (identity). When i execute the INSERT command i need to get back the number that was assigned. Keep in mind that this is a fast multiuser database (can be 100 inserts/minute) so a SELECT MAX() thing would never work.

 

I'm not familiar with stored procedures etc. If someone could supply me with an example of how this is done through a VB.NET application it would be great.

 

Thanks

[PlausiblyDamp]

SQL provide a SCOPE_IDENTITY() function for just such a purpose.

[otherside]

Thanks for the reply

 

Any examples ?

[otherside]

OK, here is an example in case someone needs it in the future.

 

Create a stored procedure like this :

CREATE PROCEDURE InsertName
 @NewName nvarchar(50),
 @Identity int OUTPUT

AS
INSERT INTO Names (FName) VALUES(@NewName)
SET @Identity = SCOPE_IDENTITY()
GO

 

on the application part:

 

Dim Con as new SqlConnection("connectionstring")
Dim SqlCmd as new SqlCommand("InsertName",Con)
SqlCmd.CommandType = CommandType.StoredProcedure
SqlCmd.Parameters.AddWithValue("@NewName", "TestName")
Dim parm As SqlParameter = cmd.Parameters.Add("@Identity", SqlDbType.Int, 0, "IDColumn")
parm.Direction = ParameterDirection.Output

Con.Open()

SqlCmd.ExecuteNonQuery()

Dim ReturnID As Integer = SqlCmd.Parameters.Item("@Identity").Value

 

I think it's pretty easy to understand, if anyone need any clarifications post here.

 

Thanks