Return of Autonumber in .NET & SQL Server

otherside

otherside

Well-known member
Joined
Mar 16, 2003
Messages
127
Location
UK - Greece
Hello guys,

Ive had this problem before with Access databases and i never bothered to solve because there isnt a real solution with Access databases. Now im using SQL server and i know that it can be done but i dont 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.

Im 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
 
OK, here is an example in case someone needs it in the future.

Create a stored procedure like this :
Code: 
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:

Code: 
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 its pretty easy to understand, if anyone need any clarifications post here.

Thanks
 
Back
Top