Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to get last inserted record ID from SQL Server table in ASP.NET

Author: Alanna Kremer
Download Source Code : 1370_GetLatestInsertedRecord.zip

Latest inserted record ID from SQL Server table can be retrieved with different ways. This article will explain how we can get last inserted record ID in ASP.NET.

 

We can create Identity column in SQL Server database table and can set it to auto increment. It means we don’t need to add identity value, it will be added by SQL Server automatically. So when we have inserted record to SQL Server database table, we might need to get identity value of this inserted record. There are three different ways to get last insert record at this time. These are:
 
IDENT_CURRENT(‘TableName’)
@@IDENTITY
SCOPE_ IDENTITY ()

You have insert statement before getting identity values using these methods. All these methods are same because all these return latest inserted identity value but there are some differences among these methods.

 

IDENT_CURRENT(‘TableName’)
 
It returns the latest identity value for the specified table without considering any scope or session. It only considers specified table and returns the latest inserted record ID. It is only limited to that table regardless of any connection, scope or session.
 
@@IDENTITY
 
It only considers current session to retrieve latest identity value. It will not consider session or connection and hence it will also retrieve the latest inserted identity value created from any trigger or user defined function.
 
SCOPE_ IDENTITY ()
 
It also returns the latest inserted identity value in the current session but it also considers current scope. It means it will not return value created from any trigger or user defined function that is not in the current scope.
 
Using MAX(TableName)
 
If you need latest identity value not at the same time when we have inserted the record then we can use MAX(TableName) to get latest value in case of integer.
 
Let’s start our example to get latest inserted identity value. You can download source code. I have created a database with the name of Sample and a table with the name Person in my SQL Server for this example. Table Person has five columns (ID, LastName, FirstName, City and State). Column ID is set as identity column with auto increment set as ‘Yes’.
 
 
  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add code below in your Web Form

     
    <table>
        <tr>
            <td>
                Last Name:
            </td>
            <td>
                <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                First Name:
            </td>
            <td>
                <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                City:
            </td>
            <td>
                <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                State:
            </td>
            <td>
                <asp:TextBox ID="txtState" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <asp:Button ID="btnInsert" runat="server" Text="Insert Record and Retrive ID"
        onclick="btnInsert_Click" />
    <br />
    <asp:Button ID="btnLatestID" runat="server" Text="Get Latest Inserted ID"
        onclick="btnLatestID_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server" ></asp:Label>
    <br />
    <asp:Label ID="lblLatestID" runat="server" ></asp:Label>

     
  6. Write a Stored Procedure to insert record and get latest insert ID.

    CREATE PROCEDURE dbo.InsertRecord
    (
    @ID int OUTPUT,
    @LastName nvarchar(50),
    @FirstName nvarchar(50),
    @City nvarchar(30),
    @State nvarchar(10)
    )
    AS
    INSERT Person
           (
           LastName,
           FirstName,
           City,
           State
           )
    VALUES
           (
           @LastName,
           @FirstName,
           @City,
           @State
           )
    SET @ID = IDENT_CURRENT('Person')
    RETURN @ID
     
    You can use @@IDENTITY or SCOPE_IDENTITY() instead of IDENT_CURRENT according to your requirements.
     
  7. Open code behind file and include following namespace.

    C#
     
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.Data
    Imports System.Data.SqlClient
     
  8. Write below function in code behind file.

    C#

    public int InsertRecord(string lastname, string firstname, string city, string state)
    {
        string constring = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand("InsertRecord", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname;
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname;
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city;
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state;
        int id = 0;
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            id = Convert.ToInt32(cmd.Parameters["@ID"].Value);
        }
        catch (Exception ex)
        {
            lblMessage.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
        return id;
    }
     
    VB.NET
     
    Public Function InsertRecord(ByVal lastname As String, ByVal firstname As String, ByVal city As String, ByVal state As String) As Integer
        Dim constring As String = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand("InsertRecord", conn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastname
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = firstname
        cmd.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city
        cmd.Parameters.Add("@State", SqlDbType.NVarChar, 10).Value = state
        Dim id As Integer = 0
        Try
            conn.Open()
            cmd.ExecuteNonQuery()
            id = Convert.ToInt32(cmd.Parameters("@ID").Value)
        Catch ex As Exception
            lblMessage.Text = ex.Message
        Finally
            conn.Close()
        End Try
        Return id
    End Function
     
    InsertRecord function takes four parameters for four columns and identity column will be incremented automatically. Connect to your server and create an instance of SqlCommand and provide stored procedure and connection object as parameter. Add parameters and theirs values for stored procedure. Add ID parameter and set its direction also. Get parameter value and return this value.
     
  9. Now write code below in btnInsert Click event

    C#
     
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        string lastname = txtLastName.Text;
        string firstname = txtFirstName.Text;
        string city = txtCity.Text;
        string state = txtState.Text;
     
        int id = InsertRecord(lastname, firstname, city, state);
        lblMessage.Text = "Record for a person is inserted successfully with an ID of '" + id + "'";
    }
     
    VB.NET
     
    Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnInsert.Click
        Dim lastname As String = txtLastName.Text
        Dim firstname As String = txtFirstName.Text
        Dim city As String = txtCity.Text
        Dim state As String = txtState.Text
     
        Dim id As Integer = InsertRecord(lastname, firstname, city, state)
        lblMessage.Text = "Record for a person is inserted successfully with an ID of '" + id + "'"
    End Sub
     
    Get TextBox values and pass to the InsertRecord() method and get latest inserted records ID.
     
  10. Write code below in btnLatestID click to get latest id using MAX(TableName)

    C#
     
    protected void btnLatestID_Click(object sender, EventArgs e)
    {
        string query = "SELECT MAX(ID) FROM Person";
        string constring = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True";
        SqlConnection conn = new SqlConnection(constring);
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.CommandType = CommandType.Text;
     
        int id = 0;
        try
        {
            conn.Open();
            id = Convert.ToInt32(cmd.ExecuteScalar());
            lblLatestID.Text = "Latest inserted ID is: " + id.ToString();
        }
        catch (Exception ex)
        {
            lblLatestID.Text = ex.Message;
        }
        finally
        {
            conn.Close();
        }
    }
     
    VB.NET
     
    Protected Sub btnLatestID_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLatestID.Click
        Dim query As String = "SELECT MAX(ID) FROM Person"
        Dim constring As String = "Data Source=Local;Initial Catalog=Sample;Integrated Security=True"
        Dim conn As New SqlConnection(constring)
        Dim cmd As New SqlCommand(query, conn)
        cmd.CommandType = CommandType.Text
     
        Dim id As Integer = 0
        Try
            conn.Open()
            id = Convert.ToInt32(cmd.ExecuteScalar())
            lblLatestID.Text = "Latest inserted ID is: " + id.ToString()
        Catch ex As Exception
            lblLatestID.Text = ex.Message
        Finally
            conn.Close()
        End Try
    End Sub
     
    Write a query to get latest ID using MAX(ID). Call ExecuteScaler() method and get the value.
     
  11. Press F5, insert values and see result.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to find controls in Header and Footer Template of Repeater control in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net