Manual Insert/Update/Delete using FormTemplate and Sql backend

Thread is closed for posting
8 posts, 0 answers
  1. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 15 Jun 2007 Link to this post

    Requirements

    RadGrid for ASP .NET version

    Q1 2007 and later
    .NET version

    2.0
    Visual Studio version

    2005
    Programming language

    C#
    Browser support

    all supported by RadGrid for ASP .NET


     
    PROJECT DESCRIPTION

    The project implements manual insert/update/delete operations using Form Template and Sql backend (with sql statements from the code-behind):

    ASPX/ASCX:

    <%@ 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> 
          <asp:Label ID="Label1" runat="server" Text="Manual Insert and Update Using Form Tempalte" EnableViewState="False" Font-Bold="True" ></asp:Label> 
            <br /> 
            <br /> 
           
             
            <radG:RadGrid ID="RadGrid1" Skin="Lime" runat="server" CssClass="RadGrid" GridLines="None" AllowPaging="True" 
                PageSize="5" AllowSorting="True" Width="99%" AutoGenerateColumns="False" EnableAJAX="True" ShowStatusBar="true" 
                HorizontalAlign="NotSet" OnNeedDataSource="RadGrid1_NeedDataSource" OnUpdateCommand="RadGrid1_UpdateCommand" OnInsertCommand="RadGrid1_InsertCommand" OnDeleteCommand="RadGrid1_DeleteCommand" > 
                <PagerStyle Mode="NextPrevAndNumeric"></PagerStyle> 
                <MasterTableView CommandItemDisplay="Top" GridLines="None"  
                    DataKeyNames="CustomerID"
                    <Columns> 
                        <radG:GridEditCommandColumn> 
                        </radG:GridEditCommandColumn> 
                        <radG:GridBoundColumn UniqueName="CustomerID" HeaderText="ID"  ReadOnly="true" DataField="CustomerID"
                             
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn UniqueName="CompanyName" HeaderText="CompanyName" DataField="CompanyName"
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn UniqueName="ContactName" HeaderText="ContactName" DataField="ContactName"
                        </radG:GridBoundColumn> 
                        <radG:GridBoundColumn UniqueName="Address" HeaderText="Address" DataField="Address"
                        </radG:GridBoundColumn> 
                        <radG:GridButtonColumn CommandName="Delete" Text="Delete"
                        </radG:GridButtonColumn> 
                         
                    </Columns> 
                    <EditFormSettings EditFormType="Template"
                        <EditColumn UniqueName="EditCommandColumn1"
                        </EditColumn> 
                        <FormTemplate> 
                            <table id="Table2" cellspacing="2" cellpadding="1" width="250" border="1" rules="none" 
                                style="border-collapse: collapse"
                                <tr> 
                                    <td> 
                                        <table id="Table3" cellspacing="1" cellpadding="1" width="250" border="0" > 
                                            <tr> 
                                                <td> 
                                                </td> 
                                                <td> 
                                                </td> 
                                            </tr> 
                                             <tr> 
                                                <td> 
                                                    CustomerID:</td> 
                                                <td> 
                                                    <asp:TextBox ID="txtCustomerID"  MaxLength=5  Visible='<% # (Container as GridItem).OwnerTableView.IsItemInserted %>' runat="server" > 
                                                    </asp:TextBox></td
                                            </tr> 
                                            <tr> 
                                                <td> 
                                                    CompanyName:</td> 
                                                <td> 
                                                    <asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Eval( "CompanyName" ) %>'
                                                    </asp:TextBox></td
                                            </tr> 
                                            <tr> 
                                                <td> 
                                                    ContactName:</td> 
                                                <td> 
                                                    <asp:TextBox ID="txtContactName" runat="server" Text='<%# Eval( "ContactName") %>' TabIndex="1"
                                                    </asp:TextBox></td
                                            </tr> 
                                            <tr> 
                                                <td> 
                                                    Address:</td> 
                                                <td> 
                                                    <asp:TextBox ID="txtAddress" runat="server" Text='<%# Eval( "Address") %>' TabIndex="2"
                                                    </asp:TextBox></td
                                            </tr> 
                                           
                                        </table> 
                                    </td> 
                                    
                                </tr> 
                                <tr> 
                                    <td colspan="2"
                                        <b>Company Info:</b></td
                                </tr> 
                                <tr> 
                                    <td align="right" colspan="2"
                                        <asp:Button ID="btnUpdate" Text='<%# (Container as GridItem).OwnerTableView.IsItemInserted ? "Insert" : "Update" %>' 
                                            runat="server" CommandName='<%# (Container as GridItem).OwnerTableView.IsItemInserted ? "PerformInsert" : "Update" %>'
                                        </asp:Button>&nbsp; 
    <asp:Button ID="btnCancel" Text="Cancel" runat="server" CausesValidation="False" 
    CommandName="Cancel"></asp:Button></td
                                </tr> 
                            </table> 
                        </FormTemplate> 
                    </EditFormSettings> 
                    <ExpandCollapseColumn Visible="False"
                        <HeaderStyle Width="19px" /> 
                    </ExpandCollapseColumn> 
                    <RowIndicatorColumn Visible="False"
                        <HeaderStyle Width="20px" /> 
                    </RowIndicatorColumn> 
                </MasterTableView> 
            </radG:RadGrid>&nbsp; 
           
             
          </div> 
        </form> 
    </body> 
    </html> 
     

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

    VB.NET

    'Declare a global DataTable dtTable    
    Public Shared dtTable As DataTable  
    'Declare a global SqlConnection SqlConnection    
    Public SqlConnection As New SqlConnection("Data Source=local;Initial Catalog=Northwind;User ID=**")  
    '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 GridNeedDataSourceEventArgs)  
        'Populate the Radgrid    
        dtTable = New DataTable()  
        'Open the SqlConnection    
        SqlConnection.Open()  
        Try 
            'Select Query to populate the RadGrid with data from table Customers.    
            Dim selectQuery As String = "SELECT CustomerID,CompanyName,ContactName,Address FROM Customers" 
            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_UpdateCommand(ByVal source As ObjectByVal e As GridCommandEventArgs)  
        'Get the GridEditableItem of the RadGrid    
        Dim eeditedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)  
        'Get the primary key value using the DataKeyValue.    
        Dim CustomerID As String = editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("CustomerID").ToString()  
        'Access the textbox from the edit form template and store the values in string variables.    
        Dim CompanyName As String = (TryCast(editedItem.FindControl("txtCompanyName"), TextBox)).Text  
        Dim ContactName As String = (TryCast(editedItem.FindControl("txtContactName"), TextBox)).Text  
        Dim Address As String = (TryCast(editedItem.FindControl("txtAddress"), TextBox)).Text  
     
        Try 
            'Open the SqlConnection    
            SqlConnection.Open()  
            'Update Query to update the Datatable     
            Dim updateQuery As String = "UPDATE Customers set CompanyName='" & CompanyName & "',ContactName='" & ContactName & "',Address='" & Address & "' where CustomerID='" & CustomerID & "'" 
            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 Customers. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
    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)  
     
        'Access the textbox from the insert form template and store the values in string variables.    
        Dim CustomerID As String = (TryCast(insertedItem.FindControl("txtCustomerID"), TextBox)).Text  
        Dim CompanyName As String = (TryCast(insertedItem.FindControl("txtCompanyName"), TextBox)).Text  
        Dim ContactName As String = (TryCast(insertedItem.FindControl("txtContactName"), TextBox)).Text  
        Dim Address As String = (TryCast(insertedItem.FindControl("txtAddress"), TextBox)).Text  
     
        Try 
            'Open the SqlConnection    
            SqlConnection.Open()  
            'Update Query to insert into  the database     
            Dim insertQuery As String = "INSERT into  Customers(CustomerID,CompanyName,ContactName,Address) values('" & CustomerID & "','" & CompanyName & "','" & ContactName & "','" & Address & "')" 
            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 Customers. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
     
    End Sub 
    Protected Sub RadGrid1_DeleteCommand(ByVal source As ObjectByVal e As GridCommandEventArgs)  
        'Get the GridDataItem of the RadGrid    
        Dim item As GridDataItem = DirectCast(e.Item, GridDataItem)  
        'Get the primary key value using the DataKeyValue.    
        Dim CustomerID As String = item.OwnerTableView.DataKeyValues(item.ItemIndex)("CustomerID").ToString()  
        Try 
            'Open the SqlConnection    
            SqlConnection.Open()  
            Dim deleteQuery As String = "DELETE from Customers where CustomerID='" & CustomerID & "'" 
            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 Customers. Reason: " + ex.Message))  
            e.Canceled = True 
        End Try 
    End Sub 
     
     
  2. Sebastian
    Admin
    Sebastian avatar
    9934 posts

    Posted 18 Jun 2007 Link to this post

    Hi Princy,

    Thank you the time you dedicated to assemble this demo application - it will surely be helpful for the other members of the Telerik community. It demonstrates descriptively and comprehensively how to update/insert/delete RadGrid records having SQL database for data storage. I have added 1500 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. Dheepa
    Dheepa avatar
    31 posts
    Member since:
    Oct 2007

    Posted 07 Nov 2007 Link to this post

    Hi,

    I have my grid bound a List<object> . Is there a way to get the object instance from the edit item?
  4. Sebastian
    Admin
    Sebastian avatar
    9934 posts

    Posted 07 Nov 2007 Link to this post

    Hello Dheepa,

    Try accessing the e.Item.DataItem instance inside the ItemDataBound handler of the grid to extract the data from the underlying edited record (when e.Item is GridDataItem and e.Item.IsInEditMode returns true). The approach should be similar as when using MS GridView control.

    Regards,
    Stephen
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  5. Dheepa
    Dheepa avatar
    31 posts
    Member since:
    Oct 2007

    Posted 07 Nov 2007 Link to this post

    I wrote

    protected void countriesGrid_ItemDataBound(object sender, GridItemEventArgs e)

    {

    if (e.Item is GridDataItem && e.Item.IsInEditMode)

    { // Set break point

    Country c = e.Item.DataItem as Country;

     

    if (c != null)

    {

    c.Save();

    }

    }

    }

    and set a break point on the  line which says Country c = e.Item.DataItem as Country; and the break point doesnt get hit at all. so int he item databound it never satisfies isineditmode condition.

    I am using a custom form template and in the ItemCommand event I do this
    case RadGrid.UpdateCommandName:

    GridEditableItem editedItem = e.Item as GridEditableItem;

    Country c = editedItem.DataItem as Country;

    if(c != null)
    {
        c.Save();
    }
    break
    ;

    but I always get c as null;

  6. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 08 Nov 2007 Link to this post

    Hi Dheepa,

    Generally e.Item.DataItem is available only during data-binding (ItemDataBound event) and after post-back (like update command) you should use DataKeyNames/DataKeyValues to locate desired item in your collection.

    Regards,
    Vlad
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center
  7. Developer Developer
    Developer Developer avatar
    7 posts
    Member since:
    May 2006

    Posted 12 Mar 2008 Link to this post

    I have employed the example from the page below and I am getting problems with DataKeyValues. I can't find any specific doco on what this is and how it's set. Can someone please point me in the right direction?

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



    //Get the primary key value using the DataKeyValue.
    string CustomerID = editedItem.OwnerTableView.DataKeyValues[editedItem.ItemIndex]["CustomerID"].ToString();

  8. Sebastian
    Admin
    Sebastian avatar
    9934 posts

    Posted 13 Mar 2008 Link to this post

    Hello Developer,

    These online help topics can put you on the right path:

    http://www.telerik.com/help/aspnet/grid/?RadGrid~Telerik.WebControls.GridTableView~DataKeyNames.html
    http://www.telerik.com/help/aspnet/grid/?RadGrid~Telerik.WebControls.GridTableView~DataKeyValues.html

    Best regards,
    Stephen
    the Telerik team

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