How to count the row in another column?

Question

0.00/5 (No votes)

See more:

C#

OleDbCommand cmd = new OleDbCommand();

                        cmd.Connection = con;
                        // set your file name in the below query
                        cmd.CommandText = "SELECT * FROM " + csv ;
     
                        //Open Oledb Connection to read CSV file 
                        con.Open();

                        //Create one datatable to store data from CSV file
                        DataTable dt = new DataTable();

                        // Load Data into the datatable 
                        dt.Load(cmd.ExecuteReader());
                        con.Close();

                        foreach (DataRow row in dt.Rows)
                        {
                            lstData.Add(new Tablelist() { process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });
                            
                        }

                        // assign the lstData to the gridview..
                        GridViewShow.DataSource = lstData;
                        GridViewShow.DataBind();

This code allowed me to read 3 csv files which is 0730.csv, 0830.csv,0930.csv and show all in gridview with this 3 columns : process, event, status...
can I actually show it with this output format

0730|0830|0930|
pass|pass|pass|

1)i need to show this table ,
2)pass is the total count of how many pass,
3)which is read from the "status column" of this 3 csv :0730.csv, 0830.csv,0930.csv

Posted 1-Jan-14 15:17pm

acing wei

Updated 1-Jan-14 18:04pm

Aarti Meswania

v2

Add a Solution

Comments

ZurdoDev 1-Jan-14 21:38pm

I don't understand your question or where you are stuck.

acing wei 1-Jan-14 22:03pm

Karthik_Mahalingam 2-Jan-14 1:27am

acing, will continue here..
you need only the pass count ?

acing wei 2-Jan-14 1:42am

and also the fail count...

2 solutions

Solution 1

Loop for one DataTable and count that column store in a Variable.
Then go for next, count the same column and store in another variable and so on for other DataTables.

Then sum all the variable counts. You now have the total count.

Create a DataTable and make columns as you need and show these data under appropriate columns.

Posted 1-Jan-14 16:48pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

acing wei 1-Jan-14 23:31pm

yes, when everytime looping i need to store it, how to store a variable.. how to use count and sum in a datatable?

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 2-Jan-14 2:53am

Show me what you have tried, if there are any problems, then I will try to rectify it.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 14-Jan-14 10:36am

I can't understand. Can you please explain what are trying to achieve? I am getting confused.

acing wei 14-Jan-14 20:13pm

i want to achieve a new row of newrowpasscount everytime it loops..because a newrowpasscount will giving me different value everytime it loops..i need new row to put my newrowpasscount ..

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 · 2014-01-01T21:22:00

Solution 2

Hi try like this...

C#

foreach (DataRow row in dt.Rows)
        {
            lstData.Add(new Tablelist() { FileName=csv,  process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });

        }
    }
        DataTable dtoutput = new DataTable();
       var columnNames =  lstData.Select(k => k.FileName).Distinct();
       foreach (string columnName in columnNames)       
           dtoutput.Columns.Add(columnName, typeof(string));
       DataRow newrowPassCount = dtoutput.NewRow();
         foreach (string columnName in columnNames) {
            int count = lstData.Where(k=> k.FileName == columnName).Count(k => k.status == "Pass");
            newrowPassCount[columnName] = count; 
        }
         dtoutput.Rows.Add(newrowPassCount);

         DataRow newrowFailCount = dtoutput.NewRow();
         foreach (string columnName in columnNames)
         {
             int count = lstData.Where(k => k.FileName == columnName).Count(k => k.status == "Fail");
             newrowFailCount[columnName] = count;
         }
         dtoutput.Rows.Add(newrowFailCount);

         gridview.datasource = dtoutput;
         gridview.databind();

add a new property FileName in the class

C#

class Tablelist
{
    public string process { get; set; }
    public string Event { get; set; }
    public string status { get; set; }
    public string FileName { get; set; }
}

Posted 1-Jan-14 21:22pm

Karthik_Mahalingam

Comments

acing wei 2-Jan-14 3:51am

