none
Can you create calculated fields in a DataGridView in the Designer? RRS feed

  • Question

  • If have a DataSet that connects to an Access *.accdb file to feed a DataGridView on a form.

    I would like to add a column to the DataGridView form that shows, for example, the result of ColumnA * ColumnE.

    Is it possible to do this in the Designer? I seem to be able to add the columns to either the DataSet or the DataGridView, but I can't find a way to enter calculations.

    Or do I have to do that in code? And is the calculated column added to the DataSet before it fills the DataGridView? Or is it added to the DataGridView, and then you loop through the rows in the DataGridView to perform the calculation?

    Thursday, February 15, 2018 6:07 AM

Answers

  • Select the table in the xsd designer file, add a new query

    A default SELECT appears (assuming you selected to return rows), add the calculated field

    Click Finish, you will get a warning that this is not desirable.

    In the load event change your fill method to the new Fill method for the above query. So I named the one above FillByExample (as this is an example for you).

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DemoDataSet.Products' 
        'table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.FillByExample(Me.DemoDataSet.Products)
    
    End Sub

    In the DataGridView designer, add a new unbound column e.g. (note DataPropertyName matches that in the SELECT) for our calculated column and set ReadOnly = true.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Robert in SF Friday, February 16, 2018 2:01 AM
    Thursday, February 15, 2018 10:41 AM
    Moderator

All replies

  • If have a DataSet that connects to an Access *.accdb file to feed a DataGridView on a form.

    I would like to add a column to the DataGridView form that shows, for example, the result of ColumnA * ColumnE.

    Is it possible to do this in the Designer? I seem to be able to add the columns to either the DataSet or the DataGridView, but I can't find a way to enter calculations.

    Or do I have to do that in code? And is the calculated column added to the DataSet before it fills the DataGridView? Or is it added to the DataGridView, and then you loop through the rows in the DataGridView to perform the calculation?

    Hi Robert,

    You get one datatable from Access database, then you want to display ColumnA * ColumnE value in datagridview. I don't find way to create calculation in DataGridview, so I suggest you can add new column in DataTable, then calculate ColumnA * ColumnE value in DataTable, finally you will get new DataTable, you can bind this datatable to datagridview.datasource, it is very simple.

    Like this:

    Dim dt As New DataTable
            dt.Columns.Add("ColumnA", GetType(Integer))
            dt.Columns.Add("ColumnB", GetType(Integer))
            dt.Rows.Add(12, 10)
            dt.Rows.Add(13, 100)
            dt.Rows.Add(14, 100)
    
    
            dt.Columns.Add("ColumnTotal", GetType(Integer))
    
            For i As Integer = 0 To dt.Rows.Count - 1
                dt(i)("ColumnTotal") = dt(i)("ColumnA") * dt(i)("ColumnB")
            Next

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 15, 2018 8:50 AM
    Moderator
  • Robert,

    You can add expression columns to a datatable, but not in the designer 

    https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx

    Be aware this added column is not added to your Access datatable but only visible in your grid if this is the dataSource

       Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            dt.Columns.Add("ColumnTotal", GetType(Integer), "ColumnA * ColumnE")
        End Sub


    Success
    Cor


    • Edited by Cor Ligthert Thursday, February 15, 2018 9:03 AM
    Thursday, February 15, 2018 9:01 AM
  • Select the table in the xsd designer file, add a new query

    A default SELECT appears (assuming you selected to return rows), add the calculated field

    Click Finish, you will get a warning that this is not desirable.

    In the load event change your fill method to the new Fill method for the above query. So I named the one above FillByExample (as this is an example for you).

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DemoDataSet.Products' 
        'table. You can move, or remove it, as needed.
        Me.ProductsTableAdapter.FillByExample(Me.DemoDataSet.Products)
    
    End Sub

    In the DataGridView designer, add a new unbound column e.g. (note DataPropertyName matches that in the SELECT) for our calculated column and set ReadOnly = true.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Robert in SF Friday, February 16, 2018 2:01 AM
    Thursday, February 15, 2018 10:41 AM
    Moderator
  • Thank you all for your answers. I can only mark one answer, so I'm marking Karen's because she went the extra mile with screenshots and a very detailed answer. Thanks again!
    Friday, February 16, 2018 2:00 AM
  • Thank you all for your answers. I can only mark one answer, so I'm marking Karen's because she went the extra mile with screenshots and a very detailed answer. Thanks again!

    Bor those who see this, the not desirable message which comes is that this makes that with the normal methods from a DataAdapter or TableAdapter or Commandbuilder, the update has become impossible. 

    By the way, you can mark as much answer as you wish. However, test it before you do it, because otherwise it is senseless.


    Success
    Cor


    Friday, February 16, 2018 4:00 AM