Getting the ID of last inserted record in asp.net using output parameter

Introduction: In this article today I am going to explain the how we can get the ID of last inserted record using SQl Server database in asp.net using output parameter

Description:

 In the previous article I have explained Date format in Sql Server, How to reset or clear all web form controls in asp.net, Getting the ID of last inserted record using SQL Server database in asp.net, Explain cookies in asp.net OR Explain cookies with example in asp.net and Example of Multiview control in asp.net .

In this example i getting the ID of last record with SCOPE_IDENTITY() using output parameter. In below given store procedure I am using output parameter @id.

Store procedure:

CREATE PROCEDURE Insert_Album

(

@albumname varchar(50),

@albumdescription varchar(100),

@id int output

)

AS

BEGIN

     

      SET NOCOUNT ON;

Insert into dbo.ALBUM values(@albumname,@albumdescription)

set @id = SCOPE_IDENTITY()

return @id   

END

HTML Markup:

<table>

        <tr>

            <td align=right>

                Album Name:</td>

            <td>

                <asp:TextBox ID="txtAlbumName" runat="server"></asp:TextBox>

            </td>

        </tr>

          <tr>

            <td align=right>

                Album Description:</td>

            <td>

                <asp:TextBox ID="txtdescription" runat="server"></asp:TextBox>

            </td>

        </tr>

        <tr>

            <td colspan="2" align="center">

                <asp:Button ID="btnCreate" runat="server" onclick="btnCreate_Click"

                    Text="Create" />

                <asp:Label ID="Label1" runat="server" Text=""></asp:Label>

            </td>

        </tr>

    </table>

On button click the below given code (C#):

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void btnCreate_Click(object sender, EventArgs e)

    {

        try

        {

            SqlCommand cmd = new SqlCommand("Insert_Album", con);

            con.Open();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@albumname", txtAlbumName.Text);

            cmd.Parameters.AddWithValue("@albumdescription", txtdescription.Text);

            cmd.Parameters.Add("@id", SqlDbType.Int);

            cmd.Parameters["@id"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            string id = (string)cmd.Parameters["@id"].Value.ToString();

            Label1.Text = "Album has been created Successfully and ID number is=" + id;

            con.Close();

            cmd.Dispose();

        }

        catch (Exception ex)

        {

        }

    }

VB:

Imports System.Data.SqlClient

Imports System.Data

Imports System.Configuration

Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())

    Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreate.Click

        Try

Dim cmd As New SqlCommand("Insert_Album", con)

            con.Open()

            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.AddWithValue("@albumname", txtAlbumName.Text)

            cmd.Parameters.AddWithValue("@albumdescription", txtdescription.Text)

            cmd.Parameters.Add("@id", SqlDbType.Int)

            cmd.Parameters("@id").Direction = ParameterDirection.Output

            cmd.ExecuteNonQuery()

            Dim id As String = DirectCast(cmd.Parameters("@id").Value.ToString(), String)

            Label1.Text = Convert.ToString("Album has been created Successfully and ID number is=") & id

            con.Close()

            cmd.Dispose()

        Catch ex As Exception

        End Try

    End Sub

 Is this article helpful for you?

If yes post your comment to appreciate my work and fell free to contact me. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.