Results 1 to 3 of 3

Thread: GridView data

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2012
    Posts
    11

    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.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

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

  3. #3
    Frenzied Member Gruff's Avatar
    Join Date
    Jan 2014
    Location
    Scappoose Oregon USA
    Posts
    1,293

    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
  •  



Click Here to Expand Forum to Full Width