Problem with Expression in column

[Javier Gonzalez de Aragon]

Hi. I have a datagrid that has a data source added programatically. I need a column to calculate an expression based on the data, but it is not working. Is it possible to combine expressions in data gridviews? Below is a snippet of my code.

Thanks.

Javier

 

 

With gvEntregaDetalle
    .MasterTemplate.AllowAddNewRow = False
    .MasterTemplate.AutoGenerateColumns = False
    .MasterTemplate.AllowDeleteRow = False
    .TableElement.BeginUpdate()
    .Columns("Pendiente").Expression = "Cantidad-Surtido"
    .MasterTemplate.LoadFrom(sqlDR)
    .TableElement.EndUpdate()
End With

 

[Richard Slade]

Hola Javier,

Here is an example of using a calculated column. Please let me know if you need further assistance.

Imports Telerik.WinControls
Imports Telerik.WinControls.UI
Imports System.ComponentModel


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object,
                           ByVal e As System.EventArgs) Handles MyBase.Load


        Dim people As New List(Of MyObject)
        people.Add(New MyObject("name 1", 10, 10))
        people.Add(New MyObject("name 2", 20, 20))
        people.Add(New MyObject("name 3", 30, 30))
        people.Add(New MyObject("name 4", 40, 40))
        people.Add(New MyObject("name 5", 50, 50))
        people.Add(New MyObject("name 6", 60, 60))

        Me.RadGridView1.DataSource = people

        Dim col = New GridViewDecimalColumn()
        col.Name = "Calculated Column"
        col.HeaderText = "Total"
        RadGridView1.Columns.Add(col)
        RadGridView1.Columns("Calculated Column").Expression = "Number1 + Number2"
    End Sub

End Class


Public Class MyObject
    Public Sub New(ByVal name As String, ByVal number1 As Integer, ByVal number2 As Integer)
        Me.Name = name
        Me.Number1 = number1
        Me.Number2 = number2
    End Sub

    Public Property Name As String
    Public Property Number1 As Integer
    Public Property Number2 As Integer
End Class

Regards,
Richard

[Javier Gonzalez de Aragon]

Hi Richard, thanks for the prompt answer. I tried to do what you suggested, but the result was the same. The calculated column shows blanks. All of the other columns populate fine. My data source is a SQL DataReader. Maybe that's causing the problem. Please advice.

Thanks,

Javier

[Richard Slade]

Hi Javier, 

If your other columns work fine, then the calculated column should be there. Have you tried my example above, and does that one work ok for you? 
If so, please can you confirm the version of the RadControls that you are using. 
Thanks
Richard

[Emanuel Varga]

Hello Javier, Richard,

I had some problems in the past with calculated columns and hierarchy but apparently all of these have been fixed in the current version.

Please try the following and let me know if you have any problems with this:

using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using Telerik.WinControls.UI;

public partial class Form1 : Form
{
    private RadGridView radGridView1;
    private RadGridView radGridView2;
    private List<SelfReferencingPerson> selfReferencingPersons;
    private List<Parent> parents;
    private List<Child> children;

    public Form1()
    {
        InitializeComponent();

        selfReferencingPersons = new List<SelfReferencingPerson>();
        selfReferencingPersons.Add(new SelfReferencingPerson(1, 0, "Father1", 1920));
        selfReferencingPersons.Add(new SelfReferencingPerson(2, 0, "Father2", 1927));
        selfReferencingPersons.Add(new SelfReferencingPerson(3, 1, "Child1", 1980));
        selfReferencingPersons.Add(new SelfReferencingPerson(4, 1, "Child2", 1984));
        selfReferencingPersons.Add(new SelfReferencingPerson(5, 2, "Child3", 1982));

        parents = new List<Parent>();
        parents.Add(new Parent(1, "Father1", 1920));
        parents.Add(new Parent(2, "Father2", 1927));

        children = new List<Child>();
        children.Add(new Child(3, 1, "Child1", 1980));
        children.Add(new Child(4, 1, "Child2", 1984));
        children.Add(new Child(5, 2, "Child3", 1982));

        this.Size = new Size(600, 600);
        var tableLayoutPanel = new TableLayoutPanel();
        tableLayoutPanel.RowStyles.Add(new RowStyle(SizeType.Percent, 50f));
        tableLayoutPanel.RowStyles.Add(new RowStyle(SizeType.Percent, 50f));
        tableLayoutPanel.Dock = DockStyle.Fill;

        tableLayoutPanel.Controls.Add(radGridView1 = new RadGridView(), 0, 0);
        radGridView1.Dock = DockStyle.Fill;
        radGridView1.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
        radGridView1.DataBindingComplete += new GridViewBindingCompleteEventHandler(radGridView1_DataBindingComplete);

        var column = new GridViewDecimalColumn("Age");
        column.Expression = "2011 - YearOfBirth";
        this.radGridView1.Columns.Add(column);
        this.radGridView1.Relations.AddSelfReference(this.radGridView1.MasterTemplate, "Id", "ParentId");
        this.radGridView1.DataSource = selfReferencingPersons;

        tableLayoutPanel.Controls.Add(radGridView2 = new RadGridView(), 0, 1);
        radGridView2.Dock = DockStyle.Fill;
        radGridView2.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
        radGridView2.DataBindingComplete += new GridViewBindingCompleteEventHandler(radGridView1_DataBindingComplete);

        column = new GridViewDecimalColumn("Age");
        column.Expression = "2011 - YearOfBirth";
        this.radGridView2.Columns.Add(column);

        var template = new GridViewTemplate();
        template.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
        template.DataSource = children;
        column = new GridViewDecimalColumn("Age");
        column.Expression = "2011 - YearOfBirth";
        template.Columns.Add(column);
        var relation = new GridViewRelation(radGridView2.MasterTemplate);
        relation.ChildTemplate = template;
        relation.ChildColumnNames.Add("ParentId");
        relation.ParentColumnNames.Add("Id");
        radGridView2.Relations.Add(relation);

        radGridView2.DataSource = parents;
        radGridView2.Templates.Add(template);

        this.Controls.Add(tableLayoutPanel);
    }

