Conditional formatting when cell value is DBNull

[Ben]

Hi,

Is it possible to add conditional formatting to a column which detects when a cell value is null (DBNull)? I want to highlight cells in my grid that are mandatory but haven't yet been populated. I have tried adding a ConditionalFormattingObject with ConditionTypes.Equal and tried "", null and DBNull.Value.ToString() but none triggered the formatting.

I have managed to get this working by using the CellFormatting event (thanks to help from this forum). Here I am able to check the cell value for DBNull and apply the following formatting;

                e.CellElement.BackColor = Color.Red;

                e.CellElement.DrawFill = true;

However this paints the cell backcolour red with a fade out - i.e. not blanket red which is what I want. Is this a setting somewhere, or part of the theme? If I use conditional formatting (tested with a non-null value) the cell is painted in plain red - i.e. no fading.

Therefore I would like to get the conditional formatting working with the null values - since this gives the formatting I require.

Thanks in advance,

Ben

[Ben]

I have managed to get the CellFormatting code to paint a solid colour by setting;

                e.CellElement.GradientStyle = GradientStyles.Solid;

I would still like to know how to get the conditional formatting working with null values however.

[Fabien]

Hi

it's really easy to personnalize your colorization. let me give you a sample

As explain in it :). You can do whatever you want. (alert user?) you shall want to use EndEdit event ...

        /// <summary>
        /// On cell Begin Edit
        /// </summary>
        private void RadGridView_Volumes_CellBeginEdit(object sender, GridViewCellCancelEventArgs e)
        {

            // Cell not editable because of DBNull value or null
            if (this.RadGridView_Volumes.MasterGridViewTemplate.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == DBNull.Value ||
                this.RadGridView_Volumes.MasterGridViewTemplate.Rows[e.RowIndex].Cells[e.ColumnIndex].Value == null)
                e.Cancel = true;
        }

Ok, that's not your question, so the famous code :p

You should take care of visibility of grid (perhaps, perhaps not)

        /// <summary>
        /// On view cell formatting
        /// </summary>
        private void RadGridView_Volumes_ViewCellFormatting(object sender, CellFormattingEventArgs e)
        {

            // Ensure that GridView is available and Format cell
            if (this.RadGridView_Volumes.Visible)
                Common.FormatCell(e.CellElement);
        }

