?
Solved

Calculating Total in a Query

Posted on 2011-04-24
3
Medium Priority
?
190 Views
Last Modified: 2012-05-11
I'm trying to return the total cost of all the items returned from this query that puts items into a listView. I know using the SUM keyword will return the sum but int this case it is not working and I dont know why. CashOutTotal is the decimal value that I want to return the total. Any help would be appreciated. Thanks
private void frmMemberCashOutList_Load(object sender, EventArgs e)
        {
            SqlCeConnection con = BookStoreDB.GetConnection();
           // SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum (Price) AS MemberPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
             SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum(Price) AS NewPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
            con.Open();
            SqlCeDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                string[] data = new string[6];
                data[0] = reader["BookID"].ToString();
                data[1] = reader["MemberID"].ToString();         
                data[2] = reader["Title"].ToString();
                data[3] = reader["Author"].ToString();
                data[4] = reader["Price"].ToString();
                CashOutTotal = (decimal)reader["NewPrice"];
                ListViewItem liv = new ListViewItem(data);
                listView1.Items.Add(liv);



            }
            txtTotal.Text = CashOutTotal.ToString();
            con.Close();
        }

Open in new window

0
Comment
Question by:linkcube1
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 35456934
Hi, this is what you want?
private void frmMemberCashOutList_Load(object sender, EventArgs e)
{
    SqlCeConnection con = BookStoreDB.GetConnection();
    // SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum (Price) AS MemberPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
    SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum(Price) AS NewPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
    con.Open();
    SqlCeDataReader reader = comm.ExecuteReader();

    decimal cashOutTotal = 0;
    while (reader.Read())
    {
        string[] data = new string[6];
        data[0] = reader["BookID"].ToString();
        data[1] = reader["MemberID"].ToString();
        data[2] = reader["Title"].ToString();
        data[3] = reader["Author"].ToString();
        data[4] = reader["Price"].ToString();
                
        cashOutTotal += Convert.ToDecimal(reader["NewPrice"]);

        ListViewItem liv = new ListViewItem(data);
        listView1.Items.Add(liv);
    }
    txtTotal.Text = cashOutTotal.ToString();
    con.Close();
}

Open in new window

0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35456942
I think that you must remove the Sum(Total) as NewPrice from this line:
SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum(Price) AS NewPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);

Open in new window

And use the Price column instead...
0
 
LVL 3

Expert Comment

by:tbsolutions
ID: 35456963
I guess yv989c's solution will work in your case, but you could also check if the column you are trying to SUM, is correctly typed in the database, because if it's for example a varchar field, SUM won't work. Currency fields use to have double type or something like that.
Hope it helps.
Boccio
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question