Gridview conditional format - % or decimal

Question

User-1314346660 posted

Hello experts,

I have used some code to conditional format cells and rows in a gridview. The thread is here: https://forums.asp.net/t/2134372.aspx?Grid+view+conditional+format+

I am struggling to get this working for a column where the data is a percent value. My data looks like this:

CostCentreHierachy	StaffDue	StaffDueTarget	Consent_Yes	Consent_No	Incomplete	% Compliance	StaffDueTargetRemain
Adults East	806	605	1	0	805	0.001241	604
Estates	4	3	0	0	4	0	3
Finance	60	45	0	0	60	0	45
HR	27	21	0	0	27	0	21
Management	16	12	0	0	16	0	12
Operations	14	11	0	0	14	0	11
Quality	56	42	0	0	56	0	42

And here is my code:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                int a = Convert.ToInt32(e.Row.Cells[3].Text);
                int b = Convert.ToInt32(e.Row.Cells[6].Text);
                if (a <= 0)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Tomato;
                }
                else
                if (b < 0.5M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.LightGreen;
                    
                }
                else
                if (b >= 0.5M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Beige;
                }
                else
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Orange;
                }
            }
        }

I am trying to set the colour of column 6 % compliance. I would be really grateful if someone could help me with this. I think this is to do with managing decimal values in the code but I cant seem to work it out!

Thanks very much in advance.

Billson3000

Answer 1

User475983607 posted

You are comparing an integer with a decimal.   By definition integers are whole numbers without decimals.

https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/int

Maybe you really need decimals?

https://msdn.microsoft.com/en-us/library/system.convert.todecimal(v=vs.110).aspx

decimal a = Convert.ToDecimal(e.Row.Cells[3].Text);
decimal b = Convert.ToDecimal(e.Row.Cells[6].Text);

Answer 2

User-1314346660 posted

Thanks MGE.

I did try that but the page errors on load. I get this message when i try and convert to decimal....

Server Error in '/' Application.

---

Input string was not in a correct format.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:

Line 20: { Line 21: Decimal a = Convert.ToDecimal(e.Row.Cells[3].Text); Line 22: Decimal b = Convert.ToDecimal(e.Row.Cells[6].Text); Line 23: if (a <= 0) Line 24: {

Source File: C:\Users\Billy\source\repos\StaffFlu\StaffFlu\R1Compliance.aspx.cs    Line: 22

Stack Trace:

[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) [phone] System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt) +131 System.Convert.ToDecimal(String value) +77 StaffFlu.R1Compliance.GridView1_RowDataBound(Object sender, GridViewRowEventArgs e) in C:\Users\Billy\source\repos\StaffFlu\StaffFlu\R1Compliance.aspx.cs:22 System.Web.UI.WebControls.GridView.OnRowDataBound(GridViewRowEventArgs e) +111 System.Web.UI.WebControls.GridView.CreateRow(Int32 rowIndex, Int32 dataSourceIndex, DataControlRowType rowType, DataControlRowState rowState, Boolean dataBind, Object dataItem, DataControlField[] fields, TableRowCollection rows, PagedDataSource pagedDataSource) +181 System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +3741 System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +67 System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +14 System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +128 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +34 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +143 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +74 System.Web.UI.WebControls.GridView.DataBind() +9 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +114 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +75 System.Web.UI.Control.EnsureChildControls() +92 System.Web.UI.Control.PreRenderRecursiveInternal() +42 System.Web.UI.Control.PreRenderRecursiveInternal() +160 System.Web.UI.Control.PreRenderRecursiveInternal() +160 System.Web.UI.Control.PreRenderRecursiveInternal() +160 System.Web.UI.Control.PreRenderRecursiveInternal() +160 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +883

 

---

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.2633.0

Answer 3

User475983607 posted

The basic issue is you are comparing an integer to a decimal.  Since we cannot see you code, you'll need to figure out where your design went south by taking advantage of the wonderfully useful Visual Studio debugger.  You should be able to find the bug if you set a breakpoint and single-step through the code.

It is a bit perplexing that an integer parses correctly but a decimal does not.  Usually this error means you have a non numeric character in the string which should fail for an integer as well. Anyway I don't see anything in the table that would cause this.

Answer 4

User-1716253493 posted

decimal b = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "complient"));

Answer 5

User-1314346660 posted

Thanks. Its evaluating and running now but something must be wrong somewhere. The cells all turn green when i use this code:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                decimal b = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "% Compliance"));
                if (b > 0.75M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Green;
                }
                else
                if (b < 0.5M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Tomato;
                }
                else
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Orange;
                }
            }
        }

But I clearly have some data that is less than 50% (Tomato) or is between 51-74% (orange). Feels like there are some gremlins in here! Here is my data...

Business Unit	Staff Due	Target	Consent Yes	Consent No	Incomplete	% Compliance	Target Remain
Estates	4	3	1	0	3	25.00%	3
Finance	60	45	48	0	12	80.00%	-3
Management	16	12	12	0	4	75.00%	0
HR	27	21	22	0	5	81.50%	-1
Operations	16	12	15	0	1	93.80%	-3
Quality	56	42	38	0	18	67.90%	4

Answer 6

User475983607 posted

You are still comparing an integer to a decimal.

decimal b = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "% Compliance"));

This is basic math slash C# type concept.  An integer cannot have a fractional part meaning a value between 0 and 1 like 0.1425.  If you were to convert 0.1415 to an integer the result is 0.  

I provided links in my first post that explains this concept.

The fact that this line is always true...

if (b > 0.75M)

Means that you are not dealing with values that are between 0 and 1.  

I  suggest that you learn how to use the Visual Studio debugger to set breakpoints and single step through code.  This will help you fix the bug.

https://msdn.microsoft.com/en-us/library/y740d9d3.aspx

Answer 7

User-1716253493 posted
Sorry, i have just copy paste and forget to edit it. Use convert.todecimal or use double

Answer 8

User-1314346660 posted

Thank you very much Gents.

I understand the theory behind int/decimal/float etc. What I don't understand is the c# syntax. I am getting to grips with it slowly - its different from what i know which is pure SQL.

So thank you very much for helping me out. I cast the dataset as a FLOAT and used the code below to get asp to do what I want. I am very grateful for your help. The quick fixes are great and I am building a code library from them and the pointers to learn myself are just as helpful.

I do need to get to grips with debug. I will look inti that. Thanks again.

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                decimal b = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "% Compliance"));
                if (b > 0.75M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Green;
                }
                else
                if (b < 0.5M)
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Tomato;
                }
                else
                {
                    e.Row.Cells[6].BackColor = System.Drawing.Color.Orange;
                }
            }
        }