MySqlCommandBuilder SqlCommandBuilder Error

[raulbolanos]

Hi Pr0's,

I have a problem trying to implement the same example from this link:

http://msdn.microsoft.com/en-us/libr...ndbuilder.aspx

But instead of Sql I use MySql. When I fill the DataSet with the data from the DB I dont have any trouble I can even fill also a DataGridViewer and display the data in my form.

The problem is when I want to use the GetUpdateCommand. I get an exception error like "Dynamic SQL generation is not supported against multiple base tables".

So basically I get the Data from the DB and then fill the DataSet and then copy it to the DataGridView. Afterwards I modify manually the rows and then I want to update it.

Why do you think I have this problem? I just copied the code exactly from the MSDN's example and I get this error.

Thank you in advance,
Raul Bolanos.

[raulbolanos]

My Query is something like this

string queryString = "SELECT xValue, yValue, specType, s.specIndex FROM (verspec s INNER JOIN verspecdata sd on s.specIndex = sd.specIndex) WHERE (productNumber = 'TEST') AND options = '000 000 000' AND specType = 0 AND curveIndex = 0 ORDER BY productNumber";

[monalin]

Reference the MySQL.Data.dll from the following URL in your project.

http://dev.mysql.com/downloads/connector/net/6.1.html

Then try the following code. I don't have a MySQL server installed on my computer right now so i can't try it out. But i pieced a few things together from one of my projects and hopefully this works for you.

            MySqlConnection conn = new MySqlConnection(connectionString);
            conn.ChangeDatabase(databaseName);

            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter();
            da.SelectCommand = new MySqlCommand("SELECT * FROM Users", conn);
            da.Fill(ds);

            return ds;

[Arjay]

INVALUABLE DATABASE DEVELOPMENT TIP:

Don't forget to close the db resources when you are done with them.

conn.Close( );

Not doing this can cause all sorts of issues with leaking resources. Sure the gc will eventually reclaim these resources, but this may not be soon enough depending on the MySql connection limits and how frequently you are making connections.

[monalin]

Yeah thanks, I forgot to add that. I pulled all this code out of one of my classes that I use to manage my database connections, I must have missed that. Also forgot to open it. That's why I always try my code before I post it here hah.

            MySqlConnection conn = new MySqlConnection(connectionString);
            DataSet ds = new DataSet();

            try
            {
                conn.Open();
                conn.ChangeDatabase(databaseName);

                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = new MySqlCommand("SELECT * FROM Users", conn);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                // Log error 
            }
            finally
            {
                conn.Close();
            }

            return ds;

[Arjay]

Another option is to use the 'using' block to automatically free the resources.

 
using( MySqlConnection conn = new MySqlConnection(connectionString) )
{
  DataSet ds = new DataSet();

  conn.Open();
  
  MySqlDataAdapter da = new MySqlDataAdapter();
  da.SelectCommand = new MySqlCommand("SELECT * FROM Users", conn);
  
  da.Fill(ds);
  
  return ds;
}

In order to use a 'using' block, the class you are using in the block needs to implement IDisposable which forces implementation of a Dispose method. Typically for a database connection object, Dispose just calls Close. The nice thing here is that object declare in a using statement get their Dispose methods called automatically when leaving the using block scope.

Note: you'll have to check with regard to returning data set objects. Sometimes the connection needs to remain open while using the dataset.

[monalin]

Just tried it, the dataset appears to still works fine with the 'using' method.