GridView data-VBForums
Results 1 to 3 of 3

Thread: GridView data

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2012

    GridView data

    I am displaying data from a MySQL database in a GridView.
    All is cool with this 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 + ";")
                'Check if the connection is open
                If con.State = Data.ConnectionState.Open Then
                    strSQL = "SELECT isbn, start, end  FROM bookstime WHERE userid ='" & UserID & "'" ' ORDER BY grade"
                    myDataAdapter = New MySqlDataAdapter(strSQL, con)
                    myDataSet = New DataSet()
                    gvBookTime.DataSource = myDataSet
                    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
        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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Sydney, Australia

    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: 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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    Frenzied Member Gruff's Avatar
    Join Date
    Jan 2014
    Scappoose Oregon USA

    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


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