MySQL Datetime --> VB.NET Datetime Conversion Prob

miCRoSCoPiC^eaRthLinG

Mar 8 2005, 11:31 AM

Hi,
Can anyone provide me with a quick example of fetching a MySQL Datetime Field and converting it into native VB.NET DateTime format ?? Say for example my db contains a couple of fields: Field1, Field2, DateField... one of which is the default MySQL DateTime.

Say I'm using the following code to connect...

CODE

ConnectionString = "....."
QueryString = "SELECT * FROM SomeTable"

Dim myConnection As New MySql.Data.MySqlClient.MySqlConnection(ConnectionString)
Dim myCommand As New MySql.Data.MySqlClient.MySqlCommand(QueryString, myConnection)
Dim myReader As MySqlDataReader

Try
myConnection.Open()
myReader = myCommand.ExecuteReader

While myReader.Read
Field1 = myReader.Item ("Field1")
Field2 = myReader.Item ("Field2")
Field3 = myReader.Item ("DateField")
End While

'Close the connection
myReader.Close()
myConnection.Close()

Catch exEvent As Exception

'Do Something

End Try

As you can see the query result is being broken up and stored into three separate fields, Field1, Field2 & Field3... Now:
1. If all these variables are declared as String - what would be the exact syntax to convert that DATETIME field into a String on the fly
2. If the Field3 variable is declared as VS.NET DateTime then what would be the syntax to convert it ..

I tried out a typecasting too:

CODE

While myReader.Read
Field3 = ctype(myReader.Item ("DateField"), string)
End While

In this case I receive an error message saying "Unable to convert mysql Date/Time value to System.DateTime"... BTW, am using the .NET Connector 1.0.4 and VS2003.

Any help would be really appreciated.

Thanks a million

Hercco

Mar 9 2005, 08:26 PM

I don't have actual solution for this and this might be a stupid suggestion but wouldn't using MySQL DATE_FORMAT to convert the data to suitable format first help?

miCRoSCoPiC^eaRthLinG

Mar 11 2005, 08:45 AM

QUOTE(Hercco @ Mar 10 2005, 03:26 AM)

I don't have actual solution for this and this might be a stupid suggestion but wouldn't using MySQL DATE_FORMAT to convert the data to suitable format first help?

I've tried using all sorts of date formatting options - the problem is that even if you format the date otherwise, it is still not returned as a STRING type to VB. Instead VB still gets a pointer to that reformatted mysql date which is still in the native mysql DATETIME type and cannot be cast into a STRING directly or even with help of the mysql connector.net dll - I mean I'm sure it can be done using the connector.net - but I'm unable to and that why searching for the right syntax.

If I can't perform this type conversion I'll have to resort to storing DATEs as CHAR/VARCHAR in MySQL - basically as STRINGS and not be able to use any of the great DATE/TIME calculation & handling features available in mysql....

miCRoSCoPiC^eaRthLinG

Apr 11 2005, 03:32 AM

Found a soution from MySQL Forums.

It worked with some additions to the connection string: "allow zero datetime=no" - that made the type cast work perfectly. Here's the working code again for your reference.

CODE

Dim BirthDate as Date
.....
...........

ConnectionString = "Server=.....;....; allow zero datetime=no"
QueryString = "Select * from whatever"

'Code for query
......
......

BirthDate = CType ( myReader.Item ( "BirthDate" ), Date )

That's it - now you can format the BirthDate variable to any desired format using the VB.Net "Format" command. Also this way you don't have to specify how to pre-format and fetch a field in the Query String. A simple "SELECT *" suffices - you just pick that specific field and typecast it to the native VB.NET format.

MySQL Datetime --> VB.NET Datetime Conversion Prob - Any solutions ??

MySQL Datetime --> VB.NET Datetime Conversion Prob - Any solutions ??