By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,695 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,695 IT Pros & Developers. It's quick & easy.

Using C# with MySQL

P: n/a
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=adm in;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.Modi fied);

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

{

myOleDbAdapter.Update(updatedRows,"Budget");

}

}
-The Bear
Nov 15 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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)
--
"The Bear" <bu**********@hotmail.com> wrote in message news:cM******************@news20.bellglobal.com...
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=adm in;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.Modi fied);

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

{

myOleDbAdapter.Update(updatedRows,"Budget");

}

}
-The Bear
Nov 15 '05 #2

P: n/a
"The Bear" wrote ...
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.

--
Regards,

Fred Chateau
e-mail: fchateauAtHotelMotelNowDotCom
Nov 15 '05 #3

P: n/a
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
"The Bear" <bu**********@hotmail.com> wrote in message
news:cM******************@news20.bellglobal.com...
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=adm in;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.Modi fied);

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

{

myOleDbAdapter.Update(updatedRows,"Budget");

}

}
-The Bear

Nov 15 '05 #4

P: n/a
"Ron Allen" wrote ...
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.

--
Regards,

Fred Chateau
e-mail: fchateauAtHotelMotelNowDotCom
Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.