2 Replies - 1791 Views - Last Post: 08 August 2016 - 10:18 AM

#1 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6444
  • View blog
  • Posts: 26,066
  • Joined: 12-December 12

Adding and Updating a Calculated Column to DGV

Posted 07 August 2016 - 01:10 PM

Demonstrates how to include a calculated column with a DataGridView, using the Expression property for the underlying DataTable:

DataColumn.Expression Property

Attached Image
Public Class frmCalculated

    Private Sub frmCalculated_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim people As New DataTable

        people.Columns.Add("FirstName", GetType(String))
        people.Columns.Add("LastName", GetType(String))
        people.Columns.Add("Salary", GetType(Decimal))
        people.Columns.Add("Salary5pc", GetType(Decimal))

        people.Columns("Salary5pc").Expression = "Salary * 0.05"

        people.Rows.Add("Bob", "Biscuit", 20000)
        people.Rows.Add("Terry", "Twitcher", 22000)
        people.Rows.Add("Mary", "Mumbles", 24600)

        dgvCalculated.AutoGenerateColumns = True
        dgvCalculated.DataSource = people

    End Sub

This isn't much different to the example from the docs, but the extra piece I wanted to add was to get the calculated value to update immediately, as soon as a value is changed:
    Private Sub dgvCalculated_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvCalculated.CellValueChanged
        DirectCast(dgvCalculated.CurrentRow.DataBoundItem, DataRowView).Row.EndEdit()
    End Sub

Without this missing piece of the puzzle the user has to move away from the row before they see the recalculation.

It is slightly better to check that it is a relevant column that is being changed:
    Private Sub dgvCalculated_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles dgvCalculated.CellValueChanged
        If dgvCalculated.Columns(e.ColumnIndex).Name = "Salary" Then
            DirectCast(dgvCalculated.CurrentRow.DataBoundItem, DataRowView).Row.EndEdit()
        End If
    End Sub

The calculation doesn't immediately update for a new row, the user still has to move away. Personally, I'm happy with this. If you wanted to pursue this then I believe you could cause the new row to be Added to the underlying DataTable as soon as the needed values are supplied.

Is This A Good Question/Topic? 0
  • +

Replies To: Adding and Updating a Calculated Column to DGV

#2 maceysoftware  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 350
  • View blog
  • Posts: 1,508
  • Joined: 07-September 13

Re: Adding and Updating a Calculated Column to DGV

Posted 08 August 2016 - 02:02 AM

Real nice tutorial snippet about expression. [edit]Just realised we were in the snippet section[/edit]

However Your solution of EndEdit on the CellValueChanged does take away part of the functionality of the DataGridView.

When your editing a cell you can click the escape key to undo your changes on that cell, you can then press the escape key again to undo changes on the whole row.

Calling EndEdit Accepts your changes for the edit level meaning that as soon as you tab off the salary cell into the next cell you are no longer able to undo your changes for the row. If you don't mind loosing this functionality then that is fine, however I thought i would point it out.

This post has been edited by maceysoftware: 08 August 2016 - 04:26 AM

Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6444
  • View blog
  • Posts: 26,066
  • Joined: 12-December 12

Re: Adding and Updating a Calculated Column to DGV

Posted 08 August 2016 - 10:18 AM

Thank you.

Probably using EndEdit only for the relevant column(s) (Salary in his case) is a reasonable compromise. That is, considering it as a significant change to the row that we cannot immediately reverse with Escape.

I'll leave for the reader to discover a way (if there is one) to retain the Escape functionality ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1