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