Select Last Inserted Row

13 posts, 0 answers
  1. Shinu
    Shinu avatar
    17764 posts
    Member since:
    Mar 2007

    Posted 09 Nov 2007 Link to this post

    Requirements

    RadControls version

    5.0.1

    .NET version

    2.0

    Visual Studio version

    2005

    programming language

    C#

    browser support

    all browsers supported by RadControls


     
    PROJECT DESCRIPTION
    This project describes how to select the last inserted row in the RadGrid. In the PreRender event get the  key value for each item in the RadGrid and then find the autoincremented  key value using a Select query from the database This will return the topmost key value (which will naturally be the last inserted row). Compare these values and select the row with the matching ID.


    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %> 
     
    <%@ Register Assembly="RadGrid.Net2" Namespace="Telerik.WebControls" TagPrefix="radG" %> 
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
     
    <html xmlns="http://www.w3.org/1999/xhtml" > 
    <head runat="server">  
        <title>Untitled Page</title> 
    </head> 
    <body> 
        <form id="form1" runat="server">  
        <div> 
            <strong><span style="color: #cc66ff">SELECT LAST INSERTED ROW<br /> 
            </span></strong>  
            <br /> 
            <br /> 
            <radG:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource" 
                OnPreRender="RadGrid1_PreRender" Skin="Brick" OnInsertCommand="RadGrid1_InsertCommand" AutoGenerateColumns="False" GridLines="None">  
                <ExportSettings> 
                    <Pdf PageBottomMargin="" PageFooterMargin="" PageHeaderMargin="" PageHeight="11in" 
                        PageLeftMargin="" PageRightMargin="" PageTopMargin="" PageWidth="8.5in" /> 
                </ExportSettings> 
                <MasterTableView CommandItemDisplay="Top">  
                    <ExpandCollapseColumn Resizable="False" Visible="False">  
                        <HeaderStyle Width="20px" /> 
                    </ExpandCollapseColumn> 
                    <RowIndicatorColumn Visible="False">  
                        <HeaderStyle Width="20px" /> 
                    </RowIndicatorColumn> 
                    <Columns> 
                        <radG:GridBoundColumn DataField="CategoryID" HeaderText="CategoryID"  ReadOnly="True"   UniqueName="CategoryID">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="CategoryName" HeaderText="CategoryName" UniqueName="CategoryName">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="Description" HeaderText="Description" UniqueName="Description">  
                        </radG:GridBoundColumn> 
                    </Columns> 
                </MasterTableView> 
            </radG:RadGrid></div>  
        </form> 
    </body> 
    </html> 
     

    CS:

    using System;  
    using System.Data;  
    using System.Configuration;  
    using System.Collections;  
    using System.Web;  
    using System.Web.Security;  
    using System.Web.UI;  
    using System.Web.UI.WebControls;  
    using System.Web.UI.WebControls.WebParts;  
    using System.Web.UI.HtmlControls;  
    using Telerik.WebControls;  
    using System.Data.OleDb;   
     
    public partial class _Default : System.Web.UI.Page   
    {  
        //Declare a global DataTable dtTable   
        public static DataTable dtTable;  
        public static int intLastCategoryID;  
        OleDbDataAdapter MyOleDbDataAdapter = new OleDbDataAdapter();  
        OleDbCommand MyOleDbCommand = new OleDbCommand();  
        protected void Page_Load(object sender, EventArgs e)  
        {  
     
        }  
        protected void RadGrid1_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)  
        {  
         OleDbConnection MyOleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"));  
         
              
     
            //Populate the Radgrid   
            //the database in the web application root   
            dtTable = new DataTable();  
     
            MyOleDbConnection.Open();  
            try  
            {  
     
                string query = "SELECT CategoryID,CategoryName,Description FROM Categories";  
                MyOleDbDataAdapter.SelectCommand = new OleDbCommand(query, MyOleDbConnection);  
                MyOleDbDataAdapter.Fill(dtTable);  
     
                RadGrid1.DataSource = dtTable;  
            }  
            finally  
            {  
                MyOleDbConnection.Close();  
            }   
               
        }  
        protected void RadGrid1_PreRender(object sender, EventArgs e)  
        {  
           if(!RadGrid1.MasterTableView.IsItemInserted)
           { 
            // Get the CategoryID of the last inseted record  
            OleDbConnection MyOleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"));  
            dtTable = new DataTable();  
     
            MyOleDbConnection.Open();  
            try  
            {  
                MyOleDbCommand.CommandText = "SELECT MAX(CategoryID) FROM  Categories";  
                MyOleDbCommand.Connection = MyOleDbConnection;  
                intLastCategoryID =Convert.ToInt16(MyOleDbCommand.ExecuteScalar().ToString());  
     
            }  
            finally  
            {  
                MyOleDbConnection.Close();  
            }  
              
            // Compare the CategoryID of the each item with the newly inserted record CategoryID   
            // and select the last inserted row  
            foreach (GridDataItem item in RadGrid1.Items)  
            {  
                int intCategoryID = Convert.ToInt16(item["CategoryID"].Text);  
                if (intCategoryID == intLastCategoryID)  
                {  
                    item.Selected = true;  
                }  
            }  
          } 
        }  
        protected void RadGrid1_InsertCommand(object source, GridCommandEventArgs e)  
        {  
            //Get the GridEditFormInsertItem of the RadGrid      
            GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;  
     
            OleDbConnection MyOleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"));  
     
            string strCategoryName = (insertedItem["CategoryName"].Controls[0] as TextBox).Text;  
            string strDescription = (insertedItem["Description"].Controls[0] as TextBox).Text;  
     
            MyOleDbConnection.Open();  
            try  
            {  
                //Open the SqlConnection      
                 
                //Update Query to insert into  the database       
                string insertQuery = "INSERT into  Categories(CategoryName,Description) values('" + strCategoryName + "','" + strDescription  + "')";  
                MyOleDbCommand.CommandText = insertQuery;  
                MyOleDbCommand.Connection = MyOleDbConnection;  
                MyOleDbCommand.ExecuteNonQuery();  
                //Close the SqlConnection      
                MyOleDbConnection.Close();  
     
     
            }  
            catch (Exception ex)  
            {  
                RadGrid1.Controls.Add(new LiteralControl("Unable to insert Employee. Reason: " + ex.Message));  
                e.Canceled = true;  
            }  
        }  
    }  
     

    VB:

    'Declare a global DataTable dtTable   
    Public Shared dtTable As DataTable  
    Public Shared intLastCategoryID As Integer 
    Private MyOleDbDataAdapter As New OleDbDataAdapter()  
    Private MyOleDbCommand As New OleDbCommand()  
    Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs)  
     
    End Sub 
    Protected Sub RadGrid1_NeedDataSource(ByVal source As ObjectByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs)  
        Dim MyOleDbConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"))  
     
     
     
        'Populate the Radgrid   
        'the database in the web application root   
        dtTable = New DataTable()  
     
        MyOleDbConnection.Open()  
        Try 
     
            Dim query As String = "SELECT CategoryID,CategoryName,Description FROM Categories" 
            MyOleDbDataAdapter.SelectCommand = New OleDbCommand(query, MyOleDbConnection)  
            MyOleDbDataAdapter.Fill(dtTable)  
     
            RadGrid1.DataSource = dtTable  
        Finally 
            MyOleDbConnection.Close()  
        End Try 
     
    End Sub 
    Protected Sub RadGrid1_PreRender(ByVal sender As ObjectByVal e As EventArgs)  
        If Not RadGrid1.MasterTableView.IsItemInserted Then 
            ' Get the CategoryID of the last inseted record  
            Dim MyOleDbConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"))  
            dtTable = New DataTable()  
     
            MyOleDbConnection.Open()  
            Try 
                MyOleDbCommand.CommandText = "SELECT MAX(CategoryID) FROM  Categories" 
                MyOleDbCommand.Connection = MyOleDbConnection  
     
                intLastCategoryID = Convert.ToInt16(MyOleDbCommand.ExecuteScalar().ToString())  
            Finally 
                MyOleDbConnection.Close()  
            End Try 
     
            ' Compare the CategoryID of the each item with the newly inserted record CategoryID   
            ' and select the last inserted row  
            For Each item As GridDataItem In RadGrid1.Items  
                Dim intCategoryID As Integer = Convert.ToInt16(item("CategoryID").Text)  
                If intCategoryID = intLastCategoryID Then 
                    item.Selected = True 
                End If 
            Next 
        End If 
    End Sub 
    Protected Sub RadGrid1_InsertCommand(ByVal source As ObjectByVal e As GridCommandEventArgs)  
        'Get the GridEditFormInsertItem of the RadGrid      
        Dim insertedItem As GridEditFormInsertItem = DirectCast(e.Item, GridEditFormInsertItem)  
     
        Dim MyOleDbConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~//App_Data//Nwind.mdb"))  
     
        Dim strCategoryName As String = (TryCast(insertedItem("CategoryName").Controls(0), TextBox)).Text  
        Dim strDescription As String = (TryCast(insertedItem("Description").Controls(0), TextBox)).Text  
     
        MyOleDbConnection.Open()  
        Try 
            'Open the SqlConnection      
     
            'Update Query to insert into  the database       
            Dim insertQuery As String = "INSERT into  Categories(CategoryName,Description) values('" + strCategoryName + "','" + strDescription + "')" 
            MyOleDbCommand.CommandText = insertQuery  
            MyOleDbCommand.Connection = MyOleDbConnection  
            MyOleDbCommand.ExecuteNonQuery()  
            'Close the SqlConnection      
     
     
            MyOleDbConnection.Close()  
        Catch ex As Exception  
            RadGrid1.Controls.Add(New LiteralControl("Unable to insert Employee. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
    End Sub 


    Shinu
  2. Sebastian
    Admin
    Sebastian avatar
    9934 posts

    Posted 13 Nov 2007 Link to this post

    Hello Shinu,

    Thank you for the code library submission. This example will be helpful for other people which would like to select the newly inserted record in RadGrid by default. I have added 3000 Telerik points to your account for the post.

    Best regards,
    Stephen
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  3. Krishna
    Krishna avatar
    89 posts
    Member since:
    Jul 2008

    Posted 15 Aug 2008 Link to this post

    Hi Shinu,
    this code is not working if the grid has paging, can u please help how to show the last inserted row or select last inserted row and show to the user.

    Thanks
    krishna
  4. Debzequke
    Debzequke avatar
    3 posts
    Member since:
    Jun 2007

    Posted 03 Sep 2008 Link to this post

    Hello Krishna,

    You can find the extended version of this project handling both grid paging and sorting at here.

    Regards,
    Debzequke
  5. joan
    joan avatar
    4 posts
    Member since:
    Nov 2008

    Posted 25 Feb 2009 Link to this post

    Hi:

    I tried the code to high light the newly inserted row, add a RadGrid PreRender event, but it seems always highlight the last row. 
    I only want to high light the row if it's newly inserted.  Am I missing something?

    thanks.

    Sue
  6. Yavor
    Admin
    Yavor avatar
    11 posts

    Posted 26 Feb 2009 Link to this post

    Hi joan,

    I double checked the code, and it should select the last inserted row, on the current page. Based on its logic, the last inserted row should have the biggest id. You can slightly modify this, by persisting the last inserted id in ViewState, and preselecting it on prerender.

    Regards,
    Yavor
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  7. xis xix
    xis xix avatar
    8 posts
    Member since:
    May 2010

    Posted 14 May 2010 Link to this post

    Hi. I used and adapt your code to my needs. Correct if i'm wrong but only works if grid is order by number, right?
    in my case my grid was alphabetical ordered so the last inserted item could be in any page (could that be joan's problem?).
    So i made some arrangements.  and if item was inserted on last page, your code would show 1a page , it was another adpations i made.
    Other thing that cause me problems as putting this in prerender (the application where i am fixing things uses diferent states) so i transform your code into a normal function and call it only when i need (after insertion) and it works just fine (i use code from the link you provide (http://www.telerik.com/community/forums/aspnet/grid/select-last-inserted-row-in-the-radgrid-with-both-paging-and-sorting-enabled.aspx#619146)

    Bottom line, thanks a lot for help. It was precious =)


    But mainly i adapt your code on last lines:
    my code:
                        if (currentPageIndex >= RadGrid1.PageCount)
                            RadGrid1.CurrentPageIndex = RadGrid1.PageCount - 1;

                        RadGrid1.CurrentPageIndex = currentPageIndex;
                        RadGrid1.Rebind();

    your code
     // Go to next RadGrid page
                currentPageIndex++;
                if (currentPageIndex >= RadGrid1.PageCount)
                    currentPageIndex = 0;
                RadGrid1.CurrentPageIndex = currentPageIndex;
                RadGrid1.Rebind();
  8. Sarulatha Selvan
    Sarulatha Selvan avatar
    10 posts
    Member since:
    Sep 2008

    Posted 06 Jul 2010 Link to this post

    Hi xis xix,

    Could you please post the piece of code where you have not used the Pre-render event but still have achieved this functionality?

    Thanks,
    Saru. 
  9. xis xix
    xis xix avatar
    8 posts
    Member since:
    May 2010

    Posted 07 Jul 2010 Link to this post

    Hi... My app has some particularity .. but the code is this:


    "if (this.WorkingMode == WorkMode.New)
                {
                    long newShareHolder = this.newShareHolderID;
                    int currentPageIndex = RadGrid1.CurrentPageIndex;

                    for (int i = 0; i < RadGrid1.PageCount; i++)
                    {
                        foreach (GridDataItem item in RadGrid1.Items)
                        {
                            long intCategoryID = Convert.ToInt64(item["shareholderID"].Text);
                            if (intCategoryID == newShareHolder)
                            {
                                item.Selected = true;
                                currentPageIndex = -1; // flag exit
                                break;
                            }
                        }
                        // If item is found then exit RadGrid page loop
                        if (currentPageIndex.Equals(-1))
                            break;

                        currentPageIndex++;

                        if (currentPageIndex >= RadGrid1.PageCount)
                            RadGrid1.CurrentPageIndex = RadGrid1.PageCount - 1;

                        RadGrid1.CurrentPageIndex = currentPageIndex;
                        RadGrid1.Rebind();
                    }
                }"
  10. Sarulatha Selvan
    Sarulatha Selvan avatar
    10 posts
    Member since:
    Sep 2008

    Posted 08 Jul 2010 Link to this post

    Thanks Xis Xis for the code. You have mentioned that you would call this code only when the last row is inserted and how is it possible to highlight the row (item.selected = true) when the page is posted back if you are not using the Pre render event?

    Could you please elaborate on this?

    Thanks,
    Saru.
  11. xis xix
    xis xix avatar
    8 posts
    Member since:
    May 2010

    Posted 09 Jul 2010 Link to this post

    I will verify my code soon i can.
    I'm working on a previous developed app by some other coders.
    As i remember, when i used on pre-render an error occur.
    The thing is, this is only used after a recorded was inserted... No post-back need...
    I can really help more - my knowledge about rad controls is little.
  12. Farid Hayati
    Farid Hayati avatar
    17 posts
    Member since:
    Mar 2011

    Posted 11 Mar 2011 Link to this post

    What about if 2-3 people doing at the same time insert from different places? I want get my last inserted row with the primary key. How can I do that.  

    Thanks
  13. Phil
    Phil avatar
    12 posts
    Member since:
    Jan 2011

    Posted 19 Apr 2011 Link to this post

    Is there not a method to do this without looping through each row in the grid?
Back to Top