Using C# with MySQL

[The Bear]

Has anyone successfully used MySQL with C#?

I'm having problems using the Update command


Here is the code snippet. I am able to connect to the database. However when
I try to update the database with the changes in the dataset I get the
following error:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: Dynamic SQL generation is not supported against a
SelectCommand that does not return any base table information.

public void setupConnection()
{
conString="Provider=MySQLProv;Data
Source=wedtest;SERVER=localhost;DB=wedtest;UID=admin;PWD=password;PORT=3306"
;

myConnection=new OleDbConnection(conString) ;

string_sql="select * from Budget";

myDataSet = new DataSet();

myConnection.Open() ;

if(myConnection.State==ConnectionState.Open)

{

Console.WriteLine("Connection made");

}

myOleDbAdapter = new OleDbDataAdapter(string_sql,myConnection);

myOleDbAdapter.Fill(myDataSet,"Budget") ;

dataGrid1.DataSource = myDataSet.DefaultViewManager;


myCommandBuilder=new OleDbCommandBuilder(myOleDbAdapter);

}

public void updateRecord()

{

// Get all of the updated rows and update the datastore

updatedRows = myDataSet.GetChanges(System.Data.DataRowState.Modified);

if (((updatedRows) != (null)))

{

myOleDbAdapter.Update(updatedRows,"Budget");

}

}


-The Bear

 

[Tom Carter]

Hi,

Because the mysql syntax may be a bit constrained I would consider using properly verified DDL to update your data.

--
~~~~~~~~~~
Tommie Carter
tcarternyc(at)hotmail.com (messenger use)
--
Has anyone successfully used MySQL with C#?

I'm having problems using the Update command


Here is the code snippet. I am able to connect to the database. However when
I try to update the database with the changes in the dataset I get the
following error:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: Dynamic SQL generation is not supported against a
SelectCommand that does not return any base table information.

public void setupConnection()
{
conString="Provider=MySQLProv;Data
Source=wedtest;SERVER=localhost;DB=wedtest;UID=admin;PWD=password;PORT=3306"
;

myConnection=new OleDbConnection(conString) ;

string_sql="select * from Budget";

myDataSet = new DataSet();

myConnection.Open() ;

if(myConnection.State==ConnectionState.Open)

{

Console.WriteLine("Connection made");

}

myOleDbAdapter = new OleDbDataAdapter(string_sql,myConnection);

myOleDbAdapter.Fill(myDataSet,"Budget") ;

dataGrid1.DataSource = myDataSet.DefaultViewManager;


myCommandBuilder=new OleDbCommandBuilder(myOleDbAdapter);

}

public void updateRecord()

{

// Get all of the updated rows and update the datastore

updatedRows = myDataSet.GetChanges(System.Data.DataRowState.Modified);

if (((updatedRows) != (null)))

{

myOleDbAdapter.Update(updatedRows,"Budget");

}

}


-The Bear

 

[Fred Chateau]

...

Has anyone successfully used MySQL with C#?

Yes... I'm using the MyODBC 3.51 driver.

I'm having problems using the Update command

I believe I remember reading something in the .NET Framework SDK about not
supporting the Insert, Update, or Delete auto-commands with the
OdbcDataAdapter. The documentation advises you to create your own command
structure.

Also, if the .NET OleDb provider works with MySQL, that's news to me. Are
you using MySQL OleDb? I don't think the .NET OleDb provider supports MySQL,
and I know MySQL-AB does not support the MySQL OleDb interface.

Why not use the Microsoft supported .NET 1.1 ODBC provider with the MySQL-AB
supported MyODBC 3.51 driver. At least you'll have more accurate
documentation that way, and some prior beta testing to rely on.

 

[Ron Allen]

The Bear,
You may want to look at the Mono driver implementation(s) for this. See
http://www.go-mono.com/mysql.html for the two available drivers. At least
they have source and are direct which should speed up performance.

Ron Allen

 

[Fred Chateau]

...

You may want to look at the Mono driver implementation(s) for this. See

http://www.go-mono.com/mysql.html for the two available drivers. At least
they have source and are direct which should speed up performance.

When I first set up a MySQL database on my server, I tried several methods
to connect my code. I was pretty excited after locating a .NET driver that
directly connected to the MySQL database, since this seemed to be the most
efficient way to do it.

One evening the driver locked up my server so hard, I wound up driving 30
miles to the co-location site to unplug the machine so I could restart it.
When working with a production server, I don't need that kind of aggravation
.. . .

I switched to the ODBC interface, and haven't had any problems since. Also,
I don't doubt that there's a performance gain when using a single layer or
direct connection, but my application is assembling Web pages on-the-fly
using the data in the MySQL database, and I haven't been able to tell the
difference between the two drivers.