    void radGridView1_DataBindingComplete(object sender, GridViewBindingCompleteEventArgs e)
    {
        var grid = sender as RadGridView;

        if (grid.Templates.Count != 0)
        {
            grid.Templates[0].Columns.Move(grid.Templates[0].Columns.IndexOf("Age"), grid.Templates[0].Columns.Count - 1);
            grid.Templates[0].BestFitColumns();
        }

        grid.Columns.Move(grid.Columns.IndexOf("Age"), grid.Columns.Count - 1);

        grid.BestFitColumns();
    }
}

public class SelfReferencingPerson
{
    public int Id
    {
        get;
        set;
    }

    public int ParentId
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    public int YearOfBirth
    {
        get;
        set;
    }

    public SelfReferencingPerson()
    {
    }

    public SelfReferencingPerson(int id, int parentId, string name, int yearOfBirth)
    {
        this.Id = id;
        this.ParentId = parentId;
        this.Name = name;
        this.YearOfBirth = yearOfBirth;
    }
}

public class Parent
{
    public int Id
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    public int YearOfBirth
    {
        get;
        set;
    }

    public Parent()
    {
    }

    public Parent(int id, string name, int yearOfBirth)
    {
        this.Id = id;
        this.Name = name;
        this.YearOfBirth = yearOfBirth;
    }
}

public class Child
{
    public int Id
    {
        get;
        set;
    }

    public int ParentId
    {
        get;
        set;
    }

    public string Name
    {
        get;
        set;
    }

    public int YearOfBirth
    {
        get;
        set;
    }

    public Child()
    {
    }

    public Child(int id, int parentId, string name, int yearOfBirth)
    {
        this.Id = id;
        this.ParentId = parentId;
        this.Name = name;
        this.YearOfBirth = yearOfBirth;
    }
}

Here there are both types of hierarchy self referencing and normal hierarchy,

Hope this helps, if you have any other questions or comments, please let me know,

Best Regards,
Emanuel Varga
Telerik WinForms MVP

[Gabriele]

Thanks Emanuel,

great examples!

I have an issue related to the selfreferencing.

I am using the 2 tables below in my database, the table groups is the one that I can automatize using self referencing as your example explains and it works perfect, I have all the nodes and subnodes perfectly created following the table rows structure.

Now the problem comes when I need to list every plan under its own node, the relation between the plans table and the groups table is the group_id but I am not able to reference that table the the mastertemplate or in any other way.

Any suggestions?

Thanks,

-Gabriele
 

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL DEFAULT '0',
  `parent` int(11) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `groups` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `groups` (`id`, `parent`, `description`) VALUES
    (1, NULL, 'asdasda'),
    (2, NULL, 'dfgdfgdf'),
    (3, 1, 'hjghjghj'),
    (4, 2, 'l;k;kl;kl;'),
    (5, NULL, 'New Main Node'),
    (6, 5, 'New Sub Main Node'),
    (7, 6, 'blablabla'),
    (8, 7, 'Another Level'),
    (9, NULL, 'No Plans on this');

