Tuesday, October 4, 2011

GridView Highlight Last Inserted Record

Introduction

 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=”&lt;%$ ConnectionStrings:ConnectionString %&gt;“
          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 />
        &nbsp;<asp:Button ID=”ButtonInsert” runat=”server” OnClick=”ButtonInsert_Click” Text=”Insert Record” /></div>
    </form>
</body>
</html>