MySQL String Not Converting to .NET Time

Question

I am trying to convert a MySQL time to a string using VB.NET.

    Dim adpt As New MySqlDataAdapter(dbcmdstring, connection)
    Dim myDataTable As New DataTable()
    adpt.Fill(myDataTable)

    DataGridView1.DataSource = myDataTable
    DataGridView1.Columns.Remove("ActivityID")
    DataGridView1.Columns.Remove("ActivityDate")
    DataGridView1.Columns.Remove("UserID")
    DataGridView1.Columns(0).HeaderCell.Value = "Name"
    DataGridView1.Columns(1).HeaderCell.Value = "Start Time"
    DataGridView1.Columns(2).HeaderCell.Value = "End Time"
    DataGridView1.Columns.Add("Duration", "Duration")
    DataGridView1.RowHeadersVisible = False

    Dim duration As New TimeSpan
    Dim durationStr As String = ""
    Dim i As Integer = 0
    For Each row As DataGridViewRow In DataGridView1.Rows
        duration = Date.Parse(row.Cells(2).Value.ToString).Subtract(Date.Parse(row.Cells(1).Value.ToString))
        durationStr = Math.Round(duration.TotalMinutes).ToString & ":" & Math.Round(duration.TotalSeconds).ToString
        row.Cells(3).Value = durationStr
    Next

When the date is parsed during the construction of the duration variable, it throws an error:

An unhandled exception of type 'System.NullReferenceException' occurred in WindowsApplicationSQL.exe

Additional information: Object reference not set to an instance of an object.

I can successfully parse the date and show it in a messagebox, but not convert it to a useable string. I have also tried using just the .value of the time as well.

Any help?

Community · Accepted Answer · 2020-06-20 09:12:55Z

There is a much easier way to get a Duration, and a much, much easier way.

Part of the problem is this: I am trying to convert a MySQL time to a string using VB.NET. There is no need to convert to string. If the column is in fact, a Time() column in MySQL, it's has a NET counterpart: TimeSpan and it can be easily used to calculate a difference using subtraction.

Much Easier Method

dtLog.Columns.Add(New DataColumn("Duration", GetType(TimeSpan)))
For Each r As DataRow In dtLog.Rows
    r("Duration") = r.Field(Of TimeSpan)("EndTime") - r.Field(Of TimeSpan)("StartTime")
Next

If you are using a DataSource, it is rarely a good idea to manipulate the data thru the DataGridView. Note: no strings were needed.

Much, MUCH Easier Method

Perform the operation in SQL:

Dim sql = "SELECT ... StartTime, EndTime, TIMEDIFF(EndTime, StartTime) As Duration FROM ActivityLog"

This will create a Duration column in the DataTable containing the result. Similarly, if you dont want certain columns, you can omit them from the SQL to start rather than removing DGV Columns.

Object reference not set to an instance of an object.

Finally, this error probably has nothing to do with the conversion of MySQL to VB, strings or TimeSpans. By default, the DGV has that extra row at the bottom - the NewRow for the user to start adding data - but all the cells are Nothing. So:

For Each row As DataGridViewRow In DataGridView1.Rows

This will try to process the NewRow and poking around it's cells will result in an NRE. When you switched to the datatable, it went away because they dont have the extra row. You still don't need all those gyrations though.

user3295336 · Accepted Answer · 2016-04-12 21:10:04Z

I tried, instead, doing it via the DataTable rather than the DataGridView and it worked!

    myDataTable.Columns.Add("Duration", GetType(String))
    myDataTable.Columns.Remove("ActivityID")
    myDataTable.Columns.Remove("ActivityDate")
    myDataTable.Columns.Remove("UserID")

    DataGridView1.DataSource = myDataTable
    DataGridView1.RowHeadersVisible = False
    DataGridView1.Columns(0).HeaderCell.Value = "Name"
    DataGridView1.Columns(1).HeaderCell.Value = "Start Time"
    DataGridView1.Columns(2).HeaderCell.Value = "End Time"

    Dim duration As New TimeSpan
    Dim durationStr As String = ""
    Dim i As Integer = 0
    For Each row As DataRow In myDataTable.Rows
        duration = Date.Parse(row.Item("EndTime").ToString).Subtract(Date.Parse(row.Item("StartTime").ToString))
        durationStr = Math.Round(duration.Minutes).ToString & ":" & Math.Round(duration.Seconds).ToString
        row.Item("Duration") = durationStr
    Next

Collectives™ on Stack Overflow

MySQL String Not Converting to .NET Time

2 Answers 2

Much Easier Method

Much, MUCH Easier Method

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Much Easier Method

Much, MUCH Easier Method

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related