asked on

Gridview add columns

i have data gridview which is bound to sqldatasource i want to do some think llike this
StarttimeMorning EndtimeMorning StarttimeEvening EndTimeEvening TotalTime FlexiBoughtForward
row1 8.30 12.30 1.00 5.00 8.00 0.66
row2 9.00 12:30 1.00 5.30 8.00 1.32
I want to use this formula to calculate Flexitime bought forward Flexiboughtforward=(totaltime-7.24 )+Flexiboughtforward
In the 2nd row the flexiBoughtForward is 0.66 so the value should be calculated like Flexiboughtforward=(8.00-7.24)+.66
in other words i need total of FlexitimeForward column values

i am doing in the way below but in my Function GetTotalFelxiTime i m not getting the sum of columns
so calculations r not giving required results

<asp:TemplateField HeaderText="Accrued flexi time" SortExpression="FlexiBoughtForward">
<EditItemTemplate>
<asp:TextBox ID="FlexiBoughtForward" runat="server" Text='<%# Bind("FlexiBoughtForward") %>' > </asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# GetTotalFelxiTime(DataBinder.Eval(Container.DataItem, "Timeworked"), DataBinder.Eval(Container.DataItem, "FlexiBoughtForward")) %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>

Public Function GetTotalFelxiTime(ByVal FlexiBoughtForward As Object, ByVal Timeworked As Object) As Double
For Each row As GridViewRow In GridView1.Rows
Dim cb As TextBox = row.FindControl("Timeworked")
If IsDBNull(Timeworked) Then
Timeworked = 0.0
End If
If IsDBNull(FlexiBoughtForward) Then
FlexiBoughtForward = 0.0
End If
Next
totalFlexiTime = (FlexiBoughtForward + (Timeworked - 7.32))
Return totalFlexiTime
End Function

Rog D

I would suggest doing this calculation in your SQL Query instead of in code. This way it comes back just as your other colums do.

Rog

sadee52

ASKER

how can i do that in SQL Query instead my select command is as follow
SelectCommand="SELECT [MyDate], [StartTime], [FinishTime], [StartTimeEvening], [FinishTimeEvening], [FlexiBoughtForward], [Timeworked] FROM [flexitime] Where (MyDate >= (@MyDate)) AND (MyDate <= @NowDate)"

Sachintana Dissanayake

Here is an another easy way do it in code level.
Use a hidden control or invisible to keep the value of the Flexiboughtforward.

Remove For Each loop from the GetTotalFelxiTime() function and rearrange as below.
Please change the template function as well.
<asp:Label ID="Label7" runat="server" Text='<%# GetTotalFelxiTime(DataBinder.Eval(Container.DataItem, "Timeworked")) %>'></asp:Label>

    Public Function GetTotalFelxiTime(ByVal Timeworked As Object) As Double
        Dim totalFlexiTime As Double
        If (Not Timeworked Is Nothing) Then
            totalFlexiTime = (CType(lblTotalBroughtForward.Text, Double) + (CType(Timeworked, Double) - 7.32))
            lblTotalBroughtForward.Text = totalFlexiTime.ToString()
        End If
 
        Return totalFlexiTime
    End Function

Open in new window

Rog D

Ok....

Now I see your delima.

one way would be to do this on the "RowDatabound" event in the gridview. You can get all the information there and have a variable that will hold the FlexiBoughtForward sum on each row bound.

You can then set the valule of the FlexiBoughtForward cell to the variable.

This event happens on each rows binding.

The SQL way would require some Sub Selects but could be accomplished. You could select have a column with a select

Select a, b, c, (Select sum(abc) from table1 where a = XX) as d, .......
From ....
Where....

The rowdatabinding event is much easier to work with than the complex SQL

Rog

Sachintana Dissanayake

Such calculated columns should bind in either rowdatabinding or rowCreated event.
Handling it in the SQL is not recommended.

Rog D

Sachintana,

There are times when you do want to do some calculations in the SQL. Say you have a query or view that will be used a lot of places. And it needs some sort of total. It makes sense to do it in the query not always have to code for that total. No reason to do the work many times when the one query will do it all the time.

Rog

Sachintana Dissanayake

Your argument is fairly correct for views if you are considering about the re-usability, but not always.
Not for a field like aggregation field, since you should not complex SQL statement unnecessarily.

sadee52

ASKER

I did try to do it with SQL Query but i was unable to do it..Sachintana i did try your code but at line
i get the error

totalFlexiTime = (CType(lblTotalBroughtForward.Text, Double) + (CType(Timeworked, Double) - 7.32))

Conversion from string "" to type 'Double' is not valid. and inner exception is {"Input string was not in a correct format."}

i did try Cdbl(lblTotalBroughtForward.Text) but same error.

Sachintana Dissanayake

I am sorry about the error message...
The value should be a number such as at least "0" or "-1", you can't pass empty values to CTYpe-Double conversion method.

You can just double check the value lblTotalBroughtForward.Text is Nothing or empty.
If (Not String.IsNullOrEmpty(lblTotalBroughtForward.Text)) Then

What;s the value of the lblTotalBroughtForward.Text when you debugging? Is it empty?

ASKER CERTIFIED SOLUTION

Rog D

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Computer101

Forced accept.

Computer101
EE Admin