-
Nov 21st, 2015, 03:52 PM
#1
Thread Starter
Hyperactive Member
[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.
-
Nov 21st, 2015, 09:07 PM
#2
Re: Sum Selected Cells of DataGridView only in specific columns
Change this:
vb.net Code:
For Each cell As DataGridViewCell In dgvCustomerDetail.SelectedCells dblTotal += Double.Parse(cell.Value.ToString) Next
to this:
vb.net Code:
For Each cell As DataGridViewCell In dgvCustomerDetail.SelectedCells If cell.ColumnIndex = theIndexOfTheDesiredColumn Then dblTotal += Double.Parse(cell.Value.ToString) End If Next
or this:
vb.net Code:
For Each cell In dgvCustomerDetail.SelectedCells. Cast(Of DataGridViewCell)(). Where(Function(dgvc) dgvc.ColumnIndex = theIndexOfTheDesiredColumn) dblTotal += Double.Parse(cell.Value.ToString) Next
or you could even do this:
vb.net Code:
dblTotal = dgvCustomerDetail.SelectedCells. Cast(Of DataGridViewCell)(). Where(Function(dgvc) dgvc.ColumnIndex = theIndexOfTheDesiredColumn). Sum(Function(dgvc) Double.Parse(dgvc.Value.ToString))
Last edited by jmcilhinney; Nov 21st, 2015 at 09:11 PM.
-
Nov 21st, 2015, 10:07 PM
#3
Re: Sum Selected Cells of DataGridView only in specific columns
Using Double.Parse that way is risky. Why not Val()?
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Nov 21st, 2015, 11:12 PM
#4
Re: Sum Selected Cells of DataGridView only in specific columns
Originally Posted by .paul.
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.
-
Nov 21st, 2015, 11:46 PM
#5
Thread Starter
Hyperactive Member
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.
-
Nov 22nd, 2015, 12:00 AM
#6
Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns
Double.Parse will fail if you include an empty cell.
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Nov 22nd, 2015, 12:35 AM
#7
Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns
Originally Posted by .paul.
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.
-
Nov 22nd, 2015, 12:39 AM
#8
Re: Sum Selected Cells of DataGridView only in specific columns
Originally Posted by rasinc
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)
-
Nov 22nd, 2015, 12:42 AM
#9
Thread Starter
Hyperactive Member
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.
-
Nov 22nd, 2015, 12:58 AM
#10
Thread Starter
Hyperactive Member
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.
-
Nov 22nd, 2015, 01:04 AM
#11
Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns
Originally Posted by rasinc
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Nov 22nd, 2015, 01:59 AM
#12
Re: [RESOLVED] Sum Selected Cells of DataGridView only in specific columns
Originally Posted by rasinc
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.
-
Nov 22nd, 2015, 01:11 PM
#13
Thread Starter
Hyperactive Member
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
-
Jan 8th, 2022, 02:42 AM
#14
New Member
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?
and also the it shows the unit after the dbtotal, (for example, it displays Selected Total: 13,880.00 KG)
thank you somuch
-
Jan 8th, 2022, 04:12 AM
#15
Re: Sum Selected Cells of DataGridView only in specific columns
Originally Posted by banderasxp
hi, thanks to this code, it really works, but it shows the dollar sign, how not to show any currency sign?
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.
-
Jan 8th, 2022, 05:11 AM
#16
New Member
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
-
Jan 8th, 2022, 06:16 AM
#17
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|