Introduction:
Here I will explain how to get newly or last inserted record id from sql server database (table) in asp.net using ado.net with c#, vb.net. By @@IDENTITY or SCOPE_IDENTITY() or IDENT_CURRENT we can get last or latest inserted record id from sql server table in asp.net using c#, vb.net.
Description:
In previous articles I explained SQL Server Query to get last inserted record id, display gridview row details in popup window on hyperlink click in asp.net, jQuery show gridview row details in modal popup in asp.net, ajax modalpopupextender to edit gridview row details, gridview examples in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to get last or latest inserted record id from database using ado.net with example in asp.net using c#, vb.net.
In previous articles I explained SQL Server Query to get last inserted record id, display gridview row details in popup window on hyperlink click in asp.net, jQuery show gridview row details in modal popup in asp.net, ajax modalpopupextender to edit gridview row details, gridview examples in asp.net and many articles relating to gridview, asp.net, c#,vb.net and jQuery. Now I will explain how to get last or latest inserted record id from database using ado.net with example in asp.net using c#, vb.net.
Before
implement this example first design one table productinfo in your database as
shown below
Column Name
|
Data Type
|
Allow Nulls
|
productid
|
Int(IDENTITY=TRUE)
|
Yes
|
productname
|
varchar(50)
|
Yes
|
price
|
varchar(50)
|
Yes
|
Once
table created in database enter some dummy data to test application.
If
we want to get last inserted record we have multiple options check below
queries
The
table which is having Identity Property from that table we can get the last
inserted record id will be like this
SELECT
@@IDENTITY
SELECT
SCOPE_IDENTITY()
SELECT
IDENT_CURRENT('TableName')
|
Another
way we can get it in
SELECT
MAX(productId) FROM
productinfo
|
Other
way
SELECT
TOP 1 productid FROM
productinfo ORDER BY
productid DESC
|
Here
I am going to use @@IDENTITY property now
create following stored procedure in your database
CREATE PROCEDURE
InsertProductInfo
@productname varchar(50),
@price INT,
@productid INT
OUTPUT
AS
BEGIN
INSERT INTO
productinfo (productname,price) VALUES(@productname,@price)
SET @productid= @@IDENTITY
RETURN @productid
END
|
Now
open your aspx page and write the code like as shown below
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Get Last (Newly)
Inserted Record Id from SQL Server Table in Asp.net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida
Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif;
color: #303933;}
.headerstyle
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color:
#df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1"
runat="server">
<div>
<table>
<tr>
<td>Product Name:</td>
<td><asp:TextBox ID="txtName"
runat="server"/></td>
</tr>
<tr>
<td>Price:</td>
<td><asp:TextBox ID="txtPrice"
runat="server"/></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnSubmit"
Text="Submit"
runat="server"
onclick="btnSubmit_Click"
/></td>
</tr>
</table>
</div>
<div class="GridviewDiv">
<asp:GridView runat="server"
ID="gvDetails"
AllowPaging="true"
PageSize="10"
AutoGenerateColumns="false"
Width="420px"
OnPageIndexChanging="gvDetails_PageIndexChanging">
<HeaderStyle CssClass="headerstyle"
/>
<Columns>
<asp:BoundField DataField="productid"
HeaderText="Product
Id" />
<asp:BoundField DataField="productname"
HeaderText="Product
Name" />
<asp:BoundField DataField="price"
HeaderText="Price"
/>
</Columns>
</asp:GridView>
</div>
<asp:Label ID="lblResult"
runat="server"
Font-Bold="true"/>
</form>
</body>
</html>
|
After completion of aspx page add following namespaces in
codebehind
C#
Code
using System;
using
System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
|
After completion of adding namespaces you need to write the
code like as shown below
protected void Page_Load(object
sender, EventArgs e)
{
if (!IsPostBack)
BindGridview();
}
// Insert New Record
protected void btnSubmit_Click(object
sender, EventArgs e)
{
using (SqlConnection con = new
SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("InsertProductInfo", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@productname",
txtName.Text);
cmd.Parameters.AddWithValue("@price",
txtPrice.Text);
cmd.Parameters.Add("@productid",
SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
string result =
cmd.Parameters["@productid"].Value.ToString();
con.Close();
lblResult.Text = "Data Inserted
Successfully. <br/> New ProductId = " + result;
txtName.Text = "";
txtPrice.Text = "";
BindGridview();
}
}
// Get ProductInfo Details
protected void BindGridview()
{
DataSet ds = new DataSet();
using (SqlConnection con = new
SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select * from productinfo", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs
e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
|
VB.NET
Code
Imports
System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal
sender As Object,
ByVal e As EventArgs) Handles
Me.Load
If Not
IsPostBack Then
BindGridview()
End If
End Sub
' Insert New Record
Protected Sub btnSubmit_Click(ByVal
sender As Object,
ByVal e As EventArgs)
Using con As New SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("InsertProductInfo",
con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@productname",
txtName.Text)
cmd.Parameters.AddWithValue("@price",
txtPrice.Text)
cmd.Parameters.Add("@productid",
SqlDbType.Int).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim result As String =
cmd.Parameters("@productid").Value.ToString()
con.Close()
lblResult.Text = Convert.ToString("Data Inserted Successfully. <br/> New
ProductId = ") & result
txtName.Text = ""
txtPrice.Text = ""
BindGridview()
End Using
End Sub
' Get ProductInfo Details
Protected Sub BindGridview()
Dim ds As New DataSet()
Using con As New SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select
* from productinfo", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds)
con.Close()
gvDetails.DataSource = ds
gvDetails.DataBind()
End Using
End Sub
Protected Sub gvDetails_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
gvDetails.PageIndex = e.NewPageIndex
BindGridview()
End Sub
End Class
|
Demo
 |
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
1 comments :
nice site
Note: Only a member of this blog may post a comment.