MySqlCommandBuilder SqlCommandBuilder Error
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 7 of 7

Thread: MySqlCommandBuilder SqlCommandBuilder Error

  1. #1
    Join Date
    Mar 2009
    Location
    Stuttgart, Germany
    Posts
    56

    MySqlCommandBuilder SqlCommandBuilder Error

    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.

  2. #2
    Join Date
    Mar 2009
    Location
    Stuttgart, Germany
    Posts
    56

    Re: MySqlCommandBuilder SqlCommandBuilder Error

    My Query is something like this
    Code:
    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";

  3. #3
    Join Date
    Jul 2006
    Posts
    297

    Re: MySqlCommandBuilder SqlCommandBuilder Error

    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.

    Code:
                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;

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,199

    Re: MySqlCommandBuilder SqlCommandBuilder Error

    INVALUABLE DATABASE DEVELOPMENT TIP:

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

    Code:
    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.

  5. #5
    Join Date
    Jul 2006
    Posts
    297

    Re: MySqlCommandBuilder SqlCommandBuilder Error

    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.

    Code:
                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;

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,199

    Re: MySqlCommandBuilder SqlCommandBuilder Error

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

    Code:
     
    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.

  7. #7
    Join Date
    Jul 2006
    Posts
    297

    Re: MySqlCommandBuilder SqlCommandBuilder Error

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center