[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.
1 Attachment(s)
Re: Caluclated Fields in a Datagrid
Quote:
Originally Posted by
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.
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.
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!
1 Attachment(s)
Re: Caluclated Fields in a Datagrid
Quote:
Originally Posted by
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?
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.
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!
