[RESOLVED] Caluclated Fields in a Datagrid

[jpiper]

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.

[kareninstructor]

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.

[jpiper]

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?

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!

[kareninstructor]

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?

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

WithEvents dtData As New DataTable

Which allows us to use the event ColumnChanged of the DataTable.

    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.

    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.

[jpiper]

Kevin,

Thank you man, you made my life much better lol here's my final 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!