Admin

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_dat abase"
' 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.