Manual Insert/Update/Delete using FormTemplate and Sql backend in UI for ASP.NET AJAX Grid

E69AACB4-3C5B-42BC-9BDD-D0F081D8FE34

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 Object, ByVal 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 Object, ByVal 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 Object, ByVal 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 Object, ByVal 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

ManualInsertUpdateUsingFormTemplate.zip

C7498A83-7E2E-418C-8791-93EF573A7569

9934 posts

Member since:
Nov 2016

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

B4A52C0B-33FA-4ECF-B2A2-BB4FBE1DDCAF

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?

C7498A83-7E2E-418C-8791-93EF573A7569

9934 posts

Member since:
Nov 2016

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

B4A52C0B-33FA-4ECF-B2A2-BB4FBE1DDCAF

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;

6AB3838B-B392-4EDE-97F0-B3F5916D8900

11100 posts

Member since:
Jan 2017

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

292E9B83-7606-4675-BD46-804914E8004B

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();

C7498A83-7E2E-418C-8791-93EF573A7569

9934 posts

Member since:
Nov 2016

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

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Manual Insert/Update/Delete using FormTemplate and Sql backend

E69AACB4-3C5B-42BC-9BDD-D0F081D8FE34

C7498A83-7E2E-418C-8791-93EF573A7569

B4A52C0B-33FA-4ECF-B2A2-BB4FBE1DDCAF

C7498A83-7E2E-418C-8791-93EF573A7569

B4A52C0B-33FA-4ECF-B2A2-BB4FBE1DDCAF

6AB3838B-B392-4EDE-97F0-B3F5916D8900

292E9B83-7606-4675-BD46-804914E8004B

C7498A83-7E2E-418C-8791-93EF573A7569