The Generic function (with a lot of personnalization, as you can see)

        /// <summary>
        /// Format a GridCellElement in a RadGridView
        /// </summary>
        /// <param name="pGridCellElement">GridCellElement criteria</param>
        public static void FormatCell(GridCellElement pGridCellElement)
        {

            // Take care of this :)
            if (pGridCellElement != null)
            {

                // By default, we must set the font, because default one is tahoma
                pGridCellElement.Font = new Font("Microsoft Sans Serif", 8.25F);

                // GridHeaderCellElement must not be formatted
                if (pGridCellElement is GridHeaderCellElement ||
                    pGridCellElement.ColumnInfo is GridViewGroupColumn ||
                    pGridCellElement is GridColumnGroupCellElement)
                    return;

                // Vista theme patch
                if (pGridCellElement.ColumnInfo is GridViewIndentColumn || pGridCellElement.ColumnInfo is GridViewRowHeaderColumn)
                {
                    pGridCellElement.ColumnInfo.IsPinned = true;
                    if (pGridCellElement.RowElement is GridDataRowElement)
                    {
                        pGridCellElement.DrawFill = true;
                        pGridCellElement.BackColor = Color.White;
                        pGridCellElement.GradientStyle = GradientStyles.Solid;
                    }
                    return;
                }

                // Custom expanders colors
                if (pGridCellElement.RowInfo is GridViewGroupRowInfo && pGridCellElement.RowElement is GridGroupHeaderRowElement)
                {
                    int level = ((GridViewGroupRowInfo)pGridCellElement.RowInfo).Group.Level;

                    if (level == 3) pGridCellElement.ForeColor = Color.FromArgb(31, 71, 79);
                    if (level == 4) pGridCellElement.ForeColor = Color.FromArgb(50, 115, 128);
                    if (level == 5) pGridCellElement.ForeColor = Color.FromArgb(70, 161, 179);

                    pGridCellElement.HorizontalLineColor = pGridCellElement.ForeColor;
                    return;
                }

                // Custom summary cells
                if (pGridCellElement is GridSummaryCellElement)
                {
                    pGridCellElement.DrawFill = true;
                    pGridCellElement.BackColor = Color.White;
                    pGridCellElement.BackColor2 = Color.FromArgb(255, 235, 173);
                    pGridCellElement.BackColor3 = Color.FromArgb(255, 215, 106);
                    pGridCellElement.BackColor4 = Color.FromArgb(255, 213, 156);
                    pGridCellElement.Font = new Font("Microsoft Sans Serif", 8.25F, FontStyle.Italic);
                    pGridCellElement.ForeColor = Color.FromArgb(0, 50, 152);
                    pGridCellElement.GradientPercentage = float.Parse("0.5015975");
                    pGridCellElement.GradientPercentage2 = float.Parse("0.5623003");
                    pGridCellElement.NumberOfColors = 4;
                    pGridCellElement.ScaleTransform = new SizeF(1, 1);

                    pGridCellElement.BorderColor = Color.FromArgb(222, 207, 156);
                    pGridCellElement.DrawBorder = true;
                    pGridCellElement.BorderGradientStyle = GradientStyles.Solid;

                    pGridCellElement.TextAlignment = pGridCellElement.ColumnIndex == 0 ? ContentAlignment.MiddleLeft : ContentAlignment.MiddleRight;

                    return;
                }

                // Conditionnal formatting (Replace Telerik conditionnal formatting)
                if (pGridCellElement.ColumnInfo is GridViewCheckBoxColumn && !(((GridDataCellElement)pGridCellElement).Value is DBNull))
                {
                    pGridCellElement.DrawFill = true;
                    pGridCellElement.BackColor = Color.FromArgb(240, 241, 243);
                    pGridCellElement.NumberOfColors = 1;
                    pGridCellElement.ForeColor = Color.FromArgb(0, 50, 152);

                    return;
                }

                // DBNull check
                if (pGridCellElement is GridDataCellElement && ((GridDataCellElement)pGridCellElement).Value is DBNull)
                    pGridCellElement.Text = "";

            }
        }

So this is a sample, you should finally have something like

/// <summary>

/// Format a GridCellElement in a RadGridView

/// </summary>

/// <param name="pGridCellElement">GridCellElement criteria</param>

public static void FormatCell(GridCellElement pGridCellElement)

{

// Take care of this :)

if (pGridCellElement != null)

{

// By default, we must set the font, because default one is tahoma

pGridCellElement.Font = new Font("Microsoft Sans Serif", 8.25F);

// GridHeaderCellElement must not be formatted

if (pGridCellElement is GridHeaderCellElement ||

pGridCellElement.ColumnInfo is GridViewGroupColumn ||

pGridCellElement is GridColumnGroupCellElement)

return;

// DBNull check

if (pGridCellElement is GridDataCellElement && ((GridDataCellElement)pGridCellElement).Value is DBNull)

{

// don't forget to draw fill :)

pGridCellElement.DrawFill = true;

pGridCellElement.BackColor = Color.FromArgb(240, 241, 243);

pGridCellElement.NumberOfColors = 1;

pGridCellElement.ForeColor = Color.FromArgb(0, 50, 152);

}             }

}

You shall want to use more colors, no problem as showed in the previous sample code, I personnalized the summary rows, and i set it like vista theme colors (as i remember). It's a gradiant color like in combobox.

Replace the style definition like this

                    pGridCellElement.DrawFill = true;
                    pGridCellElement.BackColor = Color.White;
                    pGridCellElement.BackColor2 = Color.FromArgb(255, 235, 173);
                    pGridCellElement.BackColor3 = Color.FromArgb(255, 215, 106);
                    pGridCellElement.BackColor4 = Color.FromArgb(255, 213, 156);
                    pGridCellElement.Font = new Font("Microsoft Sans Serif", 8.25F, FontStyle.Italic);
                    pGridCellElement.ForeColor = Color.FromArgb(0, 50, 152);
                    pGridCellElement.GradientPercentage = float.Parse("0.5015975");
                    pGridCellElement.GradientPercentage2 = float.Parse("0.5623003");
                    pGridCellElement.NumberOfColors = 4;
                    pGridCellElement.ScaleTransform = new SizeF(1, 1);

                    pGridCellElement.BorderColor = Color.FromArgb(222, 207, 156);
                    pGridCellElement.DrawBorder = true;
                    pGridCellElement.BorderGradientStyle = GradientStyles.Solid;

