-
Sep 3rd, 2016, 10:14 AM
#1
Thread Starter
New Member
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.
-
Sep 3rd, 2016, 08:51 PM
#2
Re: GridView data
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.
-
Sep 4th, 2016, 04:18 AM
#3
Re: GridView data
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.
Burn the land and boil the sea
You can't take the sky from me
~T
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|