I am back with another article in ASP.NET, SQL Server and simple Program to demonstrate how to get Last Inserted Record in SQL Server.
Background
Sometime ago, I was creating an Application for my own practice and I need to return Record that is Last inserted in Database.
There are many cases where you may need to return the ID or some record that is just inserted. Suppose you are creating an application for expense entry and user is supposed to record system reference number in the physical expense voucher itself. In my case, which was Voucher ID exactly.
So, if you are having any of following question in mind, you will get answers in this article.
- How to get ID of Last inserted row in SQL Server?
- How to get last inserted ID in SQL?
- How to return last inserted ID in SQL?
- How to get last inserted record in SQL Server?
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
I expect you to have some basic knowledge of ASP.NET and SQL Server. Tools required will be:
- Visual Studio for creating ASP.NET webpage
- SQL Server
Front end Design to get last inserted record in SQL Server
Below is a simple data entry design inside Table for this example with Input TextBox, Button & Labels to display ID returned from server.
ASPX Code
<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>ASPX.CS Code (Backend in C#)
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, then wrote a query, created SQL Data Adaptor, then executed query and stored result in DataSet table. Later printed those value from DataSet tables to Label.
The Main part of our code is in SQL Query
..... student OUTPUT Inserted.* .....;
You know all what happened, because it is normal procedure of Adding Values as Parameters 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 Columns of Inserted Table.
Get Last Inserted record in SQL Server – Output
Finally, we have the output as we expected. You can return any value as your convenience.
Note: Insert, Update and Merge are same and uses same INSERTED to tell the Last inserted table Name
In this example I simply printed all the Values in Labels just below the Inserted data. Subscribe to our YouTube channel.
Download “Get Last Inserted Record in SQL Server”
Get-Last-Inserted-Record-in-SQL-Demo.zip – Downloaded 113 times – 2.16 KB