it display 0 in the column..

Karthik_Mahalingam 2-Jan-14 3:56am

which column?

Karthik_Mahalingam 2-Jan-14 3:57am

FileName=csv.Replace(".csv","").Replace(".CSV","");

acing wei 2-Jan-14 3:58am

all column...i debug and see the value from list data didn't go into the int count

Karthik_Mahalingam 2-Jan-14 4:07am

check the case for "pass"

acing wei 2-Jan-14 4:16am

yes it works...thank you .....

Karthik_Mahalingam 2-Jan-14 4:26am

welcome acing :)

acing wei 2-Jan-14 5:04am

ohya... if want to change to "chart" what should i put as x and y VAlue? i try but mine cannot work

Karthik_Mahalingam 2-Jan-14 5:10am

you r working on gridview know..
y suddenly chart came :)

Karthik_Mahalingam 2-Jan-14 5:11am

i am not so expert in charts.
you can post a new question.
might be others who are expert in chart can guide you..

acing wei 2-Jan-14 5:28am

Alright.its okay...thank you... :)

Karthik_Mahalingam 2-Jan-14 5:35am

:)

acing wei 2-Jan-14 22:14pm

oh no..after i checked with my table the pass and the fail count is wrong...help..

Karthik_Mahalingam 2-Jan-14 22:59pm

post your code..

Karthik_Mahalingam 2-Jan-14 23:18pm

1)make sure all the csv file names are (.csv, .CSV only)
2) check the case for ( Pass , Fail )
apply break point and debug it...

acing wei 2-Jan-14 23:38pm

i found something for eg: I read 0730.csv,o830.csv,and 0930.csv the first 0730.csv pass and rework count is correct, 0830 and 0930 will be wrong...
when i read eg:o830.csv, 0930.csv and 1030.csv , the 0830.csv will be correct..only first looping count is correct...i use break point to check only first loop count is correct after 2nd and 3rd loop the total count will become wrong.

Karthik_Mahalingam 3-Jan-14 0:25am

try this
k.status.Trim() =="PASS"

acing wei 3-Jan-14 1:02am

tried.. still same..only first csv pass/fail count correct..

Karthik_Mahalingam 3-Jan-14 1:12am

how many rows are there in lstData ?

acing wei 3-Jan-14 1:18am

3 rows.. i know why the 2nd and 3rd file when i read the pass and fail count become wrong..they actually sum up the previous file pass/fail eg: i read 0730.csv,o830.csv,and 0930.csv ..pass and fail in o830.csv,and 0930.csv will be wrong because o830.csv sum up the count of 0730.csv..and 0930.csv sum up both 0730.csv,o830.csv ass /fail....it never clear the value..

Karthik_Mahalingam 3-Jan-14 1:28am

no no, pls check this lstData.Count()

acing wei 3-Jan-14 1:41am

i debug and put break point..
int count = lstData.Where(k => k.FileName == columnName).Count(k => k.status == "PASS");
the lstdata.count=368 how they get 368?

Karthik_Mahalingam 3-Jan-14 1:50am

yes it will looping on the datatables..

acing wei 3-Jan-14 1:48am

oh, thats the row count that read from table list...but my row i count it manually between 0730 & 0830 total 262.. the lstdata. count counted it 368

Karthik_Mahalingam 3-Jan-14 1:50am

lstdata count is common...
we are iterating the lstdata.. it wont change..

acing wei 3-Jan-14 1:58am

so what should i do now? it keep taking previous file pass/fail count.

Karthik_Mahalingam 3-Jan-14 2:04am

ok wait for some time, i wil have my lunch and be back in few mins...

acing wei 6-Jan-14 1:53am

mr karthik, can explain me how to get a simple chart from my new tablelist? i have solved my table problem yesterday..

Karthik_Mahalingam 6-Jan-14 2:50am

what kind of chart.
for chart you can post a new question.
might be some experts on chart will answer you the exact mean.

acing wei 6-Jan-14 3:15am