CREATE TABLE IF NOT EXISTS `plans` (
  `plan_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`plan_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `plans` (`plan_id`, `group_id`, `description`) VALUES
    (1, 1, 'aaaaaaaa'),
    (2, 3, 'bbbbbbbb'),
    (3, 4, 'ccccccccccc'),
    (4, 2, 'ddddddddd'),
    (5, 3, 'eeeeeeeee'),
    (6, 4, 'fffffffff'),
    (7, 7, 'ggggggggggg');

[Julian Benkov]

Hi Gabriele,

To add the 'plans' data in you case to RadGridView control, you can create a second GridViewTemplate and a Relation. Here is a sample:

GridViewTemplate template = new GridViewTemplate();
template.DataSource = plans;
this.radGridView1.Templates.Add(template);

GridViewRelation relation = new GridViewRelation(radGridView1.MasterTemplate, template);
relation.RelationName = "GroupsPlans";
relation.ParentColumnNames.Add("id");
relation.ChildColumnNames.Add("group_id");
this.radGridView1.Relations.Add(relation);

I hope this was helpful.

Kind regards,
Julian Benkov
the Telerik team

[Gabriele]

Hi Julian,

I tried your suggestion with no results, attached is the screenshot fo the resutls I get.
A second column is created with the plus/minus sign and the plans are not showing, here is my code, do you see something wrong?

Thanks,

-Gabriele

private void loadradGridView3()
{
radGridView3.BeginInit();
radGridView3.DataSource = null;
radGridView3.Columns.Clear();
radGridView3.Rows.Clear();
radGridView3.AutoGenerateHierarchy = true;

DataTable DataToReadTestSuites = CreateDataTableTestSuites();
DataTable DataToReadTestPlans = CreateDataTableTestPlans();

DataSet  dsGroups = new DataSet("dsGroups");
dsGroups.Tables.Add(DataToReadTestSuites);
dsGroups.Tables.Add(DataToReadTestPlans);

radGridView3.AutoGenerateHierarchy = true;

dsGroups = new DataSet("dsGroups");
dsGroups.Tables.Add(DataToReadTestSuites);
dsGroups.Tables.Add(DataToReadTestPlans);
radGridView3.AutoGenerateHierarchy = true;

radGridView3.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
radGridView3.DataSource = dsGroups.Tables["groups"];
radGridView3.Relations.AddSelfReference(this.radGridView3.MasterTemplate, "id", "parent");
radGridView3.MasterTemplate.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
radGridView3.ShowColumnHeaders = true;
radGridView3.MasterTemplate.Columns["id"].IsVisible = true;
radGridView3.MasterTemplate.Columns["parent"].IsVisible = true;
radGridView3.MasterTemplate.Columns["id"].MaxWidth = 100;
radGridView3.MasterTemplate.Columns["parent"].MaxWidth = 100;

GridViewTemplate template = new GridViewTemplate();
template.DataSource = dsGroups.Tables["plans"];
this.radGridView3.Templates.Add(template);

GridViewRelation relation = new GridViewRelation(radGridView3.MasterTemplate, template);
relation.RelationName = "GroupsPlans";
relation.ParentColumnNames.Add("id");
relation.ChildColumnNames.Add("group_id");
this.radGridView3.Relations.Add(relation);

radGridView3.EndInit();
}


        private DataTable CreateDataTableTestSuites()
        {
            DataTable returnTable = new DataTable();

            string query = "SELECT"
                + " groups.id,"
                + " groups.parent,"
                + " groups.description"
                + " FROM groups";

            MySqlConnection connection = new MySqlConnection();
            connection.ConnectionString = myCommon.ConnectionString;


            using (MySqlCommand command = new MySqlCommand(query))
            {
                command.Connection = connection;
                command.Connection.Open();
                try
                {
                    returnTable.BeginLoadData();
                    returnTable.Load(command.ExecuteReader());
                    returnTable.EndLoadData();
                }
                catch (Exception e)
                {
                    returnTable = null;
                }
            }
            return returnTable;
        }


        private DataTable CreateDataTableTestPlans()
        {
            DataTable returnTable = new DataTable();

            query = "SELECT"
                + " plans.plan_id,"
                + " plans.group_id,"
                + " plans.description"
                + " FROM plans ";

            MySqlConnection connection = new MySqlConnection();
            connection.ConnectionString = myCommon.ConnectionString;


            using (MySqlCommand command = new MySqlCommand(query))
            {
                command.Connection = connection;
                command.Connection.Open();
                try
                {
                    returnTable.BeginLoadData();
                    returnTable.Load(command.ExecuteReader());
                    returnTable.EndLoadData();
                }
                catch (Exception e)
                {
                    returnTable = null;
                }
            }
            return returnTable;
        }

[Julian Benkov]

Hi Gabriele,

The issue is that in your code you are mixing AuoGenerateHierarchy property with self reference mode and relation hierarchy mode settings. The best solution is this case is to use only the manually created relational hierarchy. Here is the changed code snippet:

private void loadradGridView3()
{
    radGridView3.DataSource = null;
    radGridView3.Columns.Clear();
    radGridView3.Rows.Clear();

    radGridView3.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
    radGridView3.DataSource = CreateDataTableTestSuites();

    radGridView3.ShowColumnHeaders = true;
    radGridView3.MasterTemplate.Columns["id"].IsVisible = true;
    radGridView3.MasterTemplate.Columns["parent"].IsVisible = true;
    radGridView3.MasterTemplate.Columns["id"].MaxWidth = 100;
    radGridView3.MasterTemplate.Columns["parent"].MaxWidth = 100;

    GridViewTemplate template = new GridViewTemplate();
    template.DataSource = CreateDataTableTestPlans();
    this.radGridView3.Templates.Add(template);

    GridViewRelation relation = new GridViewRelation(radGridView3.MasterTemplate, template);
    relation.RelationName = "GroupsPlans";
    relation.ParentColumnNames.Add("id");
    relation.ChildColumnNames.Add("group_id");
    this.radGridView3.Relations.Add(relation);
}

I hope this helps.

Regards,
Julian Benkov
the Telerik team

[Gabriele]

Still does not work, I get the same results as my previous post.
use the sample code below to see the resutls I get. The solution for this issue is critical for my project.

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using Telerik.WinControls;
using Telerik.WinControls.UI;

namespace DnsTest.Forms
{
    public partial class TestForm : Telerik.WinControls.UI.RadForm
    {
        public TestForm()
        {
            InitializeComponent();
        }

        void TestForm_Load(object sender, System.EventArgs e)
        {
            loadradGridView3();
        }

        private void loadradGridView3()
        {
            //radGridView3.BeginInit();
            radGridView3.DataSource = null;
            radGridView3.Columns.Clear();
            radGridView3.Rows.Clear();


            radGridView3.AutoSizeColumnsMode = GridViewAutoSizeColumnsMode.Fill;
            radGridView3.DataSource = CreateDataSourceGroups();

            radGridView3.Relations.AddSelfReference(radGridView3.MasterTemplate, "ID", "ParentID");

            radGridView3.ShowColumnHeaders = true;

            GridViewTemplate template = new GridViewTemplate();
            template.DataSource = CreateDataSourcePlans();
            this.radGridView3.Templates.Add(template);

            GridViewRelation relation = new GridViewRelation(radGridView3.MasterTemplate, template);
            relation.RelationName = "GroupsPlans";
            relation.ParentColumnNames.Add("ID");
            relation.ChildColumnNames.Add("GroupID");
            this.radGridView3.Relations.Add(relation);
            radGridView3.EndInit();

            radGridView3.EndInit();
        }

        private static DataTable CreateDataSourceGroups()
        {
            DataTable dataSource = new DataTable("groups");
            dataSource.Columns.Add("ID", typeof(int));
            dataSource.Columns.Add("ParentID", typeof(int));
            dataSource.Columns.Add("Name", typeof(string));

            dataSource.Rows.Add(1, null, "Main Group 1");
            dataSource.Rows.Add(2, 1, "Sub Group 1 1");
            dataSource.Rows.Add(3, 1, "Sub Group 1 2");
            dataSource.Rows.Add(4, 1, "Sub Group 1 3");

            dataSource.Rows.Add(5, null, "Main Group 2");
            dataSource.Rows.Add(6, 5, "Sub Group 2 1");
            dataSource.Rows.Add(7, 6, "Sub Group 2 1 1");
            dataSource.Rows.Add(8, 7, "Sub Group 2 1 1 1");
            dataSource.Rows.Add(9, 8, "Sub Group 2 1 1 1 1");

            return dataSource;
        }

        private static DataTable CreateDataSourcePlans()
        {
            DataTable dataSource = new DataTable("plans");
            dataSource.Columns.Add("ID", typeof(int));
            dataSource.Columns.Add("GroupID", typeof(int));
            dataSource.Columns.Add("Name", typeof(string));

            dataSource.Rows.Add(1, 2, "Plan 1 Under Sub Group 1 1");
            dataSource.Rows.Add(2, 2, "Plan 2 Under Sub Group 1 1");
            dataSource.Rows.Add(3, 4, "Plan 3 Under Sub Group 1 3");

            dataSource.Rows.Add(4, 5, "Plan 4 Under Main Group 2");
            dataSource.Rows.Add(5, 6, "Plan 5 Under Sub Group 2 1");
            dataSource.Rows.Add(6, 9, "Plan 6 Under Sub Group 2 3 1");

            return dataSource;
        }
    }
}

-Gabriele

[Julian Benkov]

Hi Gabriele,

Please view the answer in your previous support ticket. If you have further need of assistance, I would be glad to provide it.

 Regards,
Julian Benkov
the Telerik team