1

I am testing a sample application, which adds a number of individuals i.e., "people" and records a measure of time for each month of the year for each of these people i.e., "minTime" a decimal value, and "auxTime" a decimal value.

In my code, I iterate through a SQL database table in an effort to total the decimal values of each individual's hours. "person" is an nvarchar, and "yearTotal" is a decimal.

I want to display the totals in a ListView screen.

For testing purposes I have added 2-people. Each individual has a measure of time entered for each of the 12-months.

There are 2-problems I'm running into.

1) Attempting to add "yearTotal" to the ListView control, ends up displaying each month of the 12-months worth of data for each person.

For example: John Smith 50-hours, John Smith 75-hours, etc, rather than a total for John Smith, and this occurs for all the persons.

2) And as soon as "yearTotal" reaches the correct total for the first individual, it holds onto this value and increments it for the next person, giving a false value for all subsequent persons.

I need to know how (and where) to add each person's yearly total in ONE Entry to the ListView control, rather than multiple entries of the same person.

For example: John Smith 840-hours, Sally Jones 750-hours.

Here is a link to download a "testDB" with a single table named "timeRecord". Please bear in mind that I need a way to completely Total the values for each individual person, totaling their values from January through December and displaying that person's grand total in a ListView control.

When I've attempted to use SUM on the minTime and auxTime columns (with an alias) SQL still displays the individual month totals but not a single Grand Total for each person. sample database

CodeMann
  • 157
  • 9
  • Do not use for loop and just change command to this: `SELECT PEOPLE, SUM(MINTIME) FROM TIEMTABLE ORDER BY PEOPLE ASC GROUP BY PEOPLE` and it will solve everything – Aleksa Ristic Jul 13 '18 at 21:50
  • Hello Aleksa. I tried your suggestion, but I believe the ORDER BY clause must be the last clause that you specify in a query. However, when I place the GROUP BY people followed by ORDER BY people, I keep getting an error on 'minTime'. When I drill into the error, it's reporting a System.IndexOutOfRangeException on 'minTime', although SQL Server Management Studio shows there is minTime data for all the persons. Not sure why this error is occurring. – CodeMann Jul 14 '18 at 21:50

2 Answers2

0

I will not explain It but just write finished code.

class String_String
{
    public _string1 { get; set; }
    public _string2 { get; set; }
}


try
{
    using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TimeKeeping.mdf;Integrated Security=True;User Instance=False"))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand(
        "SELECT people, SUM(minTime) FROM timeTable ASC GROUP BY PEOPLE ORDER BY people", conn))
        {
            SqlDataReader dr = cmd.ExecuteReader();

            List<String_String> list = new List<String_String>();

            while(dr.Read())
            {
                list.Add(new List<String_String> { _string1 = dr[0].ToString(), _string2 = dr[1].ToString() });
            }


            lstVwPeopleTime.DataSource = list;
            lstVwPeopleTime.DisplayMember = "_string2";
            lstVwPeopleTime.ValueMember = "_string1";
        }
    }
}
catch (SqlException ex) { MessageBox.Show(ex.Message); }
catch (System.Exception ex) { MessageBox.Show(ex.Message); }

Code is not tested so it may give error.

Aleksa Ristic
  • 2,394
  • 3
  • 23
  • 54
  • Aleksa, I wasn't able to get your code working. However, I noticed a distinct problem when attempting to SUM my column. For example, if I form my code like this: using (SqlDataAdapter adap = new SqlDataAdapter( "SELECT people, SUM(minTime) FROM timeTable ORDER BY people ASC", conn)), then it throws the error "Column does not belong to table." When I step through the code, it shows [0] {people}, [1] {Column 1} instead of {minTime}. Why does performing a SUM on the column prevent the query from recognizing the actual column name? – CodeMann Jul 26 '18 at 20:14
  • try `SUM(minTime) as TIME` – Aleksa Ristic Aug 01 '18 at 07:34
  • Hello Aleksa. That corrected the error that was thrown. However, it still doesn't give me a Total for each person. Whether I try it in code, or in SQL Server Management Studio, it still displays a list of decimal values for each person. I will therefore try to read the Decimal values into an array and then do a SUM on them. – CodeMann Aug 02 '18 at 20:28
  • @CodeMann could you provide me with your table structure with few rows in it – Aleksa Ristic Aug 03 '18 at 06:40
  • Aleksa, please see my edited comments above, i.e., supplying the additional database structure and explanatory info that was requested. – CodeMann Aug 10 '18 at 23:22
  • Aleksa, I've created a simple one-table database with 2-people and 12-months worth of data, so that you might test it. I've provided a link above in my previous post, to download it. – CodeMann Sep 02 '18 at 22:36
0

I finally figured out how to get around the issues I was experiencing. I created a separate method, i.e., private void GetTotals(string name) wherein I pass the name of each person. I queried the person, minTime, auxTime from tablename where person = @person. Then inside a SQL datareader loop, I increment the minTime and auxTime values and pass those values to the ListViewItem for display in my ListView control.

while (reader.Read())
{
    yearTotal += Convert.ToDecimal(reader["minTime"].ToString(), CultureInfo.InvariantCulture) + 0.00M;
    auxTotal += Convert.ToDecimal(reader["auxTime"].ToString(), CultureInfo.InvariantCulture) + 0.00M;
}
ListViewItem listItem = new ListViewItem(name);
listItem.SubItems.Add(yearTotal.ToString());
listItem.SubItems.Add(auxTotal.ToString());
lstVwPeopleTime.Items.Add(listItem);
name = "";
return;

In my former method, I merely create and instantiate an ArrayList, query all the person's names in table and add them to the ArrayList. Then inside a foreach loop, I pass each person's name to the GetTotals method. This method successfully totals and displays each person's 12-month value in the ListView control.

CodeMann
  • 157
  • 9