In this code sample we will take a look at how to use SCOPE_IDENTITY()  in ASP.NET. We use this function to get new ID of inserted record from SQL database.


Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim strConnection, strSQL As String
Dim newUserID As String

    ' Database Connection
  strConnection = "server=LOCAL;uid=sa;password=;database=sample_database"
    ' Our SQL String that will insert a new record to database table
  strSQL = "Insert into member_list ( "
  strSQL + = "userName ,passWord"
  strSQL + = ")"
  strSQL + = " values ("
  strSQL + = "@uName,@pName"
  strSQL + = ")"
    ' Here this part of SQL code will take the latest identity
  strSQL + = "; SELECT SCOPE_IDENTITY() ; "
  objConnection = New SqlConnection(strConnection)
  objCommand = New SqlCommand(strSQL, objConnection)
  objCommand.Parameters.Add("@uName", SqlDbType.NVarChar).Value = "some text here or a variable"
  objCommand.Parameters.Add("@pWord", SqlDbType.NVarChar).Value = "some text here or a variable"
  objCommand.Connection.Open()
  newUserID = objCommand.ExecuteScalar
  objCommand.Connection.Close()


SCOPE_IDENTITY takes only latest records identity only in current scope, but if you use @@IDENTITY will retrurn identity in all scopes.

discuss this topic to forum