And for the end, I present you the solution with GridDataCellElement Check. You should precise the good typeof or check for a specific column name (or index with pGridCellElement.ColumnIndex == 1)


Hope this will help you.

Best regards,

 fabien

[Jack]

Fabien, thank you for the detailed suggestions - I am sure they will be very useful for our customers. Nevertheless, I would propose some small modifications.

1. Maybe it would be more convenient to check the CurrentCell directly inside the CellBeginEdit method instead of accessing it through the Rows collection of the MasterGridViewTemplate. Here is my sample:

void radGridView1_CellBeginEdit(object sender, GridViewCellCancelEventArgs e)
{
    if (this.radGridView1.CurrentCell.Value == DBNull.Value ||
        this.radGridView1.CurrentCell.Value == null)
    {
        e.Cancel = true;
    }
}

2. The ViewCellFormatting event is fired only when the grid is visible, so there is no need to check whether the Visible is set to true;

3. The Font is a windows resource, so we should be careful when using it. I suggest using a single instance instead of creating a new font every time when processing the CellFormatting event. This will optimize the performance and the memory usage of RadGridView.

Once again, thank you for sharing your code with us, Fabien - I have updated your Telerik Points.

Ben, I am glad that you have found a solution for this issue. Currently the conditional formatting in RadGridView doesn't support null values. We will consider implementing this feature in one of our upcoming releases.

If you have any other questions, I will be glad assisting you.

Sincerely yours,
Jack
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Fabien]

Hi,
thanks for CellBeginEdit and Font Tip. It will improve my application :)

But, I'm not agree with the ViewCellFormatting  call. I check this because event is fired in my application. (Perhaps I made a mystake somewhere?!?)

You said :
Currently the conditional formatting in RadGridView doesn't support null values. We will consider implementing this feature in one of our upcoming releases.

It should be useful to add Bool capability. I created the Conditionnal formatting for CheckBox columns. It's not possible to add contionnal with this sort of column. (is it?)

Thanks for your tips and tricks :)

Best regards,

Fabien

[Ben]

Thanks guys - really appreciate your time and assistance. I guess I just wanted to make sure I wasn't missing anything when trying to setup the conditional formatting for a null value - I see now this isn't possible so will stick with my alternate solution. 

Regards,

Ben

[Jack]

Hi Fabien,

It is possible to add conditional formatting to checkbox columns. All you need to do is set the TValue1 property to "true" or "false".

Maybe the ViewCellFormatting is fired in some boundary situation. In general it is fired only when RadGridView is visible on screen.

 
Hi Ben, I am glad to hear that you have found an alternative solution. Please do not hesitate to write us if you have questions.
 

Kind regards,
Jack
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Randy]

I am experiencing an issue with the RadGridView control when I sort a column and then scroll up and down. Many of the columns in my table do not have a value in all cells... some cells are empty, or DBNull. When I sort by such a column, scrolling works smoothly as long as the visible part of the sorted column contains values. When I scroll while the visible part of the sorted column contains no values, scrolling becomes very jerky.

It almost seems like as I scroll and the control decides what rows to display next based on the sorted column's values, it's doing a comparison of the values and is hiccupping when it gets to the DBNull values...

I decided to post here because at first, the issue seemed related to the original poster's issue, but now I'm not so sure...

-----

I just noticed that I installed WinControls 2008 Q2, so I installed the latest 2008 Q3 SP2 build and will see if the issue has already been resolved.

-----

Nevermind, I've opened up a support ticket for this issue instead.

[Jack]

Hello Daniel,

You can find the answer to this question in your support ticket with the same issue. If you have any further questions, please do not hesitate to write us.

Regards,
Jack
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.