Getting the ID of last inserted record in asp.net using output parameter - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

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 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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon