binding a mysql table on vb05 datagridview error

[thelner]

Im trying to bind a table in mysql containing a mysql date field in a datagridview in vb.net 05. When i only select fields containing non-dates it works, but once i add a date field it generates this error:

Unable to convert MySQL date/time value to System.DateTime


Any work around for this?

Thanks in advance. ;)

[thelner]

Can anybody help me please...

[insomnix]

I've run into the same problem. I've tried to dynamically create a List View. My tests work, but I think I still prefer a Data Grid View.

[insomnix]

I don't know if this helps you, but I wound up using a ListView to show the data. It uses a TreeView to show the table names.

Imports System.Data.OleDb
Imports MySQLDriverCS
Imports System.Data

Public Class Main
Private MyConnectionString As String = ""
Private Database As String = ""
Private Sub FetchTableList()
Dim mVals(255) As String
Dim mBuiltCon As String = Nothing
Dim i As Integer = 0
Dim myConnection As New MySQLConnection(MyConnectionString) '(mBuiltCon)
myConnection.Open()
Dim Cmd As New MySQLCommand("SHOW TABLES FROM " + Database, myConnection)
Dim RD As MySQLDataReader = Cmd.ExecuteReader()
Try
If RD.HasRows Then
While RD.Read()
mVals(i) = RD.GetString(0)
trvTableList.Nodes.Add(mVals(i))
i += 1
End While
End If
Cmd.Dispose()
RD.Close()
Catch ex As Exception
MsgBox("Error while executing: (" & Cmd.CommandText & ")" & _
vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "UCM")
Finally
If myConnection.State = ConnectionState.Open Then myConnection.Close()
mBuiltCon = Nothing
End Try
End Sub
Private Sub FetchTableColumns(ByVal TableName As String)
Dim mVals(255) As String
Dim mBuiltCon As String = Nothing
Dim i As Integer = 0
Dim myConnection As New MySQLConnection(MyConnectionString)
myConnection.Open()
Dim Cmd As New MySQLCommand("SHOW COLUMNS FROM " & TableName & " FROM " + Database, myConnection)
Dim RD As MySQLDataReader = Cmd.ExecuteReader()
Try
If RD.HasRows Then
While RD.Read()
mVals(i) = RD.GetString(0)
lstSQLList.Columns.Add(RD.GetString(0), 100, HorizontalAlignment.Left)
i += 1
End While
End If
Cmd.Dispose()
RD.Close()
Catch ex As Exception
MsgBox("Error while executing: (" & Cmd.CommandText & ")" & _
vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "UCM")
Finally
If myConnection.State = ConnectionState.Open Then myConnection.Close()
mBuiltCon = Nothing
FetchTableData(TableName)
End Try
End Sub
Private Sub FetchTableData(ByVal TableName As String) ', ByVal TotalColumns As Integer)
Dim mVals(255) As String
Dim mBuiltCon As String = Nothing
Dim myConnection As New MySQLConnection(MyConnectionString) '(mBuiltCon)
myConnection.Open()
Dim Cmd As New MySQLCommand("SELECT * FROM " & TableName, myConnection)
Dim RD As MySQLDataReader = Cmd.ExecuteReader()
Try
If RD.HasRows Then
While RD.Read()
Dim Column0 As String = IIf(IsDBNull(RD(0)), "", RD(0))
Dim item As New ListViewItem(Column0)
For i = 1 To RD.FieldCount - 1
If IsDBNull(RD(i)) Then
item.SubItems.Add("")
Else
item.SubItems.Add(RD(i))
End If
Next
lstSQLList.Items.Add(item)
End While
End If
Cmd.Dispose()
RD.Close()
Catch ex As Exception
MsgBox("Error while executing: (" & Cmd.CommandText & ")" & _
vbCrLf & "Msg: " & ex.Message.ToString, MsgBoxStyle.Exclamation, "UCM")
Finally
If myConnection.State = ConnectionState.Open Then myConnection.Close()
mBuiltCon = Nothing
End Try
End Sub
Private Sub trvTableList_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles trvTableList.AfterSelect
lstSQLList.Columns.Clear()
lstSQLList.Items.Clear()
FetchTableColumns(e.Node.Text)
End Sub
Private Sub Main_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
FetchTableList()
End Sub
End Class

[insomnix]

Better solution, a friend sent me this link and it fixed all my problems. Try this driver.

http://dev.mysql.com/downloads/connector/net/5.0.html