Getting the ID of last inserted record using SQL Server database in asp.net

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

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, Advantages and Disadvantages of Querystring, Explain cookies in asp.net OR Explain cookies with example in asp.net and Example of Multiview control in asp.net .

In this example we getting the ID of last record with SCOPE_IDENTITY()without using output parameter.

Store procedure:

CREATE PROCEDURE Insert_Album

(

@albumname varchar(50),

@albumdescription varchar(100)

)

AS

BEGIN     

      SET NOCOUNT ON;

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

select SCOPE_IDENTITY()   

END

GO

Add a new webform to poject.

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);

            object obj = cmd.ExecuteScalar();

            Label1.Text = "Album has been created Successfully.ID=" + obj.ToString();

            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)

            Dim obj As Object = cmd.ExecuteScalar()

            Label1.Text = "Album has been created Successfully.ID=" + obj.ToString()

            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.