VS 2010 [RESOLVED] Caluclated Fields in a Datagrid-VBForums
Results 1 to 5 of 5

Thread: [RESOLVED] Caluclated Fields in a Datagrid

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2011
    Posts
    71

    Resolved [RESOLVED] Caluclated Fields in a Datagrid

    My program is databound to an access 2007 database. I have a datagridview showing rows as part of my screen and the other part of my screen is texboxes bound to a different table.

    What I currently have:

    1. Date in textbox from one table
    2. Age in Datagridview column from another table

    I need help with having a calculated date column in the datagridview that will be the textbox date + age (in days) in the datagrid column. I would also need this column to store in my database like any other field also. So if I typed in 2/17/11 into the textbox, and I typed in 2 in the age field, the calculated field would display and store 2/19/2011 in my datagrid and database. My dataset designer won't let me add a date to a string so I'm completely lost. A query calculates it easy in access but when I load the access query in my dataset it doesn't calculate anymore.

    Any help would be greatly appreciated.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,322

    Re: Caluclated Fields in a Datagrid

    Quote Originally Posted by jpiper View Post
    My program is databound to an access 2007 database. I have a datagridview showing rows as part of my screen and the other part of my screen is texboxes bound to a different table.

    What I currently have:

    1. Date in textbox from one table
    2. Age in Datagridview column from another table

    I need help with having a calculated date column in the datagridview that will be the textbox date + age (in days) in the datagrid column. I would also need this column to store in my database like any other field also. So if I typed in 2/17/11 into the textbox, and I typed in 2 in the age field, the calculated field would display and store 2/19/2011 in my datagrid and database. My dataset designer won't let me add a date to a string so I'm completely lost. A query calculates it easy in access but when I load the access query in my dataset it doesn't calculate anymore.

    Any help would be greatly appreciated.
    The attached zip file contains one form which you can try out by adding to a project and making it the start up form. It shows a direction rather than giving you a plug into your code solution.

    I load a DataTable where there are two Date fields, one of them has a value while the other does not. There is a NumericUpDown control that when you increment the value it adds days to the second Date Field displayed in a DataGridView and if you go back to zero the two dates will match.

    So you can examine the code and modify the logic, for instance the trigger might be pressing ENTER or leaving the TextBox with the date or better yet use a calendar control to ensure a proper date was entered etc. For saving the data that might be using an update statement the designer created for you or a manual update using a connection and command pair of objects.
    Attached Files Attached Files

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2011
    Posts
    71

    Re: Caluclated Fields in a Datagrid

    Thank you, that is definitely what I'm trying to do and much farther than I would have ever got.

    Can you give me a little bit more advice?

    Code:
    Dim d As New Date
            If Date.TryParse(bsData.Current("originalDate"), d) Then
                If NumericUpDown1.Value > 0 Then
                    CType((CType(bsData.Current, DataRowView)).Row, DataRow).Item("NewDate") = _
                        d.AddDays(CInt(NumericUpDown1.Value))
                Else
                    CType((CType(bsData.Current, DataRowView)).Row, DataRow).Item("NewDate") = _
                        DirectCast(bsData.Current, DataRowView).Row("originalDate")
                End If
                '
                ' Write code here to save data to your database table
                '
            End If
    This has an object controlling it, my program has the datagrid cell controlling it. How would you code it to replace NumericUpDown1.Value with a cell from my datagrid? I can make it work with textboxes, comboboxes, any object just not the datagrid. I'm close, just not quite there, keeps giving me errors.

    Thanks for getting me this far!

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,322

    Re: Caluclated Fields in a Datagrid

    Quote Originally Posted by jpiper View Post
    Thank you, that is definitely what I'm trying to do and much farther than I would have ever got.

    Can you give me a little bit more advice?

    Code:
    Dim d As New Date
            If Date.TryParse(bsData.Current("originalDate"), d) Then
                If NumericUpDown1.Value > 0 Then
                    CType((CType(bsData.Current, DataRowView)).Row, DataRow).Item("NewDate") = _
                        d.AddDays(CInt(NumericUpDown1.Value))
                Else
                    CType((CType(bsData.Current, DataRowView)).Row, DataRow).Item("NewDate") = _
                        DirectCast(bsData.Current, DataRowView).Row("originalDate")
                End If
                '
                ' Write code here to save data to your database table
                '
            End If
    This has an object controlling it, my program has the datagrid cell controlling it. How would you code it to replace NumericUpDown1.Value with a cell from my datagrid? I can make it work with textboxes, comboboxes, any object just not the datagrid. I'm close, just not quite there, keeps giving me errors.

    Thanks for getting me this far!
    Take a look at the additional code in the attached demo

    The BindingSource DataTable is now declared as follows

    Code:
    WithEvents dtData As New DataTable
    Which allows us to use the event ColumnChanged of the DataTable.
    Code:
        Private Sub dtData_ColumnChanged( _
            ByVal sender As Object, _
            ByVal e As System.Data.DataColumnChangeEventArgs) Handles dtData.ColumnChanged
    
            If e.Column.ColumnName = "originalDate" Then
                Dim d As New Date
                If Date.TryParse(bsData.CurrentRow("originalDate"), d) Then
                    bsData.WriteCurrentRow("NewDate", d.AddDays(CInt(NumericUpDown1.Value)))
                End If
                lblOriginalDate.Text = String.Format("Column Changed -> Original Date [{0}]", _
                                                     bsData.CurrentRow("originalDate"))
            End If
            If e.Column.ColumnName = "NewDate" Then
                lblNewDate.Text = String.Format("Column Changed -> New Date [{0}]", _
                                                bsData.CurrentRow("NewDate"))
            End If
        End Sub
    So if you change column data from the DataGridView it will be picked up in this event.

    I also added PositionChanged event of the BindingSource which allows you to examine data when the row changes in the DataGridView because of the BindingSource being notified of the row change.

    Also added a dummy Integer field to the underlying DataTable so you can see how you might verify the user when editing the column in the DataGridView entered a valid integer.

    Code:
        Private Sub DataGridView1_DataError( _
            ByVal sender As Object, _
            ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles DataGridView1.DataError
    
            Dim cell As DataGridViewCell = DataGridView1.Item(e.ColumnIndex, e.RowIndex)
            If cell.IsInEditMode Then
                Dim c As Control = DataGridView1.EditingControl
                If DataGridView1.Columns(e.ColumnIndex).Name = "AnotherFieldColumn" Then
                    Dim Value As Integer = 0
                    If Integer.TryParse(c.Text, Value) Then
                        c.Text = Value.ToString
                    Else
                        MsgBox("Enter a valid integer")
                        c.Text = bsData.CurrentRow("AnotherField")
                        e.Cancel = True
                    End If
                End If
            End If
        End Sub
    Lastly I added a code module which contains Extension methods which must be included for the code in the form to work. Not all methods are used as this is from a project I use to assist in situation such as this.

    Any ways hopefully this gives you what you need to code for your issue at hand.
    Attached Files Attached Files

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2011
    Posts
    71

    Re: Caluclated Fields in a Datagrid

    Kevin,

    Thank you man, you made my life much better lol here's my final code:

    Code:
    Private Sub DataGridView1_CellLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellLeave
            Dim d As New Date
            If Date.TryParse(bsData.Current("originalDate"), d) Then
                If bsData.Current("AnotherField") > 0 Then
                    CType((CType(bsData.Current, DataRowView)).Row, DataRow).Item("NewDate") = _
                        d.AddDays(CInt(bsData.Current("AnotherField")))
                End If
            End If
        End Sub
    Simply adds a scheduled number of days to the original date within the datagrid.

    THANK YOU!

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.