How could I view absent and present with unique color?

Question

1.00/5 (3 votes)

See more:

I have a table(PayRoll_AttendanceStatus) that contains 4 columns like

======================================
StatusID | Status | ShortName | color
======================================
   01    | Present|     P     |  Yellow
   02    | Absent |     A     |  Red
=======================================

from above mentioned table I used to mark day wise attendance into another table (PayRollMarkAttendance)and it contains values like

====================================================
MarkId  |  Employee_Code  |  StatusID  |  DateTime
====================================================
1       |        001      |    01      |  2016-06-11
2       |        002      |    02      |  2016-06-11
====================================================

For view I am using Pivot table and the code for pivot table is

SQL

SELECT * FROM (SELECT [EMPLOYEENAME] ,[EMPLOYEE_CODE]  ,[ShortName], DATENAME(M, [DateTime])as [Month]  ,DAY([DateTime]) as [DayValue] FROM [View_PayRollMarkAttendance]) as Composite PIVOT (MAX([ShortName]) FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31])) AS PivotTable

Through this pivottable I can view my status quite good. But I want to view ABSENT AND PRESENT through its unique code color. Absent for Red and Present for Yellow.

Please help any one.

Thanks

Posted 10-Jun-16 10:59am

Member 12245539

Updated 10-Jun-16 19:01pm

CHill60

v2

Add a Solution

Comments

BillWoodruff 10-Jun-16 17:11pm

What Control or user interface are you using to actually render the color so they are visible ?

Member 12245539 10-Jun-16 17:21pm

Windows Form C# and Gridview

Dave Kreskowiak 10-Jun-16 18:32pm

One more time. WHICH UI TECHNOLOGY ARE YOU USING? Is this a Windows Forms app, WPF, ASP.NET, Console, ?? WHAT? We have no idea what controls you're using to display this information so it's impossible to tell you how to color it!
'

Member 12245539 11-Jun-16 0:21am

Windows Form with C#

Dave Kreskowiak 11-Jun-16 0:25am

AND WHICH CONTROL(S) ARE YOU USING TO SHOW THIS DATA?? A DataGridView, a bunch of TextBoxes, ... what??

Come on. We can't see your screen or read your mind.

Member 12245539 11-Jun-16 0:27am

Gridview

CHill60 11-Jun-16 7:15am

And yet below you say you are using a DatagridView! GridView and Datagridview are two completely different controls! You really are not helping us to help you

BillWoodruff 10-Jun-16 21:40pm

If you are speaking about actually seeing the colors, then there has to be some user-interface container/control where those colors are rendered. What is it ?

Karthik_Mahalingam 10-Jun-16 22:34pm

what is the front end technology?

Member 12245539 11-Jun-16 0:31am

Windows Form C# and DatagridView

Karthik_Mahalingam 11-Jun-16 0:50am

you should have mentioned this at the first place.

Member 12245539 11-Jun-16 1:01am

Ok

Karthik_Mahalingam 11-Jun-16 1:14am

check the solution.

ZurdoDev 10-Jun-16 22:43pm

If you have code that gives you absent and present why can't you choose a color based on those values?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2016-06-10T19:01:00

Solution 1

updated solution:

C#

