Results 1 to 17 of 17

Thread: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Resolved [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    I wrote some code that will total the dollars selected by the mouse in a datagridview. The code below works just fine if the user is smart enough only to select data in a single dollar value column.

    The dgv is a list of outstanding invoices with dates, invoice numbers, amount of invoice and amount owing.

    Ideally for the first part of my question, I would like to allow the calculation to ignore any time someone clicks in the invoice number or date columns and only do the calculation if they select cells in the amount owing or invoice amount columns.

    Would the best approach here be to specify the column and only allow the math to happen when the column name matches Invoice Amount or Amount Owing?

    If so, how do I add the qualification of column name? I tried adding a comparison of the selected cell to dgvCustomerDetail.Columns("Amount Owing") but my syntax is wrong because I can't get the column name of the selected cell as it goes through the For loop (it looks like I can only get the index - so should I just use the index instead of name everywhere? - I just find it easier to use the name because I can't remember which column order is used half the time).

    Code:
    'in the MouseUp event
    Dim dblTotal As Double = 0
    
    For Each cell As DataGridViewCell In dgvCustomerDetail.SelectedCells
       dblTotal += Double.Parse(cell.Value.ToString)
    Next
    
    If dblTotal <> 0 Then
       lblSelectedTotal.Text = "Selected Total: " & dblTotal.ToString("C")
    Else
       lblSelectedTotal.Text = ""
    End If
    A second part has recently been suggested. The client would like to also use an unbound check box to do a selection of rows to other processing. I have already added that column called "Select" and will be adding the code to do stuff with the row data later, but they now wish a summary total of the selected row Amounts Owing similar to the above but only for the one column, to be up in the same label. I think I am going to have to split the calculation and use two labels ultimately but how do I do the same calculation as above for just the selected Amount Owing column when the unbound checkbox (Column 0) selects the row?

    Hopefully that all makes sense and the answer isn't something really obvious that I am missing, but it likely is.

    TIA rasinc.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Sum Selected Cells of DataGridView only in specific columns

    Change this:
    vb.net Code:
    1. For Each cell As DataGridViewCell In dgvCustomerDetail.SelectedCells
    2.    dblTotal += Double.Parse(cell.Value.ToString)
    3. Next
    to this:
    vb.net Code:
    1. For Each cell As DataGridViewCell In dgvCustomerDetail.SelectedCells
    2.     If cell.ColumnIndex = theIndexOfTheDesiredColumn Then
    3.         dblTotal += Double.Parse(cell.Value.ToString)
    4.     End If
    5. Next
    or this:
    vb.net Code:
    1. For Each cell In dgvCustomerDetail.SelectedCells.
    2.                                    Cast(Of DataGridViewCell)().
    3.                                    Where(Function(dgvc) dgvc.ColumnIndex = theIndexOfTheDesiredColumn)
    4.     dblTotal += Double.Parse(cell.Value.ToString)
    5. Next
    or you could even do this:
    vb.net Code:
    1. dblTotal = dgvCustomerDetail.SelectedCells.
    2.                              Cast(Of DataGridViewCell)().
    3.                              Where(Function(dgvc) dgvc.ColumnIndex = theIndexOfTheDesiredColumn).
    4.                              Sum(Function(dgvc) Double.Parse(dgvc.Value.ToString))

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Sum Selected Cells of DataGridView only in specific columns

    Using Double.Parse that way is risky. Why not Val()?

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by .paul. View Post
    Using Double.Parse that way is risky. Why not Val()?
    I would suggest that validating the data when it's entered to prevent any non-numeric data being accepted in the first place would be the most appropriate option. That would mean that using Double.Parse would not be an issue when it came to actually using the data. The main issue that I have with Val is that, while it avoids an exception being thrown if invalid data is detected, it will silently ignore such data and produce an invalid result without notification.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: Sum Selected Cells of DataGridView only in specific columns

    Thanks to both of you. I was just reading up on Val in the MSDN as I thought it was an antiquated VB6 command. Thanks .paul. for making me go back and read it.

    I was coming to the same conclusion as jmcilhinney has mentioned regarding inaccurate data returned by Val without a warning.

    In this case, however, I am getting the data directly from a database, not from user input. The dgv is read-only so users cannot invalidate the data type. So either would probably work.

    The code alterations jmcilhinney provided does work (I've tried the first two of them so far and both accomplish the goal).

    So thanks again for the help.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Double.Parse will fail if you include an empty cell.

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by .paul. View Post
    Double.Parse will fail if you include an empty cell.
    That's a good point, but then I'd be more inclined to filter out empty cells using the If statement or Where method.

  8. #8
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by rasinc View Post
    In this case, however, I am getting the data directly from a database, not from user input. The dgv is read-only so users cannot invalidate the data type.
    In that case, the data should already be Double values, so why convert to String and then back to Double? You should simply cast the Object reference you get from the Value property as type Double:
    Code:
    dblTotal += CDbl(cell.Value)
    If some null values may come from the database then, as I said to .paul., you could filter them out first:
    Code:
    Where(Function(dgvc) dgvc.ColumnIndex = theIndexOfTheDesiredColumn AndAlso dgvc.Value IsNot DBNull.Value)

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Thanks .paul., I'll keep that in mind. So far in my testing, I have not come across this issue. All invoices have an amount owing and invoice amount and both in this case are non-zero.

    I will keep this in mind though if they decide they wish to see fully paid invoices as well, though I would suspect my SELECT query would return a zero on amount owing so it would not qualify as an empty/null cell.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    jm, time for me to go back and reread the dgv info in the msdn. I have not been working with them much (primarily quick display of info without any manipulation or summarising). I was under the impression from reviewing various code samples that the values were always presented as strings similar to textboxes and labels and therefore had to be cast as the string, then converted to whatever integer, double, currency, etc. However, now that I am reading what I just wrote, the data is coming from a datatable so it should be picking up the data type from it.

    Too late tonight (ok, now it's tomorrow) so I will look through it and datatables again after some sleep.

    Thank you both for the help.

  11. #11
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by rasinc View Post
    I was under the impression from reviewing various code samples that the values were always presented as strings similar to textboxes and labels
    All cell values are type Object

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by rasinc View Post
    I was under the impression from reviewing various code samples that the values were always presented as strings similar to textboxes and labels and therefore had to be cast as the string, then converted to whatever integer, double, currency, etc. However, now that I am reading what I just wrote, the data is coming from a datatable so it should be picking up the data type from it.
    The Value property of a DataGridViewCell is type Object, so that a cell can contain any object at all. That's just like the Item property of a DataRow. You will generally know what the type is for any particular column so, when getting data from cells in that column, you cast as appropriate.

    If you use a DataGridViewTextBoxColumn in an unbound grid then each cell will contain a String. If the grid is bound though, the type of the data contained in the cell is determined by the data source. Any text entered will be automatically converted to that type and the DataError event is raised if that fails. If you have a DataTable as the data source and a column contains Double values, that's what you will get from the cells in that column of the grid. You will get them as Object references though, so you still need to cast as type Double. There's no actual conversion though. You're just unboxing a value type, not converting one type to another.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2007
    Posts
    476

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    Thanks. That makes perfect sense. However, I think part of the reason why I used double.parse is because of some code like below. This is just a part of the code in that link but it shows taking the data in a cell, converting to string to change it back to integer. So I had used a similar concept when creating my code. What I didn't see was that when they added the data to the datagridview it was added as string, while I am adding as double. So point taken and I think I have this one solved now with the help of you both. Thank you.

    Code:
    ' Iterate through all the rows and sum up the appropriate columns.
        For counter = 0 To (DataGridView1.Rows.Count - 1)
            If Not DataGridView1.Rows(counter) _
                .Cells("Withdrawals").Value Is Nothing Then
    
                If Not DataGridView1.Rows(counter) _
                    .Cells("Withdrawals").Value.ToString().Length = 0 Then
    
                    WithdrawalTotal += _
                        Integer.Parse(DataGridView1.Rows(counter) _
                        .Cells("Withdrawals").Value.ToString())
                End If
            End If
    
            If Not DataGridView1.Rows(counter) _
                .Cells("Deposits").Value Is Nothing Then
    
                If Not DataGridView1.Rows(counter) _
                    .Cells("Deposits").Value.ToString().Length = 0 Then
    
                    DepositTotal += _
                        Integer.Parse(DataGridView1.Rows(counter) _
                        .Cells("Deposits").Value.ToString())
                End If
            End If
        Next

  14. #14
    New Member
    Join Date
    Oct 2021
    Posts
    5

    Re: Sum Selected Cells of DataGridView only in specific columns

    hi, thanks to this code, it really works, but it shows the dollar sign, how not to show any currency sign?
    Name:  dsdsdd.PNG
Views: 650
Size:  7.3 KB

    and also the it shows the unit after the dbtotal, (for example, it displays Selected Total: 13,880.00 KG)

    thank you somuch

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Sum Selected Cells of DataGridView only in specific columns

    Quote Originally Posted by banderasxp View Post
    hi, thanks to this code, it really works, but it shows the dollar sign, how not to show any currency sign?
    Name:  dsdsdd.PNG
Views: 650
Size:  7.3 KB

    and also the it shows the unit after the dbtotal, (for example, it displays Selected Total: 13,880.00 KG)

    thank you somuch
    The total is just a number, so you can format it any way you want. Convert it to a String by calling its ToString and specify the format you want.

  16. #16
    New Member
    Join Date
    Oct 2021
    Posts
    5

    Re: Sum Selected Cells of DataGridView only in specific columns

    which part can I edit not to show the dollar sign and how to add the KG in code.

    thanks for reply

  17. #17
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns

    .ToString(“c”) for the $. If you want to remove it, don’t specify the “c” when you’re formatting it.
    Just add & “KG” for the kg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width