CodeSnip: How to Get Id of the Record Using ASP.NET and SQL Server 2000
 
Published: 29 Jun 2006
Unedited - Community Contributed
Abstract
In this article Sushila demonstrates how to retrieve the ID of a newly added record using ASP.NET and SQL Server 2000. The author makes use of SCOPE_IDENTITY() function to achieve the functionality.
by Sushila Bowalekar Patel
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 65553/ 93

Introduction

In this code snippet we will examine how to get Id of the newly added record using ASP.NET and SQL Server 2000.

We come across cases where we have to get the Id for the newly inserted record.  The Id in our case is an IDENTITY field.  The retrieval of this Id can be done in a variety of ways.  In this article we will see one of the techniques on how we can achieve this retrieval.  We will use the TSQL function, SCOPE_IDENTITY(), that gives us the last identity value generated in the current scope of the current process.

Requirements

·         Visual Studio .NET 2003

·         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

VB.NET

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim strconnection, strsqlinsert As String
Dim Employeeid As String
Private Sub btnAddRecord_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnAddRecord.Click
  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
  cmd.Connection.Close()
  lblLastRecordAdded.Text = Employeeid
End Sub

C#

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.

Downloads

Summary

In the sample code we have achieved the functionality by using the SCOPE_IDENTITY() function. We can also use IDENT_CURRENT and @@IDENTITY as per our requirement.



User Comments

Title: thanks   
Name: vibhu singh
Date: 5/1/2007 4:20:09 AM
Comment:
thanks. i am a student. i was searching code for insert data in sql table. your site provide perfect solution for my search. thanks for this help.
Title: help   
Name: amit07
Date: 3/30/2007 9:58:25 AM
Comment:
hi,
can u plz clear the diff b/w scope_identity() and @@identity
more.
Title: Excellent   
Name: From Iran
Date: 3/28/2007 5:36:09 AM
Comment:
This Tutorial was one of the rare clear ones on the NET excellent job
Title: Thanks   
Name: Hooman
Date: 3/28/2007 5:13:04 AM
Comment:
Thank you, You Made my Day
Title: Thanks Thanks Thanks Thanks Thanks   
Name: Desperate
Date: 2/22/2007 10:28:39 PM
Comment:
Man, you have no idea, you saved my job!
Title: Thanks   
Name: Swinkster
Date: 2/13/2007 9:17:43 AM
Comment:
Thank you! Well written example.
Title: a very good one...   
Name: Genie
Date: 12/26/2006 9:58:10 PM
Comment:
Thanks a lot...
i've been searching for this the whole day, but now i got it :)
Title: Very Good Code   
Name: M.Anwar Iqbal Khan
Date: 12/3/2006 7:13:54 AM
Comment:
That kind of code i am looking for in internet, very good code. Thanks.
Title: Muchas Gracias   
Name: Peter
Date: 11/28/2006 3:29:33 AM
Comment:
Thanks, just what i needed!
Title: great article   
Name: cake
Date: 9/27/2006 10:14:38 AM
Comment:
thanks alot!
Title: thanks   
Name: surya bhan
Date: 9/27/2006 4:03:50 AM
Comment:
very good
Title: Thanks   
Name: Dave London
Date: 8/21/2006 12:45:40 PM
Comment:
Good article. Until recently I wasn't aware of the difference myself. Your solution is the one I would recommend as @@indentity is not thread safe and it is better practice to use SCOPE_IDENTITY() just in case triggers are added to the table at a later stage.
Title: Good snippit   
Name: toffer
Date: 8/10/2006 5:34:26 AM
Comment:
great article... exactly what i need ... :)
Title: Thanks   
Name: TonyM
Date: 8/8/2006 4:50:49 PM
Comment:
Excellent Example. Thanks for the snippets, explanations and example. I searched high and low for a good example, glad I found this one!
Title: Thank you!   
Name: terryh
Date: 7/28/2006 9:25:26 PM
Comment:
Finally, exactly what I needed. Why is this stuff so hard to find?...oh well...






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 7/31/2014 5:26:16 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search