How to count and compare number in datagirdview c#

Question

I have labels "Hardwork" and 1 datagirdview display when load form. I use the code below to do the quantity comparison in the column "TotalTime". I want if the value is in column "TotalTime"> = 30 then labels "Harwork" + 1

but not run.the result is: specified cast is not valid.

Please, help me fix it

public void BtnSearch_Click(object sender, EventArgs e)

       {
           db = new DbConnector();
           lbTotal.Text = "00";

           db.fillDataGridView("select *from tbl_WorkLoad where TimeComplete Between'" + dateTimePicker1.Value.ToString("dd-MM-yy| HH:mm:tt") + "' and '" + dateTimePicker2.Value.ToString("dd-MM-yy| HH:mm:tt") + "'", dataGridView1);

           const string HardWorkLabelText = "Hard Work Count: {0}";
           const int HardWorkThreshold = 30;

           try
           {
               IEnumerable<DataGridViewRow> rows = dataGridView1.Rows.Cast<DataGridViewRow>().Where(r => ((Int32)r.Cells["TotalTime"].Value) >= HardWorkThreshold);

               lbHardwork.Text = string.Format(HardWorkLabelText, rows.Count());

               {
                   for (int i = 0; i < dataGridView1.Rows.Count; i++)
                   {
                       lbTotaltime.Text = (Convert.ToString(double.Parse(lbTotaltime.Text) + double.Parse(dataGridView1.Rows[i].Cells[7].Value.ToString())));

                  
           }
           catch (Exception ex)
           {

               MessageBox.Show(ex.Message.ToString());
           }
       }

Did you debug the code? Which line in the code throws the error? — Chetan, Oct 26 '20 at 06:06
IEnumerable rows = dataGridView1.Rows.Cast().Where(r => ((Int32)r.Cells["TotalTime"].Value) >= HardWorkThreshold); lbHardwork.Text = string.Format(HardWorkLabelText, rows.Count()); — Kevil, Oct 26 '20 at 06:17
You can count the HardworkCount inside the for loop only... and display it in label after the for loop — Chetan, Oct 26 '20 at 06:24
please, help me adjust code with loop for. I'm not good at this — Kevil, Oct 26 '20 at 06:28
On the side note: `db.fillDataGridView("select *from tbl_WorkLoad where....` this line of code is open for sql injection... User Parameterized query instead. — Chetan, Oct 26 '20 at 06:28
@chetan I understand your point about SQL injection in the general sense of concatting values into an sql string, but there seems minimal risk of it in this particular case — Caius Jard, Oct 26 '20 at 07:09

Chetan · Accepted Answer · 2020-10-26T07:21:20.737

0

You have apart from the exception, you have few other issues in the code. But let us focus on the issue which you reported.

From the code, I understand that, you want to display the number of hardwork and also you want to display the total hardwork time.

Since you are looping thru the rows of gridview, you can calculate both of these in the for loop.

var hardworkCount = 0;
var totalTimeSum = 0.0;

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    double totalTime = double.Parse(dataGridView1.Rows[i].Cells[7].Value.ToString());

    if(totalTime >= HardWorkThreshold)
    {
        hardworkCount++;
    }
    totalTimeSum  += totalTime;
}
lbHardwork.Text = hardworkCount.ToString();
lbTotaltime.Text = totalTimeSum.ToString();

edited Oct 26 '20 at 07:21

answered Oct 26 '20 at 06:44

Chetan

6,711
3
22
32

amazing!!! .Problem solved and code is clean. I just added lbHardwork.Text = hardworkCount.ToString (); – Kevil Oct 26 '20 at 07:05
But it raises a new problem: when I press the Search button many times, lbTotalTime keeps increasing Is there a way to fix it? – Kevil Oct 26 '20 at 07:18

score 0 · Answer 2 · answered Oct 26 '20 at 07:40

You life would be a lot simpler if you stop pulling your data out of the datagridview, and just have your datagridview based off a datatable that holds the data. It might be this way already, but we can't see the definition of your filldatagridview method

Here's how I'd do it:

//assuming sql sevrer, use correct dataadapter for your db
using(var da = new SqlDataAdapter "select * from tbl_WorkLoad where TimeComplete Between @a and @b", "YOUR CONNECTION STRING HERE")){
  da.SelectedCommand.Parameters.AddWithValue("@a", dateTimePicker1.Value);
  da.SelectedCommand.Parameters.AddWithValue("@b", dateTimePicker2.Value);

  var dt = new DataTable();

  da.Fill(dt);

  dataGridViewX.DataSource = dt;

  lbHardwork.Text = dt.Compute("Count([TotalTime])", "[TotalTime] >= 30").ToString();
  lbTotalTime.Text = dt.Compute("Sum([TotalTime])", "[TotalTime] >= 30").ToString();

}

The way you wrote your SQL is a pattern that risks SQL injection if used with strings - always use parameters in SQL. Always. There is never a reason not to when dealing with data values. See why, and also here

You should read this blog before using AddWithValue with strings in SQL Server. There are better ways to do parameters than AWV, but at the very least it would be preferable to use AddWithValue than what you're doing now

Don't access data in a gridview via the grid; bind the grid to a datatable and if you want data, access the datatable. If you want to get the datatable out of a grid at any point, you can use var dt = datagridviewX.DataSource as DataTable

No problem. To mark an answer as useful, click the up arrow; to mark an answer as the one you used click the grey check mark to turn it green. This changes the appearance of the question on the dashboard to let people know you no longer seek an answer. Any number of questions can be up-voted but only one can be ticked. Your up-votes are recorded but will only show publicly when you hit 15 reputation so don't worry if it looks like they didn't apply yet — Caius Jard, Oct 26 '20 at 08:04

How to count and compare number in datagirdview c#

2 Answers2