private void Form1_Load(object sender, EventArgs e)
      {
          DataTable dtStatusColor = new DataTable();
          dtStatusColor.Columns.Add("ShortName");
          dtStatusColor.Columns.Add("color");
          dtStatusColor.Rows.Add("P", "Yellow");
          dtStatusColor.Rows.Add("A", "Red");
          Dictionary<string, string> dictColors = new Dictionary<string, string>();
          foreach (DataRow row in dtStatusColor.Rows)
              dictColors.Add(row["ShortName"].ToString(), row["color"].ToString());



          DataTable dtStatus = new DataTable();
          dtStatus.Columns.Add("EMPLOYEENAME");
          dtStatus.Columns.Add("EMPLOYEE_CODE");
          dtStatus.Columns.Add("ShortName");
          dtStatus.Columns.Add("Month");
          dtStatus.Columns.Add("DayValue");
          dtStatus.Rows.Add("karthik", "E01", "P", "Jan", 25);
          dtStatus.Rows.Add("parthip", "E02", "P", "Feb", 25);
          dtStatus.Rows.Add("kavya", "E03", "A", "Mar", 25);



          dataGridView1.DataSource = dtStatus;
          int columnIndex = 2; // index value for the status column ( zero based index )
          foreach (DataGridViewRow row in dataGridView1.Rows)
          {
              if (row.Index > -1 && row.Index < dataGridView1.Rows.Count - 1)
              {
                  string status = row.Cells[columnIndex].Value.ToString();
                  row.Cells[columnIndex].Style.BackColor = System.Drawing.Color.FromName(dictColors[status]);

              }
          }

      }

Posted 10-Jun-16 19:01pm

Karthik_Mahalingam

Updated 10-Jun-16 20:09pm

v2

Comments

Member 12245539 11-Jun-16 1:25am

Thanks Mr Karthik,
But there is little bit issue.
The color I want to pick from my First table (PayRoll_AttendanceStatus) that I have mentioned above. I you see, there is one column for color only. I want pick from color column only. Please see.

Karthik_Mahalingam 11-Jun-16 1:26am

ok, does the datatable has the color value ?

what is the sample output of the table to be displayed in the grid ?

Member 12245539 11-Jun-16 1:43am

Yes it has color value.

the code that I am using is
try
{
string Querry = "SELECT * FROM(SELECT [EMPLOYEENAME] ,[EMPLOYEE_CODE] ,[ShortName] ,DATENAME(M, [DateTime])as [Month] ,DAY([DateTime]) as [DayValue] FROM [View_PayRollMarkAttendance]) as Composite PIVOT (MAX([ShortName]) FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31])) AS PivotTable";

SqlDataAdapter sda = new SqlDataAdapter(Querry, con);
DataSet ds = new DataSet();
sda.Fill(ds);
DataGridView.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

Karthik_Mahalingam 11-Jun-16 1:58am

so you need to get the color from status table.

Karthik_Mahalingam 11-Jun-16 2:09am

check the updated solution.

Member 12245539 11-Jun-16 3:16am

I have updated the codes. But The there is no changes in color. Previously, that I updated was effected with color but with helop of dataGridView1_CellFormatting event.

Kindly see the issue.

Karthik_Mahalingam 11-Jun-16 9:38am

post your code.

Member 12245539 11-Jun-16 22:42pm

DataTable dtStatusColor = new DataTable();
string selectQuerry1 = "select ShortName, color from PayRoll_AttendanceStatus";
sda = new SqlDataAdapter(selectQuerry1,con);
sda.Fill(dtStatusColor);
Dictionary<string, string=""> dictColors = new Dictionary<string, string="">();
foreach (DataRow row in dtStatusColor.Rows)
dictColors.Add(row["ShortName"].ToString(), row["color"].ToString());

string Querry2 = "SELECT * FROM(SELECT [EMPLOYEENAME] ,[EMPLOYEE_CODE] ,[ShortName] ,DATENAME(M, [DateTime])as [Month] ,DAY([DateTime]) as [DayValue] FROM [View_PayRollMarkAttendance]) as Composite PIVOT (MAX([ShortName]) FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31])) AS PivotTable";
DataTable dtStatus = new DataTable();
sda = new SqlDataAdapter(Querry2, con);
sda.Fill(dtStatus);

dataGridView1.DataSource = dtStatus;
int columnIndex = 5; // index value for the status column ( zero based index )
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Index > -1 && row.Index < dataGridView1.Rows.Count - 1)
{
string status = row.Cells[columnIndex].Value.ToString();
row.Cells[columnIndex].Style.BackColor = System.Drawing.Color.FromName(dictColors[status]);

}
}

Karthik_Mahalingam 12-Jun-16 0:26am

change it to 2

int columnIndex = 2;