Tuesday, 15 July 2014

Telerik RadGrid - Change Cell Value based on another Cell value in ASP.NET using C#/VB.NET

Hi Friends,in this article I will explain about Telerik RadGrid - Change Cell Value based on another Cell value in ASP.NET using C#/VB.NET or change values in radgrid cell according to changes in another cell.
I already explained in the previous articles about Batch Editing(Edit/Insert/Update/Delete) in telerik RadGrid in ASP.NET using C#/VB.NETHow to Bind the Dataset to telerik RadGrid in ASP.NET using C#/VB.NET and Telerik RadGrid Grouping -Drag and Drop a column header to group by that column in ASP.NET using C#/VB.NET

Before going to use Telerik ,first  download the Telerik dll’s from Telerik site or any another websites.
ASP.NET:
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Telerik RadGrid - Change Cell Value based on another Cell value in ASP.NET using C#/VB.NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <telerik:RadScriptManager ID="RadScriptManager1" runat="server" />
        <telerik:RadGrid ID="RadGridDetails" GridLines="None" runat="server" AllowSorting="true"
            AllowMultiRowEdit="True" OnBatchEditCommand="RadGridDetails_BatchEditCommand"
            AllowAutomaticUpdates="True" AllowAutomaticInserts="true" AllowAutomaticDeletes="true"
            PageSize="10" AllowPaging="True" OnNeedDataSource="RadGridDetails_NeedDataSource"
            AllowActiveRowCycle="true" AutoGenerateColumns="False" AllowMultiRowSelection="true"
            Width="750px" Skin="Sunset">
            <HeaderContextMenu EnableTheming="True">
                <CollapseAnimation Type="OutQuint" Duration="200"></CollapseAnimation>
            </HeaderContextMenu>
            <ClientSettings>
                <ClientEvents OnBatchEditCellValueChanging="RadGridAdditions_OnBatchEditCellValueChanging" />
                <Selecting AllowRowSelect="true" />
            </ClientSettings>
            <MasterTableView CommandItemDisplay="TopAndBottom" DataKeyNames="StudentID" HorizontalAlign="NotSet"
                EditMode="Batch" AutoGenerateColumns="False">
                <BatchEditingSettings EditType="Cell" OpenEditingEvent="Click" />
                <SortExpressions>
                    <telerik:GridSortExpression FieldName="StudentID" SortOrder="Ascending" />
                </SortExpressions>
                <Columns>
                    <telerik:GridClientSelectColumn UniqueName="ClientSelectColumn">
                    </telerik:GridClientSelectColumn>
                    <telerik:GridBoundColumn DataField="StudentID" HeaderStyle-Width="135px" HeaderText="Student ID"
                        ReadOnly="true" SortExpression="StudentID" UniqueName="StudentID">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="StudentName" HeaderStyle-Width="80px" HeaderText="Student Name"
                        SortExpression="StudentName" UniqueName="StudentName">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="MATHS" HeaderStyle-Width="80px" HeaderText="MATHS"
                        SortExpression="MATHS" UniqueName="MATHS">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="SCIENCE" HeaderStyle-Width="80px" HeaderText="SCIENCE"
                        SortExpression="SCIENCE" UniqueName="SCIENCE">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="SOCIAL" HeaderStyle-Width="80px" HeaderText="SOCIAL"
                        SortExpression="SOCIAL" UniqueName="SOCIAL">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="Total" HeaderStyle-Width="80px" HeaderText="Total"
                        SortExpression="Total" UniqueName="Total">
                    </telerik:GridBoundColumn>
                    <telerik:GridButtonColumn ConfirmText="Delete this user?" ConfirmDialogType="RadWindow"
                        ConfirmTitle="Delete" HeaderText="Delete" HeaderStyle-Width="50px" ButtonType="ImageButton"
                        CommandName="Delete" Text="Delete" UniqueName="DeleteColumn">
                    </telerik:GridButtonColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
        <telerik:RadScriptBlock runat="server">
            <script type="text/javascript">
                function RadGridAdditions_OnBatchEditCellValueChanging(sender, args) {
                    var data = sender.get_batchEditingManager()._getCellDataToOpenEdit(sender.get_batchEditingManager().get_currentlyEditedCell());
                    //var row = data.row;
                    //var rowIndex = row.rowIndex;
                    var columnName = data.columnUniqueName;
                    var oldValue = args.get_cellValue();
                    var newValue = args.get_editorValue();
                    var cell = data.cell;
                    var grid = $find('<%= RadGridDetails.ClientID %>');
                    var masterTable = grid.get_masterTableView();
                    var rows = masterTable.get_dataItems();
                    var rowArgs = args.get_row();
                    var rowIndex = rowArgs.sectionRowIndex;
                    var row = rows[rowIndex];
                    if (columnName == "MATHS" || columnName == "SCIENCE" || columnName == "SOCIAL") {
                        var txtTotal = row.get_cell("Total");
                        var txtScience = sender.get_masterTableView()._getCellByColumnUniqueNameFromTableRowElement(args.get_row(), "SCIENCE");
                        var txtScienceValue = sender.get_batchEditingManager().getCellValue(txtScience);
                        var txtSOCIAL = sender.get_masterTableView()._getCellByColumnUniqueNameFromTableRowElement(args.get_row(), "SOCIAL");
                        var txtSOCIALValue = sender.get_batchEditingManager().getCellValue(txtScience);
                        var total = parseInt(newValue) + parseInt(txtScienceValue) + parseInt(txtSOCIALValue);
                        grid.get_batchEditingManager().changeCellValue(txtTotal, total);
                    }
                }
            </script>
        </telerik:RadScriptBlock>
    </div>
    </form>
