OdbcDataAdapter and MySQL

[Mikael Thorsson]

Hello! Here are two pieces of C# that connect to MySQL. The first one
works, the second one raises an exception on the Fill call. The Open
succeeds in both cases.

What am I doing wrong?

************* Works:

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=****" +
"UID=root;" +
"PASSWORD=****;" +
"OPTION=3";

OdbcConnection conn = new OdbcConnection(MyConString);

conn.Open();

OdbcDataReader myDataReader;

OdbcCommand myCommand = new OdbcCommand("SELECT * FROM User", conn);

myDataReader = myCommand.ExecuteReader();

while (myDataReader.Read())
{
Console.WriteLine("Data: " + myDataReader.GetString(0) + " " +
myDataReader.GetString(1));
}

conn.Close();

************* Fails:

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=*****;" +
"UID=root;" +
"PASSWORD=****;" +
"OPTION=3";

OdbcConnection conn = new OdbcConnection(MyConString);

conn.Open();

OdbcDataAdapter a = new OdbcDataAdapter("SELECT * FROM User", conn);

DataSet d = new DataSet();

a.Fill(d, "User"); // This raises the exception!

conn.Close();

************** The exception:

Unhandled Exception: System.InvalidCastException: Specified cast is
not valid.
at System.Data.Common.DbDataAdapter.get_SelectCommand()
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
at csClient.DataGridSample.ConnectToData2() in ...

 

[Mohamoss]

Hi
it seems like it shoud work fine " in fact i tried it and it worked fine"
. However , try to remove the the user from fill make it a.Fill(d) , since
it will creat the table User anyway insidet he dataset d and tlet us know
what will happened

 

[Mikael Thorsson]

Sorry, same problem I'm afraid

I guess this means that there's something fishy about my setup? Here
is a list of the versions of the references that I have in my project.
Can you please tell me if you see any problems here?

Microsoft.Data.Odbc, version 1.0.3300.0
System, System.Data, System.Drawing, System.Windows.Forms, System.XML,
all version 1.0.5000.0.

I added the odbc reference by browsing to

C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll

I'm running XP Professional SP1 and I also installed the latest MDAC.

Any ideas appreciated!

 

[Austin Ehlers]

Sorry, same problem I'm afraid

I guess this means that there's something fishy about my setup? Here
is a list of the versions of the references that I have in my project.
Can you please tell me if you see any problems here?

Microsoft.Data.Odbc, version 1.0.3300.0
System, System.Data, System.Drawing, System.Windows.Forms, System.XML,
all version 1.0.5000.0.

I added the odbc reference by browsing to

C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll

I'm running XP Professional SP1 and I also installed the latest MDAC.

Any ideas appreciated!

Two things that might be going on:

1) Don't use Microsoft.Data.Odbc reference; use System.Data
reference, and "using System.Data; using System.Data.Odbc;" instead.
You'll need ODBC.NET driver (not included with VS):
http://www.microsoft.com/downloads/...27-1017-4F33-A062-D165078E32B1&displaylang=en

Configure the driver in Control Panel>Administrative Tools>Data
Sources. Select "System DSN">Add>MySQL ODBC 3.51 Driver DSN. Use the
following code to connect:

OdbcConnection conn=new OdbcConnection("DSN=/*the name you chose
just*/");

2) I remember reading somewhere that the Odbc driver has a problem
with certain types of tables, specifically MyISAM types. Try changing
it to InnoDB.

Austin

 

[Mikael Thorsson]

Thank you for that, Austin! I changed my References and "using"
statements as you suggested, and added a System DSN.

I guess there must be some problem with my framework versions, because
now I get an exception when _opening_ the connection. I've recently
installed the 1.1 dotnet framework, and to make VisualStudio reference
it, I added this to my References Path:

C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322

Maybe this is the problem?

I discovered today that Framework 1.1 contains the ODBC DLLs already,
so there should be no need to install them separately, see
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp
(look for "ODBC .NET Data Provider").

The exception I get this time is:

------------

An unhandled exception of type 'System.TypeLoadException' occurred in
system.data.dll

Additional information: Invalid PInvoke metadata format.


Unhandled Exception: System.TypeLoadException: Invalid PInvoke
metadata format.
at Microsoft.Win32.UnsafeNativeMethods.GetFileVersionInfoSize(String
lptstrFilename, Int32[] lpdwHandle)
at System.Diagnostics.FileVersionInfo.GetVersionInfo(String
fileName)
at System.Data.Common.ADP.GetVersionInfo(String filename)
at System.Data.Odbc.OdbcGlobalEnv.GetGlobalEnv()
at System.Data.Odbc.OdbcConnection.Open()
at csClient.DataGridSample.ConnectToData2() in
c:\pbr\csclient\datagridsample.cs:line 80
at csClient.DataGridSample.InitializeComponent() in
c:\pbr\csclient\datagridsample.cs:line 34
at csClient.DataGridSample..ctor() in
c:\pbr\csclient\datagridsample.cs:line 19