data grid view multiply cells error

[HibaPro]

i have two row in my dataGridView one store the labour working hour (Time) and the other one is store the cost of one working hour , i need to multiply these two row but its give me this error :

operator * is not defined for type timespan and type double .

row.Cells(6).Value = (row.Cells(4).Value) * CDbl(row.Cells(5).Value)

[kRod]

HibaPro, you can get the totalseconds of your timespan and use that to create a double then do the multiplaction. TimeSpan.TotalSeconds

[TnTinMN]

Hiba,

No offense intended, but the DataGridView control is really intended as a viewer for data in a tabular format and not as a spreadsheet. It would be better to handle these calculations in the backing data storage. Even if the data is not pulled from a database, it is much easier to create a datatable with a computed column and set the DGV's datasource to your created table.

Let's assume you are using a database for that you have created a dataset for through the designer. We will edit the datasource. This is just a simple Access DB that I created, but the type of database is irrelevant.

Now we are going to add a new column to hold the result of multiplying "Hours_Worked" by "Rate". We will name this column "Cost".

Go to the properties for "Cost" and set its DataType to decimal.

Ok, now we have cost column in the dataset, however it does not exist in the database. We will create it in our fill query.

Click the "Next" button until you reach the Sql screen.

We will edit the "Select" statement to add the "Cost" column
Hours_Worked * Rate As Cost

Click "Next" and give a name for the two methods.

Click "Finish". Now let's work on the DGV and add our new column.

Now we have to modify the Form's Fill statement and use our new query.

*** Edit: Add this statement beneath the Fill statement in the Load handler.
Me.PlayDataSet.Labor.Columns("Cost").Expression = "[Hours_Worked]*[Rate]"
This will automatically calculate Cost if you are using the DGV for data entry/editing.
*** End Edit.

That's it. Run it.

Not very pretty with the decimal points. Let's Edit the DGV columns and set a cell format for the "Cost" column.

Type "C2", for currency with two decimal places into format box. and click OK. Do the same for the "Rate" column.

Run it again. Much better.

[ome2012]

can show us the time format??? on how it is displayed in your datagrid...

[HibaPro]

the time format is 00:00:00 0 is time(7), i will try TnTinMN method , and i will see what will be going.

[Gé48]

Hi, is the format for time "00:00:00" needed in your DataGrid or can it be the hours worked like "3,1" if so then it is no problem.

[TnTinMN]

the time format is 00:00:00 0 is time(7), i will try TnTinMN method , and i will see what will be going.

The SQL Time datatype will through a wrench in the machinery for using the datatable expression. This datatype gets converted to a .Net TimeSpan and there is no way that I know of to achieve the data manipulations necessary to get this to a decimal value need to compute "Cost" in a datacolumn expression. You can follow the example I provided up to the point of adding the expression statement with this minor change to the Select statement in the query.

SELECT Empl_Name, Hours_Worked, Rate, CAST(CAST(Hours_Worked AS DateTime) AS Float) * 24.0 * CAST(Rate AS Float) AS Cost FROM Labor

In the case where you would be using the DGV for entry/editting, it will be necessary to handle the CellValueChanged event. Here is an example for that.

   Private Sub LaborDataGridView_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles LaborDataGridView.CellValueChanged
      If Not LaborDataGridView.IsHandleCreated Then Exit Sub 'handles initialization

      If LaborDataGridView.Columns(e.ColumnIndex).DataPropertyName = "Hours_Worked" OrElse _
         LaborDataGridView.Columns(e.ColumnIndex).DataPropertyName = "Rate" Then
         'get the underlying datatable row
         Dim dr As DataRow = CType(LaborDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView).Row
         'set cost to reflect changes
         dr("Cost") = CType(dr("Hours_Worked"), TimeSpan).TotalHours * CType(dr("Rate"), Decimal)
         LaborDataGridView.InvalidateRow(e.RowIndex) 'refresh the row with new value
      End If

   End Sub

[brylle]

Hope you can get and idea for this.. :)

 For i = 0 To DataGridView1.Rows.Count - 2
            DataGridView1.Item(3, i).Value = DataGridView1.Item(1, i).Value * DataGridView1.Item(2, i).Value
        Next

[Gé48]

Hi, you are getting the error: operator is not defined for type timespan and type double. because the format of timespan "00:00:00" is a value that can't be multiplied. If you convert hours:minutes:seconds to one value for hours it will work. Attached a piece of code I wrote and tried that does the trick. It also rounds of the value of the cost at two digits. It is in the keyup event of the datagrid and loops through all the rows of the grid by pushing the down arrow. Just adapt it to your liking, good luck.

Private Sub DataGridView1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyUp
        Dim temp As String = 0, temp1 As Double = 0
        If e.KeyCode = Keys.Down Then
            On Error Resume Next
            For i = 0 To DataGridView1.CurrentRow.Index - 1
                temp = DataGridView1.Rows(i).Cells("Column1").Value
                temp = temp.Substring(0, 2) + (temp.Substring(3, 2) / 60) + (temp.Substring(6, 2) / 3600)
                temp1 = temp * DataGridView1.Rows(i).Cells("Column2").Value
                DataGridView1.Rows(i).Cells("Column3").Value = Math.Round(temp1, 2)
            Next i
        End If
    End Sub

[HibaPro]

TnTinMN method is working into my project thnx all.