MySQL and C#

[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");

}

}


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.

 

[Alvin Bruney]

Try writing a straight query from the command object. I know this works
public static void Execute(string sql)

{

using(OleDbConnection cn = new OleDbConnection(_connString))

{

cn.Open();

//Set sql as your insert,update or delete statement.

using(OleDbCommand cmd = new OleDbCommand(sql,cn))

{

cmd.ExecuteNonQuery();

}

}

}


--
Regards,
Alvin Bruney
Got tidbits? Get it here...
http://tinyurl.com/3he3b
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");

}

}


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.