GridView data

I am displaying data from a MySQL database in a GridView.
All is cool with this code:

Code:

---

    Private Sub GetBooksUsageTime(ByRef UserID As String)
        Dim myDataAdapter As MySqlDataAdapter
        Dim myDataSet As New DataSet
        Dim strSQL As String
        con = New MySqlClient.MySqlConnection("Server=" + _host + ";User Id=" + _user + ";Password=" + _pass + ";")

        Try
            con.Open()
            'Check if the connection is open
            If con.State = Data.ConnectionState.Open Then
                con.ChangeDatabase("eBuki")

                strSQL = "SELECT isbn, start, end  FROM bookstime WHERE userid ='" & UserID & "'" ' ORDER BY grade"
                myDataAdapter = New MySqlDataAdapter(strSQL, con)
                myDataSet = New DataSet()
                myDataAdapter.Fill(myDataSet)

                gvBookTime.DataSource = myDataSet
                gvBookTime.DataBind()
                gvBookTime.HeaderRow.Cells(0).Text = "Book"
                gvBookTime.HeaderRow.Cells(1).Text = "Start Time"
                gvBookTime.HeaderRow.Cells(2).Text = "End Time"
                gvBookTime.HeaderRow.Cells(3).Text = "Total Time"

                gvBookTime.HeaderRow.Cells(0).HorizontalAlign = HorizontalAlign.Left
                gvBookTime.HeaderRow.Cells(1).HorizontalAlign = HorizontalAlign.Left
                gvBookTime.HeaderRow.Cells(2).HorizontalAlign = HorizontalAlign.Left
                gvBookTime.HeaderRow.Cells(3).HorizontalAlign = HorizontalAlign.Left

            End If
        Catch ex As Exception
            'MsgBox(ex.Message) ' Display any errors.
        End Try
        con.Close()
        con.Dispose()

    End Sub

---

What I need to do is a calculation on the datetime fields (end - start) and then display this in the "Total Time" column of the GridView.
I have been beating my head against the wall trying to achieve this.
Please and assistance would be greatly appreciated.

I'm not sure whether it will work with dates or not but you could try a calculated column in your DataTable:

vb.net Code:

myDataTable.Columns.Add("Total Time", GetType(TimeSpan), "end - start")

That column will then be bound to your grid like any other. That would work for numbers but I'm not 100% sure whether a DataTable can subtract Dates.

By the way, why bother creating a DataSet when you only want to use one DataTable? Just create a DataTable.

Jumping in here JM.

In your Select query use a calculation. I believe mySQL has a number of date & time functions.
The following will return a new column called difference that contains the seconds elapsed.
Look up options in mySQL help.

SELECT isbn, start, end, TIMESTAMPDIFF(SECOND,start, end) as difference
FROM bookstime
WHERE userid ='" & UserID & "'" ' ORDER BY grade

P.S. Since mySQL is not one of my areas I am not sure which should come first in the function.
start, end or end, start.