</body>
</html>

In C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using Telerik.Web.UI;
using System.Configuration;
using System.Data;
using System.Collections;

public partial class RadGrid : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGrid();
        }
    }
    protected void RadGridDetails_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        SqlCommand cmd = new SqlCommand("Select * from MARKS", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        RadGridDetails.DataSource = ds;
    }
    protected void BindGrid()
    {
        SqlCommand cmd = new SqlCommand("Select * from MARKS", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        RadGridDetails.DataSource = ds.Tables[0];
        RadGridDetails.DataBind();
    }
    protected void RadGridDetails_BatchEditCommand(object sender, GridBatchEditingEventArgs e)
    {

        foreach (GridBatchEditingCommand command in e.Commands)
        {
            Hashtable oldValues = command.OldValues;
            Hashtable newValues = command.NewValues;
            string StudentName = newValues["StudentName"] == null ? null : newValues["StudentName"].ToString();
            string MATHS = newValues["MATHS"] == null ? null : newValues["MATHS"].ToString();
            string SCIENCE = newValues["SCIENCE"] == null ? null : newValues["SCIENCE"].ToString();
            string SOCIAL = newValues["SOCIAL"] == null ? null : newValues["SOCIAL"].ToString();
            string Total = newValues["Total"] == null ? null : newValues["Total"].ToString();
            string StudentID = newValues["StudentID"] == null ? null : newValues["StudentID"].ToString();
            if (command.Type == GridBatchEditingCommandType.Update)
            {
                string str = "UPDATE MARKS SET StudentName='" + StudentName + "',MATHS='" + MATHS + "',SCIENCE='" + SCIENCE + "',SOCIAL='" + SOCIAL + "',Total='" + Total + "'  WHERE StudentID= " + StudentID;
                SqlCommand cmd = new SqlCommand(str, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            else if (command.Type == GridBatchEditingCommandType.Insert)
            {
                string str = "INSERT INTO MARKS(StudentName,MATHS,SCIENCE,SOCIAL,Total) VALUES('" + StudentName + "','" + MATHS + "','" + SCIENCE + "','" + SOCIAL + "','" + Total + "' )";

                SqlCommand cmd = new SqlCommand(str, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }

            else if (command.Type == GridBatchEditingCommandType.Delete)
            {
                string User_ID = newValues["StudentID"] == null ? null : newValues["StudentID"].ToString();
                string str = "DELETE FROM MARKS WHERE StudentID= " + StudentID;

                SqlCommand cmd = new SqlCommand(str, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();

            }
        }
    }

}

In VB.NET:

Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports Telerik.Web.UI
Imports System.Configuration
Imports System.Data
Imports System.Collections

Partial Public Class RadGrid
    Inherits System.Web.UI.Page
    Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)

    Protected Sub Page_Load(sender As Object, e As EventArgs)
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    Protected Sub RadGridDetails_NeedDataSource(sender As Object, e As GridNeedDataSourceEventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ConnectionString)
        Dim cmd As New SqlCommand("Select * from MARKS", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        RadGridDetails.DataSource = ds
    End Sub
    Protected Sub BindGrid()
        Dim cmd As New SqlCommand("Select * from MARKS", con)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        RadGridDetails.DataSource = ds.Tables(0)
        RadGridDetails.DataBind()
    End Sub
    Protected Sub RadGridDetails_BatchEditCommand(sender As Object, e As GridBatchEditingEventArgs)

        For Each command As GridBatchEditingCommand In e.Commands
            Dim oldValues As Hashtable = command.OldValues
            Dim newValues As Hashtable = command.NewValues
            Dim StudentName As String = If(newValues("StudentName") Is Nothing, Nothing, newValues("StudentName").ToString())
            Dim MATHS As String = If(newValues("MATHS") Is Nothing, Nothing, newValues("MATHS").ToString())
            Dim SCIENCE As String = If(newValues("SCIENCE") Is Nothing, Nothing, newValues("SCIENCE").ToString())
            Dim SOCIAL As String = If(newValues("SOCIAL") Is Nothing, Nothing, newValues("SOCIAL").ToString())
            Dim Total As String = If(newValues("Total") Is Nothing, Nothing, newValues("Total").ToString())
            Dim StudentID As String = If(newValues("StudentID") Is Nothing, Nothing, newValues("StudentID").ToString())
            If command.Type = GridBatchEditingCommandType.Update Then
                Dim str As String = Convert.ToString((Convert.ToString((_
Convert.ToString((Convert.ToString((Convert.ToString((Convert.ToString("UPDATE MARKS SET StudentName='") & StudentName) +_
 "',MATHS='") & MATHS) + "',SCIENCE='") & SCIENCE) +_
 "',SOCIAL='") & SOCIAL) + "',Total='") & Total) + "'  WHERE StudentID= ") & StudentID
                Dim cmd As New SqlCommand(str, con)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()
            ElseIf command.Type = GridBatchEditingCommandType.Insert Then
                Dim str As String = (Convert.ToString((Convert.ToString((Convert.ToString((Convert.ToString((Convert.ToString("INSERT INTO MARKS(StudentName,MATHS,SCIENCE,SOCIAL,Total) VALUES('") & StudentName) + "','") & MATHS) + "','") & SCIENCE) + "','") & SOCIAL) + "','") & Total) + "' )"

                Dim cmd As New SqlCommand(str, con)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()
                cmd.Connection.Close()

            ElseIf command.Type = GridBatchEditingCommandType.Delete Then
                Dim User_ID As String = If(newValues("StudentID") Is Nothing, Nothing, newValues("StudentID").ToString())
                Dim str As String = Convert.ToString("DELETE FROM MARKS WHERE StudentID= ") & StudentID

                Dim cmd As New SqlCommand(str, con)
                cmd.Connection.Open()
                cmd.ExecuteNonQuery()

                cmd.Connection.Close()
            End If
        Next
    End Sub

End Class


The output of the above code as shown in the below figure.

After changing the values in the first row the output will be as shown in the below figure.The changed cells will be have red mark in the left top side of the cell.

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.