CodeSnip: How to Get Id of the Record Using ASP.NET and SQL Server 2000

Listing 1 – Simple WebForm used for the Test

<form id="Form1" method="post" runat="server">

<p>First Name<asp:TextBox id="FirstName" runat="server"></asp:TextBox></p>

<p>Last Name<asp:TextBox id="LastName" runat="server"></asp:TextBox></p>

<p><asp:Button id="btnAddRecord" runat="server" Text="Add"></asp:Button></p>

<p><asp:Label id="lblLastRecordAdded" runat="server"></asp:Label></p>

</form>

We will create a database named DummyDB and a table with the name Employees with Employeeid, FirstName and LastName as column names under the database.  The required SQL Script is given below for reference

Listing 2 - Table Creation Script

CREATE TABLE Employees (Employeeid int IDENTITY (1, 1) NOT NULL, FirstName nvarchar (50), LastName nvarchar (50))

Now that we have the table in place, we will see how to get the Id of newly added record through the code.  We will use the SCOPE_IDENTITY() function for our requirement.  After inserting the new record we simply return the value, as shown below.

Listing 3 – Use SCOPE_IDENTITY()

-- INSERT the new record

            INSERT INTO Employees(FirstName, LastName)

            VALUES(@FirstName, @LastName)

            -- Now return the EmployeeId of the newly inserted record

            SELECT SCOPE_IDENTITY()

The ExecuteScalar() method can be used in ASP.NET since it returns the first column of the first row in the result set returned by the query.

Listing 4 – Use ExecuteScalar()

Below is the code in VB.NET and C#.

Code

string strconnection, strsqlinsert;
SqlConnection conn;
SqlCommand cmd;
string Employeeid;
private void btnAddRecord_Click(object sender, System.EventArgs e)
{
  strconnection = "server=localhost;uid=sa;password=;database=DummyDB";
  strsqlinsert = "Insert into Employees ( ";
  strsqlinsert += "FirstName ,LastName";
  strsqlinsert += ")";
  strsqlinsert += " values (";
  strsqlinsert += "@FirstName,@LastName";
  strsqlinsert += ")";
  strsqlinsert += "; SELECT SCOPE_IDENTITY() ; ";
  conn = new SqlConnection(strconnection);
  cmd = new SqlCommand(strsqlinsert, conn);
  cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstName.Text;
  cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastName.Text;
  cmd.Connection.Open();
  Employeeid = cmd.ExecuteScalar().ToString();
  cmd.Connection.Close();
  lblLastRecordAdded.Text = Employeeid;
}

Figure 1

NOTE

Although SCOPE_IDENTITY and @@IDENTITY return the last identity value generated for any table in the current session, SCOPE_IDENTITY returns value only in current scope whereas @@IDENTITY returns value across all scopes.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.