469,917 Members | 1,764 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Using C# with MySQL

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
4 7271
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
"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
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
"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.

Similar topics

5 posts views Thread by Phil Powell | last post: by
2 posts views Thread by cocoalearner | last post: by
reply views Thread by Tawfik Rady | last post: by
12 posts views Thread by MJK | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.