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