Have GridView Highlight Last Inserted Record in ASP.NET 2.0
Author: Peter Kellner http://peterkellner.net
Date: December 7, 2006
Introduction
A question that frequently comes up in the asp.net forums is how to have a gridview show the the last record added highlighted. This articles gives a technique for doing this. It only works with SqlServer but could could be modified for other databases.
What It Looks Like Running
After pressing the insert button on the screen shot below, the bottom line was actually added and it is automatically highlighted. This is in a nutshell, what the code listed below and this article discusses.
The Code
In order to run the code below, you must first set up Membership. The simplest way to do this is to simply add to your web.config a very small section enabling RoleManager. This will automatically create the membership database in sqlexpress. You should probably add a couple users just so your gridview is not empty from the start. The code you need to add to an empty asp.net 2.0 web site project is as follows (put it in the <System.Web> section).
<roleManager enabled=“true“></roleManager>
Once you have done that, you can copy the code below to a new web page and simply run it.
Briefly, the way the code works is that when a row is inserted into the database the sqldatasource’s Inserted event is called. In this event, we take a look at the return parameter which comes from the sql:
InsertCommand=”INSERT INTO [Names] ([name]) VALUES (@name);SELECT @NewID = Scope_Identity()”
NewId is actually a return parameter, so we can get that back in the inserted event. Once we get the value back, we store it in viewstate so that on the upcoming page_prerender, we can check and see which row has that id in it, and then highlight that row. The reason we do it in prerender and not load is because the inserted event is processed after load and it would not work if we put it there.
So, here is the code! Good luck.
<%@ Page Language=”C#” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<script runat=”server”>
// simple table named: Names. Two columns: id int, name varchar(64)
protected void ButtonInsert_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters[“name”].DefaultValue = DateTime.Now.ToString();
int numInserted = SqlDataSource1.Insert();
GridView1.DataBind();
}
protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
object newId = e.Command.Parameters[“@NewId”].Value;
ViewState[“NewId”] = Convert.ToInt32(newId);
}
protected void Page_PreRender(object sender, EventArgs e)
{
string newIdLast = string.Empty;
if (ViewState[“NewId”] != null)
{
int newId = (int)ViewState[“NewId”];
newIdLast = newId.ToString();
int rowCnt = 0;
foreach (GridViewRow row in GridView1.Rows)
{
string newIdText = row.Cells[1].Text;
if (newIdText.Equals(newIdLast))
{
//GridView1.EditIndex = rowCnt;
//GridView1.SelectedIndex = rowCnt;
row.Attributes.Add(“bgcolor”, “Gray”);
break;
}
rowCnt++;
}
}
}
</script>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head id=”Head1″ runat=”server”>
<title>Untitled Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<h2>Example of GridView that shows highlighted last inserted row</h2>
<br />
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataKeyNames=”id” DataSourceID=”SqlDataSource1″ >
<Columns>
<asp:CommandField ShowEditButton=”True” ShowDeleteButton=”True” />
<asp:BoundField DataField=”id” HeaderText=”id” InsertVisible=”False” ReadOnly=”True”
SortExpression=”id” />
<asp:BoundField DataField=”name” HeaderText=”name” SortExpression=”name” />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString %>“
DeleteCommand=”DELETE FROM [Names] WHERE [id] = @id”
InsertCommand=”INSERT INTO [Names] ([name]) VALUES (@name);SELECT @NewID = Scope_Identity()”
SelectCommand=”SELECT [id], [name] FROM [Names]” UpdateCommand=”UPDATE [Names] SET [name] = @name WHERE [id] = @id” OnInserted=”SqlDataSource1_Inserted”>
<DeleteParameters>
<asp:Parameter Name=”id” Type=”Int32″ />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name=”name” Type=”String” />
<asp:Parameter Name=”id” Type=”Int32″ />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name=”name” Type=”String” />
<asp:Parameter Direction=Output Name=”NewId” Size=4 Type=Int16 />
</InsertParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:Button ID=”ButtonInsert” runat=”server” OnClick=”ButtonInsert_Click” Text=”Insert Record” /></div>
</form>
</body>
</html>
About the Author
Peter Kellner currently works as an asp.net enterprise consultant at http://www.73rdstreet.com. During the past year, Peter Kellner has authored four MSDN articles dealing with Membership and Profiles. He maintains a blog with more articles at http://peterkellner.net.
February 1st, 2007 at 4:35 pm
Hi,
Pretty cool. You could do this with Oracle as below.
InsertCommand=”INSERT INTO NAME ([NAME]) VALUES (:name) RETURNING COLUMN_ID INTO :new_id”
The above uses two Oracle Parameteres. Obviously the parameter :name is a inbound param, however the :new_id Param catches the new value of the specified column and returns it. Hence new_id should be declared with a direction of return value.
Thanks for the cool idea!
April 30th, 2007 at 12:07 pm
Just wanted to say thanks for posting the above, it’s helped me no end with a current project.
I’m using similar functionality to highlight “deleted” items (rows where a particular Boolean field is set to false). Works a treat.
Now all I have to do is figure out how to hide that field and still use its value.
May 9th, 2007 at 5:41 am
Thanks for the article, but I use paging on the GridView and the foreach process only rows displayed on the current page. If the latest row is on another page, I need to switch to that page. Is there some possibility to do that? Thank you
June 18th, 2007 at 3:50 am
Thanks to you and GridView (it has saved my time)
July 12th, 2007 at 11:39 pm
Please help me asap…
I have this problem.
An OdbcParameter with parametername ‘@NewID’ is not contained by this OdbcparameterCollection.
I just follow instruction and copy paste your program but it doesn’t work like what i’ve expected.
Please help.
Thank you
March 25th, 2008 at 2:19 pm
HOW CAN WE DO THE SAME WITH ACCESS DATABASE?
I AM USING THE DEFAULT FormView to insert data. In my next page I would need to know the id of the row inserted.
I have set the id field to be a auto number.
Thanks you