I post before but nobody help me....i research google but i can't find what i need..a column chart using chart binding.

Karthik_Mahalingam 6-Jan-14 3:20am

you re-post it by adding proper and sufficient information , so that the user wil be able to understand your task easily and will help you for sure,
if they didn't understand the question, they wont consider your question..

acing wei 13-Jan-14 3:23am

mr karthik thanks for your suggestion , i did it for my chart, i got another question..
dtoutput.Rows.Add(newrowPassCount);
this code that time you suggest me created a new row...i don't want the value inside the row to be overwrite everytime it loops,after second time of looping i want the value to appear in a new row, can you suggest me?

Karthik_Mahalingam 13-Jan-14 3:48am

it will diplay only 2 rows
one is for pass count
another is for fail count..

acing wei 13-Jan-14 4:11am

yes correct,for example i got another file , in a second looping can i create another row of pass and fail count, instead of just overwrite...now become 4 rows, then after third loop it become 6 rows..i try using
foreach (DataRow row in dtoutput.Rows)
but it can't work...please?

acing wei 13-Jan-14 5:52am

I try couple of times using loop but I can't get the idea hw to do the looping ..

Karthik_Mahalingam 13-Jan-14 5:54am

post your full code

acing wei 13-Jan-14 6:22am

The code is the solution above that you teach me... I want it to create a new row after every looping..the value of the newrowpasscount and newrowfailcount will be created in a new row ..keep adding new row..

acing wei 13-Jan-14 6:23am

The value of newrowpasscount and newrow failcount will be different value in every row that created..

acing wei 13-Jan-14 21:26pm

foreach (DataRow row in dt.Rows)
{
lstData.Add(new Tablelist() { FileName=csv, process = row["process"] + "", Event = row["Event"] + "", status = row["status"] + "" });

}
}
DataTable dtoutput = new DataTable();
var columnNames = lstData.Select(k => k.FileName).Distinct();
foreach (string columnName in columnNames)
dtoutput.Columns.Add(columnName, typeof(string));
DataRow newrowPassCount = dtoutput.NewRow();
foreach (string columnName in columnNames) {
int count = lstData.Where(k=> k.FileName == columnName).Count(k => k.status == "Pass");
newrowPassCount[columnName] = count;
}
dtoutput.Rows.Add(newrowPassCount);

Karthik_Mahalingam 13-Jan-14 21:37pm

this code is fine.. it will add one row of data to the datatable...

acing wei 13-Jan-14 22:11pm

yes is fine creating a new row with (newrowpasscount) value..but now i need more row each time it loops it will give me (newrowpasscount) to show in another new row.. i use this but cannot work..
foreach (DataRow row in dtoutput.Rows)
{
dtoutput.Rows.Add(newrowPassCount);
}

Karthik_Mahalingam 14-Jan-14 6:27am

you have teamviewer ?

acing wei 14-Jan-14 6:33am

No , I really need your help to guide me...because my teacher not good in c#.net

Karthik_Mahalingam 14-Jan-14 6:35am

pls install team viewer in your machine..
and give me the id..

acing wei 14-Jan-14 6:53am

sorry but I can't because is a school project..and im not allowed to do installation in the machine..can just help me here?

Karthik_Mahalingam 14-Jan-14 11:36am

u can send the full code of that page only and screen shot to my mail id mtp.karthik@gmail.com

acing wei 14-Jan-14 20:09pm

I sent

Karthik_Mahalingam 14-Jan-14 22:55pm

ok i will check later... now i am in office, i cant check now..

acing wei 15-Jan-14 3:05am

Thank you :)

acing wei 15-Jan-14 9:26am

if you are not busy later..please help me see mine, thanks :)

Karthik_Mahalingam 15-Jan-14 9:37am

still i am in office, i will go to home in half hour after that i will see your mail..

acing wei 16-Jan-14 22:48pm

Hi have you check for me?

Karthik_Mahalingam 16-Jan-14 22:58pm

in office now, tomo leave for me, your issue will be resolved.. dont worry...