Manual Insert/Update/Delete operations using Auto-generated editform with sql statements from the code-behind:

15 posts, 0 answers
  1. Shinu
    Shinu avatar
    17725 posts
    Member since:
    Mar 2007

    Posted 25 Jun 2007 Link to this post

    Requirements

    RadGrid for ASP .NET version

    RadControls for ASP .NET AJAX version
    4.6.1 and later


    2008.1.415 and later
    .NET version

    2.0 and later

    Visual Studio version

    2005 and later

    Programming language

    C#

    Browser support

    all supported by RadGrid for ASP .NET


    all browsers supported by RadControls for ASP .NET AJAX


     
    PROJECT DESCRIPTION 
    This is a sample project implementing manual insert/update/delete operations using Auto-generated edit form with sql statements from the code-behind:

    ASPX/ASCX:
    <body> 
        <form id="form1" runat="server">  
        <div> 
            <strong><span style="font-size: 16pt; color: #009900">Manual Insert/Update/Delete Using  
                Auto-Generated EditForm</span></strong><br /> 
            <br /> 
            <radG:RadGrid ID="RadGrid1"  runat="server" Skin="Lime" 
                GridLines="None" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" 
                Width="97%" OnNeedDataSource="RadGrid1_NeedDataSource" OnDeleteCommand="RadGrid1_DeleteCommand" OnInsertCommand="RadGrid1_InsertCommand" OnUpdateCommand="RadGrid1_UpdateCommand" EnableAJAX="True"  > 
                <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle> 
                  
                <MasterTableView DataKeyNames="EmployeeID" GridLines="None" Width="100%" CommandItemDisplay ="Top" > 
                  
                    <Columns> 
                        <radG:GridButtonColumn CommandName="Delete" Text="Delete" UniqueName="Delete">  
                        </radG:GridButtonColumn> 
                        <radG:GridBoundColumn DataField="EmployeeID" HeaderText="EmployeeID" UniqueName="EmployeeID" ReadOnly="True">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="LastName" HeaderText="LastName" UniqueName="LastName">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="FirstName" HeaderText="FirstName" UniqueName="FirstName">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="Title" HeaderText="Title" UniqueName="Title">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="Address" HeaderText="Address" UniqueName="Address">  
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn DataField="City" HeaderText="City" UniqueName="City">  
                        </radG:GridBoundColumn> 
                        <radG:GridEditCommandColumn> 
                        </radG:GridEditCommandColumn> 
                    </Columns> 
                    <EditFormSettings ColumnNumber="2" CaptionFormatString="Edit details for employee with ID {0}" 
                        CaptionDataField="EmployeeID">  
                        <FormTableItemStyle Wrap="False"></FormTableItemStyle> 
                        <FormCaptionStyle CssClass="EditFormHeader"></FormCaptionStyle> 
                        <FormMainTableStyle CellSpacing="0" CellPadding="3" Width="100%" /> 
                        <FormTableStyle GridLines="Horizontal" CellSpacing="0" CellPadding="2" CssClass="module" 
                            Height="110px" Width="100%" /> 
                        <FormTableAlternatingItemStyle Wrap="False"></FormTableAlternatingItemStyle> 
                        <FormStyle Width="100%" BackColor="#EEF2EA"></FormStyle> 
                        <EditColumn UpdateText="Update record" UniqueName="EditCommandColumn1" CancelText="Cancel edit">  
                        </EditColumn> 
                        <FormTableButtonRowStyle HorizontalAlign="Right" CssClass="EditFormButtonRow"></FormTableButtonRowStyle> 
                    </EditFormSettings> 
                    <ExpandCollapseColumn Visible="False">  
                        <HeaderStyle Width="19px"></HeaderStyle> 
                    </ExpandCollapseColumn> 
                    <RowIndicatorColumn Visible="False">  
                        <HeaderStyle Width="20px" /> 
                    </RowIndicatorColumn> 
                </MasterTableView> 
            </radG:RadGrid> 
     
             
        </div> 
        </form> 
    </body> 

    C#:
    using System;  
    using System.Data;  
    using System.Configuration;  
    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 System.Data.SqlClient;  
    using Telerik.WebControls;  
    using System.Configuration;  
     
    public partial class _Default : System.Web.UI.Page   
    {  
        //Declare a global DataTable dtTable    
        public static DataTable dtTable;  
        //Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"    
        public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];  
        public SqlConnection SqlConnection = new SqlConnection(connectionString);  
        //Declare a global SqlDataAdapter SqlDataAdapter    
        public SqlDataAdapter SqlDataAdapter = new SqlDataAdapter();  
        //Declare a global SqlCommand SqlCommand    
        public SqlCommand SqlCommand = new SqlCommand();    
     
        protected void RadGrid1_NeedDataSource(object source, Telerik.WebControls.GridNeedDataSourceEventArgs e)  
        {  
            //Populate the Radgrid    
            dtTable = new DataTable();  
            //Open the SqlConnection    
            SqlConnection.Open();  
            try  
            {  
                //Select Query to populate the RadGrid with data from table Employees.    
                string selectQuery = "SELECT * FROM Employees";  
                SqlDataAdapter.SelectCommand = new SqlCommand(selectQuery, SqlConnection);  
                SqlDataAdapter.Fill(dtTable);  
                RadGrid1.DataSource = dtTable;  
            }  
            finally  
            {  
                //Close the SqlConnection    
                SqlConnection.Close();  
            }    
     
        }  
        protected void RadGrid1_DeleteCommand(object source, Telerik.WebControls.GridCommandEventArgs e)  
        {  
            //Get the GridDataItem of the RadGrid    
            GridDataItem item = (GridDataItem)e.Item;  
            //Get the primary key value using the DataKeyValue.    
            string EmployeeID = item.OwnerTableView.DataKeyValues[item.ItemIndex]["EmployeeID"].ToString();  
            try  
            {  
                //Open the SqlConnection    
                SqlConnection.Open();  
                string deleteQuery = "DELETE from Employees where EmployeeID='" + EmployeeID + "'";  
                SqlCommand.CommandText = deleteQuery;  
                SqlCommand.Connection = SqlConnection;  
                SqlCommand.ExecuteNonQuery();  
                //Close the SqlConnection    
                SqlConnection.Close();  
     
            }  
            catch (Exception ex)  
            {  
                RadGrid1.Controls.Add(new LiteralControl("Unable to delete Employee. Reason: " + ex.Message));  
                e.Canceled = true;  
            }    
     
        }  
        protected void RadGrid1_UpdateCommand(object source, Telerik.WebControls.GridCommandEventArgs e)  
        {  
            //Get the GridEditableItem of the RadGrid    
            GridEditableItem eeditedItem = e.Item as GridEditableItem;  
            //Get the primary key value using the DataKeyValue.    
            string EmployeeID = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["EmployeeID"].ToString();  
            //Access the textbox from the edit form template and store the values in string variables.    
            string LastName = (editedItem["LastName"].Controls[0] as TextBox).Text;  
            string FirstName = (editedItem["FirstName"].Controls[0] as TextBox).Text;  
            string Title = (editedItem["Title"].Controls[0] as TextBox).Text;  
            string Address = (editedItem["Address"].Controls[0] as TextBox).Text;  
            string City = (editedItem["City"].Controls[0] as TextBox).Text;  
     
            try  
            {  
                //Open the SqlConnection    
                SqlConnection.Open();  
                //Update Query to update the Datatable     
                string updateQuery = "UPDATE Employees set LastName='" + LastName + "',FirstName='" + FirstName + "',Title='" + Title + "',Address='" + Address + "',City='" + City + "' where EmployeeID='" + EmployeeID + "'";  
                SqlCommand.CommandText = updateQuery;  
                SqlCommand.Connection = SqlConnection;  
                SqlCommand.ExecuteNonQuery();  
                //Close the SqlConnection    
                SqlConnection.Close();  
     
     
            }  
            catch (Exception ex)  
            {  
                RadGrid1.Controls.Add(new LiteralControl("Unable to update Employee. Reason: " + ex.Message));  
                e.Canceled = true;  
            }    
     
        }  
        protected void RadGrid1_InsertCommand(object source, Telerik.WebControls.GridCommandEventArgs e)  
        {  
            //Get the GridEditFormInsertItem of the RadGrid    
            GridEditFormInsertItem insertedItem = (GridEditFormInsertItem)e.Item;  
     
            //string EmployeeID = (insertedItem["EmployeeID"].Controls[0] as TextBox).Text;  
     
            string LastName = (insertedItem["LastName"].Controls[0] as TextBox).Text;  
            string FirstName = (insertedItem["FirstName"].Controls[0] as TextBox).Text;  
            string Title = (insertedItem["Title"].Controls[0] as TextBox).Text;  
            string Address = (insertedItem["Address"].Controls[0] as TextBox).Text;  
            string City = (insertedItem["City"].Controls[0] as TextBox).Text;  
             
            try  
            {  
                //Open the SqlConnection    
                SqlConnection.Open();  
                //Update Query to insert into  the database     
                string insertQuery = "INSERT into  Employees(LastName,FirstName,Title,Address,City) values('" + LastName + "','" + FirstName + "','" + Title + "','" + Address + "','" + City + "')";  
                SqlCommand.CommandText = insertQuery;  
                SqlCommand.Connection = SqlConnection;  
                SqlCommand.ExecuteNonQuery();  
                //Close the SqlConnection    
                SqlConnection.Close();  
     
     
            }  
            catch (Exception ex)  
            {  
                RadGrid1.Controls.Add(new LiteralControl("Unable to insert Employee. Reason: " + ex.Message));  
                e.Canceled = true;  
            }    
     
        }  
          
    }  
     

    VB.NET
    Public Shared dtTable As DataTable  
    'Get the connectionstring from the webconfig and declare a global SqlConnection "SqlConnection"       
    Public Shared connectionString As String = ConfigurationManager.AppSettings("ConnectionString")  
    Public SqlConnection As New SqlConnection(connectionString)  
    'Declare a global SqlDataAdapter SqlDataAdapter       
    Public SqlDataAdapter As New SqlDataAdapter()  
    'Declare a global SqlCommand SqlCommand       
    Public SqlCommand As New SqlCommand()  
     
    Protected Sub RadGrid1_NeedDataSource(ByVal source As ObjectByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs)  
        'Populate the Radgrid       
        dtTable = New DataTable()  
        'Open the SqlConnection       
        SqlConnection.Open()  
        Try 
            'Select Query to populate the RadGrid with data from table Employees.       
            Dim selectQuery As String = "SELECT * FROM Employees" 
            SqlDataAdapter.SelectCommand = New SqlCommand(selectQuery, SqlConnection)  
            SqlDataAdapter.Fill(dtTable)  
            RadGrid1.DataSource = dtTable  
        Finally 
            'Close the SqlConnection       
            SqlConnection.Close()  
        End Try 
     
    End Sub 
    Protected Sub RadGrid1_DeleteCommand(ByVal source As ObjectByVal e As Telerik.WebControls.GridCommandEventArgs)  
        'Get the GridDataItem of the RadGrid       
        Dim item As GridDataItem = DirectCast(e.Item, GridDataItem)  
        'Get the primary key value using the DataKeyValue.       
        Dim EmployeeID As String = item.OwnerTableView.DataKeyValues(item.ItemIndex)("EmployeeID").ToString()  
        Try 
            'Open the SqlConnection       
            SqlConnection.Open()  
            Dim deleteQuery As String = "DELETE from Employees where EmployeeID='" & EmployeeID & "'" 
            SqlCommand.CommandText = deleteQuery  
            SqlCommand.Connection = SqlConnection  
            SqlCommand.ExecuteNonQuery()  
            'Close the SqlConnection       
     
            SqlConnection.Close()  
        Catch ex As Exception  
            RadGrid1.Controls.Add(New LiteralControl("Unable to delete Employee. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
     
    End Sub 
    Protected Sub RadGrid1_UpdateCommand(ByVal source As ObjectByVal e As Telerik.WebControls.GridCommandEventArgs)  
        'Get the GridEditableItem of the RadGrid       
        Dim eeditedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)  
        'Get the primary key value using the DataKeyValue.       
        Dim EmployeeID As String = editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("EmployeeID").ToString()  
        'Access the textbox from the edit form template and store the values in string variables.       
        Dim LastName As String = (TryCast(editedItem("LastName").Controls(0), TextBox)).Text  
        Dim FirstName As String = (TryCast(editedItem("FirstName").Controls(0), TextBox)).Text  
        Dim Title As String = (TryCast(editedItem("Title").Controls(0), TextBox)).Text  
        Dim Address As String = (TryCast(editedItem("Address").Controls(0), TextBox)).Text  
        Dim City As String = (TryCast(editedItem("City").Controls(0), TextBox)).Text  
     
        Try 
            'Open the SqlConnection       
            SqlConnection.Open()  
            'Update Query to update the Datatable        
            Dim updateQuery As String = "UPDATE Employees set LastName='" & LastName & "',FirstName='" & FirstName & "',Title='" & Title & "',Address='" & Address & "',City='" & City & "' where EmployeeID='" & EmployeeID & "'" 
            SqlCommand.CommandText = updateQuery  
            SqlCommand.Connection = SqlConnection  
            SqlCommand.ExecuteNonQuery()  
            'Close the SqlConnection       
     
     
            SqlConnection.Close()  
        Catch ex As Exception  
            RadGrid1.Controls.Add(New LiteralControl("Unable to update Employee. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
     
    End Sub 
    Protected Sub RadGrid1_InsertCommand(ByVal source As ObjectByVal e As Telerik.WebControls.GridCommandEventArgs)  
        'Get the GridEditFormInsertItem of the RadGrid       
        Dim insertedItem As GridEditFormInsertItem = DirectCast(e.Item, GridEditFormInsertItem)  
     
        'string EmployeeID = (insertedItem["EmployeeID"].Controls[0] as TextBox).Text;     
     
        Dim LastName As String = (TryCast(insertedItem("LastName").Controls(0), TextBox)).Text  
        Dim FirstName As String = (TryCast(insertedItem("FirstName").Controls(0), TextBox)).Text  
        Dim Title As String = (TryCast(insertedItem("Title").Controls(0), TextBox)).Text  
        Dim Address As String = (TryCast(insertedItem("Address").Controls(0), TextBox)).Text  
        Dim City As String = (TryCast(insertedItem("City").Controls(0), TextBox)).Text  
     
        Try 
            'Open the SqlConnection       
            SqlConnection.Open()  
            'Update Query to insert into  the database        
            Dim insertQuery As String = "INSERT into  Employees(LastName,FirstName,Title,Address,City) values('" & LastName & "','" & FirstName & "','" & Title & "','" & Address & "','" & City & "')" 
            SqlCommand.CommandText = insertQuery  
            SqlCommand.Connection = SqlConnection  
            SqlCommand.ExecuteNonQuery()  
            'Close the SqlConnection       
     
     
            SqlConnection.Close()  
        Catch ex As Exception  
            RadGrid1.Controls.Add(New LiteralControl("Unable to insert Employee. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
     
    End Sub 
     

  2. Sebastian
    Admin
    Sebastian avatar
    9804 posts
    Member since:
    Sep 2012

    Posted 26 Jun 2007 Link to this post

    Hi Shinu,

    Thank you for your post - this will surely be a nice starting point for everyone interest in implementing manual insert/update/delete having SQL backend and auto-generated edit form. I have added 1000 Telerik points to your account for the involvement.

    Best,
    Stephen
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  3. Stephen Tickner
    Stephen Tickner avatar
    3 posts
    Member since:
    May 2006

    Posted 24 Aug 2007 Link to this post

    Hi Shinu,
    Thank you for the project example. I've run through this project and I am having a problem where the RadGrid1_UpdateCommand and RadGrid1_InsertCommand events are not firing? Do they fire for you in this example when you click on the Insert or Update links in the auto generated edit form?

    I can't get these events to fire at all??
    Thanks
    Steve

  4. Yavor
    Admin
    Yavor avatar
    11 posts
    Member since:
    Dec 2013

    Posted 27 Aug 2007 Link to this post

    Hello,

    I reviewed and tested the sample locally.
    The events were properly fired, since the insert/update functionality is handled in them.
    Please, make sure that your events are properly attached. If the problem persists at your end, you can open a formal support ticket, and send us a small project sample, with the problematic implementation.

    Best wishes,
    Yavor
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  5. John
    John avatar
    8 posts
    Member since:
    Sep 2007

    Posted 13 Sep 2007 Link to this post

    Hi,
    if I've a template column with a raddatapicker, the code fails. See example.

    vb.code
    myString = (TryCast(editedItem("MyField").Controls(0), TextBox)).Text.Trim

    aspx.codce
    <radG:GridTemplateColumn HeaderText="My Field" UniqueName="MyField" >
    <EditItemTemplate >
    <radCln:RadDatePicker ID="RadDatePicker1" runat="server" DbSelectedDate='<%# Bind("MyField", "{0:d}") %>' SharedCalendarID="" >
    </radCln:RadDatePicker>
    </EditItemTemplate>
    <ItemTemplate><asp:Label ID="dLabel" runat="server" Text='<%# Eval("MyField", "{0:d}") %>' />
    </ItemTemplate>
    </radG:GridTemplateColumn>


    Thanks
    Fabio

  6. Sebastian
    Admin
    Sebastian avatar
    9804 posts
    Member since:
    Sep 2012

    Posted 13 Sep 2007 Link to this post

    Hi Fabio,

    Two sample project presenting how to update/insert records having RadDatePicker editors in template columns you can find in this section of our knowledge base:

    http://www.telerik.com/support/kb/article/b454K-gmg-b454T-cbb-b454c-cbb.aspx

    I believe that they will help you address the problem you are facing.

    Best,
    Stephen
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  7. Ronaldo
    Ronaldo avatar
    5 posts
    Member since:
    Oct 2007

    Posted 01 Oct 2007 Link to this post

    Hi! Do you have complete package sample of manual insert/update/delete with dropdownlist and datepicker? And does the form edit template has interface for editing or you can just edit it in aspx source?  I will greatly appreciate if you can give me a sample of this. Thanks!

  8. Sebastian
    Admin
    Sebastian avatar
    9804 posts
    Member since:
    Sep 2012

    Posted 03 Oct 2007 Link to this post

    Hi Ronaldo,

    One of the projects from the article linked in the previous reply along with the demo from this KB article:

    http://www.telerik.com/support/kb/article/b454K-gmk-b454T-cbb-b454c-cbb.aspx

    demonstrate manual update/insert with date pickers and dropdown list editors. Review the code logic from the samples for more info. To transform the implementation for FormTemplate custom edit form  I suggest you review the following online resources:

    http://www.telerik.com/demos/aspnet/Grid/Examples/DataEditing/TemplateFormUpdate/DefaultVB.aspx

    http://www.telerik.com/help/aspnet/grid/?grdUpdatingValuesUserControlFormTemplate.html

    http://www.telerik.com/help/aspnet/grid/?grdInsertingValuesUserControlFormTemplate.html

    Note that the delete operation is not dependant on the edit form type. How to delete records you can find out from this help topic:

    http://www.telerik.com/help/aspnet/grid/?grdHowToDeleteARecord.html

    Best regards,
    Stephen
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  9. Henri De Roeck
    Henri De Roeck avatar
    5 posts
    Member since:
    Jun 2006

    Posted 16 Oct 2007 Link to this post

    hi,

    I've been searching for a while now how to update an item in a RADGrid. If i try to use code provided by shinu, i keep getting the following problem:

    On update, in my code behind, i always get the old values when i try to get the new values like this:

    string LastName = (editedItem["LastName"].Controls[0] as TextBox).Text;

    Also when i try an other way (the only other way i found) with extractValuesFromItem i keep getting the old values, not the adjusted ones...  Hereby, i notice that SavedOldValues is empty
    editedItem.OwnerTableView.ExtractValuesFromItem(newValues, editedItem);

    What am i doing wrong? How can i read the new values from the textboxes on the automatically generated editform? It seems so easy, i feel stupid it wont work with me...

  10. Sebastian
    Admin
    Sebastian avatar
    9804 posts
    Member since:
    Sep 2012

    Posted 16 Oct 2007 Link to this post

    Hi Henri,

    Do you use advanced binding for your grid through the NeedDataSource event as Shinu did? Note that editing operations using the ExtractValuesFromItem method are not supported with simple data-binding calling DataBind(). This might be the cause for the old values been extracted on update/insert action.

    Best regards,
    Stephen
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  11. Henri De Roeck
    Henri De Roeck avatar
    5 posts
    Member since:
    Jun 2006

    Posted 16 Oct 2007 Link to this post

    Stephen,

    First of all thank you for the very fast anwser!

    I implemented the needDataSource Event as you proposed and it works perfect! I did not knew that this had to be implemented when using ExtractValuesFromItem.


  12. Puay Chai Tan
    Puay Chai Tan avatar
    22 posts
    Member since:
    May 2010

    Posted 21 Aug 2010 Link to this post

    Hi ,
       can u provide me a sample for Hierarchical Radgrid's Manual Insert/Update/Delete operations using Auto-generated editform with sql statements from the code-behind

  13. Ram
    Ram avatar
    97 posts
    Member since:
    Jun 2011

    Posted 04 Jul 2011 Link to this post

    Hi I have radgrid.
    I have 9 records in table but it showing 6.
    And sometimes 8 like that why its happening
    can u please answer my question

  14. Ricardo
    Ricardo avatar
    113 posts
    Member since:
    Jul 2011

    Posted 27 Jul 2011 Link to this post

    Hello! I used the sample above, and i have a question:

    How can i delete the add option from the grid??

  15. Aarthi
    Aarthi avatar
    3 posts
    Member since:
    Sep 2011

    Posted 23 Feb 2012 Link to this post

    Dear Friend,
                        Your code sample was very useful for me,its working properly without layer,when i am trying to apply it in layered architechture ,its not working.
    Can you please explain me how to apply this concept with help of business entities and Business logic methods.

Back to Top