MySQL Time is TimeSpan?

[NervousRex]

I am trying to return some data from a MySQL database, I am having trouble reading one of the columns though...

The columns datatype is set as Time.

When I read it through the datareader, it is of TimeSpan type. If I do a myDR("time").tostring it gives me 00:00:00 for every row.


My question is, how can I get this column to display correctly as hours:mins:secs?

 

[woyler]

Are the actual values in the database correct?

 

[NervousRex]

Yes the values are correct.

I changed the query to convert the column to a datetime, which I think will work, but still would like to know if there is a way to do it in VB.net

 

[NervousRex]

Actually, even converting it to date isn't working, now I can't even get it to display the column

Here is my query:

"SELECT agent_id, start_date, CONVERT(start_time, DATETIME) as 'start_time' FROM recordings WHERE start_date = '" & sDate & "'"


Here is the code where I write the rows to a list box:

Me.ListBox1.Items.Add(MyDataReader("agent_id") & " " & MyDataReader("start_date") & " " & MyDataReader("start_time"))


Here is what is displayed:

1004 3/9/2006



What I'm trying to get is:

1004 3/9/2006 09:55:06

 

[SiriusBlackOp]

Maybe this will help???

        Dim t As Date = CDate(MyDataReader("start_time"))
        Dim s As String = t.Hour & ":" & t.Minute & ":" & t.Second
        Me.ListBox1.Items.Add(MyDataReader("agent_id") & " " & MyDataReader("start_date") & " " & s)

At least it will give you some insight.

Senior Software Developer

http://www.scoutsft.com

 

[NervousRex]

Close

System.InvalidCastException: Conversion from type 'DBNull' to type 'Date' is not valid.


Thing is, there are no Null values, I run the query in query browser and everyone of the 2445 rows has a time (and nulls aren't allowed).

 

[SiriusBlackOp]

Well... if you are not getting a IndexOutOfRangeException
then the start_date column is there... so (grasping at straws)... have you checked all of the values in the query to make sure that they ALL are not null?


Senior Software Developer

http://www.scoutsft.com

 

[NervousRex]

OK, not sure how to answer that as I truly don't know what the case is.

Heres the deal, when I run the query in the query browser, ALL 2445 rows have values in every column, there are NO nulls.

When I run the same exact query in my app, and list the results, there are 3 rows that have no value in start_time (but they have values in the DB)


Then if I put the convert into the query, run it in query browser, it has 2445 rows, all with values which look like "0000-00-00 09:55:46", so the time is right, and since there is no date, only a time, I don't know if that is screwing up the code or what, but when I run it in my app....all 2445 rows have a null value in start_time (which isn't true!!)


This is driving me insane, something so simple as reading a date from a database has never given me such a headache. Please keep the ideas rolling in, as I'm running out.

 

[NervousRex]

Ok, resolved why 3 rows have no start_time value

each has one common factor, there values have a 00 in it such as 09:00:03 or 10:00:38 but thats still referrs back to my first post, they display as 00:00:00 no matter what the value is as its coming in as a TimeSpan which can't be cast to string or date

 

[SiriusBlackOp]

I understand your frustration... If I were you, I would probably start thinking about storing the data as a varchar and use vb to parse it in and out manually.

Nothing worse then getting stuck on the stupid issue watch the minutes tick by.



Senior Software Developer

http://www.scoutsft.com

 

[NervousRex]

Sounds like a great idea...unfortunatly the database is for a 3rd party application I did not design and have no control over.

And in saying that, finally got it to work by casting the column in the query to a CHAR field, tryed this ealier but with the wrong mysql syntax


Thanks for the help, you kept me sane

 

[SiriusBlackOp]

Glad you figured it out!

Senior Software Developer

http://www.scoutsft.com