Get Last Inserted Record in SQL
Sometimes we need to get or return the Last inserted record in SQL Server, using ASP.NET platform. Check out the code snippet for fetching the last inserted record from a database.
Hello everybody,
Once again I am back with another article in ASP.NET, SQL Server and simple Program.
Sometime ago, I was creating an Application for my own practice and I need to return Record that is Last inserted in Database. That was Voucher ID exactly.
I have tried too much and finally got a Simple solution. Which is described in many blogs and many Examples but was never easy to get.Requirement:
Visual Studio
SQL Server
Backend : C# or VBDesign Part
I have designed following design.
Code for this design:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Returning Last Inserted Records in SQL using ASP.NET</title>
</head>
<body>
<form id="form1" runat="server">
<table width="60%">
<tr>
<td align="center">
<h1>Insert Records</h1>
</td>
</tr>
<tr>
<td>
<table>
<tr>
<td>Roll Number :
<asp:TextBox ID="txtRollNo" runat="server" Width="120px"></asp:TextBox>
</td>
<td>First Name :
<asp:TextBox ID="txtFirstName" runat="server" Width="150px"></asp:TextBox></td>
<td>Last Name :
<asp:TextBox ID="txtLastName" runat="server" Width="150px"></asp:TextBox></td>
<td>Home Address :
<asp:TextBox ID="txtHomeAdd" runat="server" Width="150px"></asp:TextBox></td>
<td valign="bottom">
<asp:Button ID="btnSubmit" runat="server" Text="Add and Return" OnClick="btnSubmit_Click" /></td>
</tr>
<tr>
<td colspan="5" align="center">
<h1>Returned Values with ID</h1>
</td>
</tr>
<tr>
<td colspan="5"> </td>
</tr>
<tr>
<td>
<asp:Label ID="lblRecordID" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblRollNo" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblFirstName" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblLastName" runat="server"></asp:Label></td>
<td>
<asp:Label ID="lblHomeAdd" runat="server"></asp:Label></td>
</tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>
Backend Code:
SqlConnection con = new SqlConnection("Data Source=.\\sqldb;database=test;integrated security=true");
protected void btnSubmit_Click(object sender, EventArgs e)
{
string query = "insert into student output inserted.* values (@RollNo,@FirstName,@LastName,@HomeAdd)";
SqlDataAdapter adp = new SqlDataAdapter(query, con);
adp.SelectCommand.CommandType = CommandType.Text;
adp.SelectCommand.Parameters.AddWithValue("@RollNo", txtRollNo.Text);
adp.SelectCommand.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
adp.SelectCommand.Parameters.AddWithValue("@LastName", txtLastName.Text);
adp.SelectCommand.Parameters.AddWithValue("@HomeAdd", txtHomeAdd.Text);
DataSet ds = new DataSet();
try
{
adp.Fill(ds);
lblRecordID.Text = ds.Tables[0].Rows[0]["RecordID"].ToString();
lblRollNo.Text = ds.Tables[0].Rows[0]["RollNo"].ToString();
lblFirstName.Text = ds.Tables[0].Rows[0]["FirstName"].ToString();
lblLastName.Text = ds.Tables[0].Rows[0]["LastName"].ToString();
lblHomeAdd.Text = ds.Tables[0].Rows[0]["HomeAdd"].ToString();
txtFirstName.Text = "";
txtHomeAdd.Text = "";
txtLastName.Text = "";
txtRollNo.Text = "";
}
catch (Exception)
{
throw;
}
finally
{
con.Dispose();
adp.Dispose();
ds.Dispose();
}
}Description of Code:
We coded here to return all the Values.
We declared Connection String first. The Main part of our code is .......... output inserted.* ...............
You know all what happened because it is normal procedure of Adding Values at Runtime and saving them to Database Table.
We have used OUTPUT clause to return the values or Output. Then returned INSERTED.* .
INSERTED will tell the Statement to return the Last inserted records. Then * will suggest All the Culumns of Inserted Table.
Finally we have the output as we expected. You can return any value as your convenience.
In this example I simply printed all the Values in Labels just below the Inserted data.
Source code is attached with database design. Just change Instance name in the Connection String.
All the Best.
Glad to be.
John Bhatt
P.Yar.B Complex
Awesome