Have GridView Highlight Last Inserted Record in ASP.NET 2.0

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=”serverAutoGenerateColumns=”FalseDataKeyNames=”idDataSourceID=”SqlDataSource1? >
            <Columns>
                <asp:CommandField ShowEditButton=”TrueShowDeleteButton=”True/>
                <asp:BoundField DataField=”idHeaderText=”idInsertVisible=”FalseReadOnly=”TrueSortExpression=”id/>
                <asp:BoundField DataField=”nameHeaderText=”nameSortExpression=”name/>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID=”SqlDataSource1? runat=”serverConnectionString=”&lt;%$ ConnectionStrings:ConnectionString %&gt;“
          DeleteCommand=”DELETE FROM [Names] WHERE [id] = @idInsertCommand=”INSERT INTO [Names] ([name]) VALUES (@name);SELECT @NewID = Scope_Identity()”

          SelectCommand=”SELECT [id], [name] FROM [Names]“ UpdateCommand=”UPDATE [Names] SET [name] = @name WHERE [id] = @idOnInserted=”SqlDataSource1_Inserted>
            <DeleteParameters>
                <asp:Parameter Name=”idType=”Int32? />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name=”nameType=”String/>
                <asp:Parameter Name=”idType=”Int32? />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name=”nameType=”String/>
               <asp:Parameter Direction=Output Name=”NewIdSize=4 Type=Int16 />
            </InsertParameters>
        </asp:SqlDataSource>
        <br />
        <br />
        &nbsp;<asp:Button ID=”ButtonInsertrunat=”serverOnClick=”ButtonInsert_ClickText=”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.

About Peter Kellner

Follow me:


Comments

  1. shalin gajjar says:

    I used this technique but one problem occured i’ m not using any Special Bound Field as Id in Cell position at 1 but binding hidden field at first column of grid view with value = .

    When i inserting record in grid view Viewstate works fine and i get Scope_intentity of element.

    but when on Page_PreRender event i can not access recently added item scope itentity as hidden field.

    it always shows me first hidden value. so, i don’t navigate page index to recently added item

    can you help me….

  2. paul makram says:

    How can i thank you ,This article have a nice ideas ,But how i can select a record from a GridView and insert that Row to anoter table ,Tanks in advance

  3. To: David and EnalraSanlac

    Are you using SqlServer? NewId is a sqlserver thing and does not apply to other databases.

  4. Just the EnelraSanlac above, I’m getting the same error message. Any ideas?

  5. Dessy Petrova says:

    I have a paged GridView. Every time when I insert new record, I set the selected row in the GridView to the inserted record. How can I done this? Here is sample code:
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If (gvClients.Rows.Count 0) Then
    gvClients.PageIndex = gvClients.PageCount – 1
    End If
    End Sub
    Protected Sub gvClients_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvClients.DataBound
    gvClients.SelectedIndex = gvClients.Rows.Count – 1
    End Sub
    Here gvClients is GridView, which I’ve used.

  6. Thank you soooo much. I am using VB, but was able to get this to work to redirect users to the a page with the “new” entry id.

  7. EnelraSanlac says:

    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

  8. Thanks to you and GridView (it has saved my time)

  9. 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

  10. 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. :(

  11. Damian Young says:

    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!

Your Comments

*

Protected with IP Blacklist CloudIP Blacklist Cloud

Follow

Get every new post delivered to your Inbox

Join other followers: