-
August 4th, 2009, 09:28 AM
#1
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.
-
August 4th, 2009, 09:47 AM
#2
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";
-
August 4th, 2009, 10:25 AM
#3
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;
-
August 4th, 2009, 11:43 AM
#4
Re: MySqlCommandBuilder SqlCommandBuilder Error
INVALUABLE DATABASE DEVELOPMENT TIP:
Don't forget to close the db resources when you are done with them.
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.
-
August 4th, 2009, 11:51 AM
#5
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;
-
August 4th, 2009, 12:08 PM
#6
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.
-
August 4th, 2009, 12:24 PM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|