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">
                        <asp:TextBox ID="FlexiBoughtForward" runat="server" Text='<%# Bind("FlexiBoughtForward") %>' > </asp:TextBox>
                       <asp:Label ID="Label7" runat="server" Text='<%# GetTotalFelxiTime(DataBinder.Eval(Container.DataItem, "Timeworked"), DataBinder.Eval(Container.DataItem, "FlexiBoughtForward")) %>'></asp:Label>

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
        totalFlexiTime = (FlexiBoughtForward + (Timeworked - 7.32))
        Return totalFlexiTime
    End Function

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rog DSQL Developer / Web Development / Business AnalysisCommented:
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.

sadee52Author Commented:
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 DissanayakeSenior Web DeveloperCommented:
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

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rog DSQL Developer / Web Development / Business AnalysisCommented:

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 ....

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

Sachintana DissanayakeSenior Web DeveloperCommented:
Such calculated columns should bind in either rowdatabinding or rowCreated event.
Handling it in the SQL is not recommended.
Rog DSQL Developer / Web Development / Business AnalysisCommented:

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.

Sachintana DissanayakeSenior Web DeveloperCommented:
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.
sadee52Author Commented:
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 DissanayakeSenior Web DeveloperCommented:
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?
Rog DSQL Developer / Web Development / Business AnalysisCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Forced accept.

EE Admin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.