Hello,
For my project I need to use a MySQL database server. I downloaded the MySQL .NET connector to be able to use ADO.NET datasets in my datalayer. I'm able to connect the the database and the datasources window shows my database with all tables, views, etc. as it should.
Now when I add a dataset to my project and start dragging and dropping tables onto the design surface, the designer returns an error when the dragged table is linked to a table already on the form (foreign key). It seems as if generating the relation between the tables is failing somehow. Is this caused by the MySQL Connector or the VS designer? All VS tells me is that an error occured with code -1 without any additional information; not too helpfull unfortunately.
I posted the same question on the MySQL forums because I'm not sure which side of the connector is causing this problem. I didn't find any valid solutionsother than checking if all tables have valid primary keys whichis indeed the case. I'm using VS2008 and the latest available MySQL .NET connector 5.2.1. |
| J Siegmund [MCTS] Saturday, March 15, 2008 3:46 PM |
(This is not fixed in 5.2.2 i think. Can't see the changes anywhere in svn (5.2 branch)
I got the above from the referenced bug but it is not a bug because INNODB does not have the last two options in version 5.0, so MySQL have closed the report because it is just a limitation of their product.
|
| Caddre Monday, March 17, 2008 7:04 PM |
If you are using the free MySQL it is MySQL because by default MySQL does not have DRI(declarative referential integrity) you need the now Oracle owned INNODB storage engine to use DRI in MySQL. So you have to find out what is needed with the connector to enable DRI in ADO.NET. Another thing you can have foreign key without the system enabled DRI. So check MySQL forums for how to use INNODB, one thing I know it is not free even before Oracle bought INNODB and Sun paid 1 billion for MySQL.
|
| Caddre Sunday, March 16, 2008 2:17 AM |
Whilest this post is begin back and forth between forums  , I checked the engine. All tables are indeed INNODB, so I guess that shouldn't be the problem. And another thing what I don't get is why the database engine should break the VS dataset designer? If the database has a kind of relations the dataset designer can't handle, it should just ignore them in my humble opinion. I don't see how the storage engine is related with the relations in a dataset, since those aren't linked to the database in any way (they're just generated when the database structure is begin loaded, right?) |
| J Siegmund [MCTS] Sunday, March 16, 2008 3:41 PM |
DRI is different you are using what we call if a references b b must exist to pass values from one table to another and also use that value to delete, update, set default and set nullthe value in b so if you don't have it in the database and you create it in ADO.NET you can create data integrity issues. And you can use it in any RDBMS because on this end columns and rows definitions are stardardized. MySQL uses many storage engines, so make sure DRI is enabled in your MySQL tables not just foreign key because you can have many foreign keys but only one qualify for DRI.
|
| Caddre Sunday, March 16, 2008 4:02 PM |
I kept on trying and finally decided to check all primary keys, foreign keys and the tables database engines as well as the collations.
Making sure everything was properly in sync, I finally succeeded in adding most (still not all!)of the tables to the dataset designer. The relations aren't added at all (have to add those manually) and there seems to be a problem with the generated SQL commands. I only get Select and Insert queries, the Delete and Update remain empty. Any ideas?
I'm using INNODB for all tables now; which collation should be used? |
| J Siegmund [MCTS] Sunday, March 16, 2008 8:16 PM |
I still thinks you have not enabled DRI in MySQL you may need MySQL SQL development book it is called Cascade Delete and Cascade Update.And why are you asking about collation both are not related collation relate to languages and code pages about rendering text columns and sorting. In the meantime here is MySQL docs for the command objects.
http://dev.mysql.com/doc/refman/4.1/en/connector-net-examples-mysqlcommandbuilder.html
|
| Caddre Sunday, March 16, 2008 9:00 PM |
Allright, you're absolutely right I have no clue about DRI and have little MySQL knowledge in general. I'm very used to SQL Server andwould much rather use that in combination with Linq-to-SQL, but unfortunately my hosting provider only supports MySQL databases.
Thanks for the info and I'll only return here after reading some docs  |
| J Siegmund [MCTS] Sunday, March 16, 2008 9:31 PM |
MySQL is now in version 6.0 so there are two versions I have not used in SQL Server DRI enabling is very different in version 2000 and 2005 because 2005 comes with the last two options set default and set null in the table properties while 2000 comes at the top of Enterprise Manager in the enable relationship property. So you see you have to find how to enable it in your version of MySQL.
|
| Caddre Sunday, March 16, 2008 9:41 PM |
I never had these problems with SQL server and I've also never had to deal with DRI in order to get it working. So I guess those settings are okay by default then. |
| J Siegmund [MCTS] Monday, March 17, 2008 7:43 AM |
It is working in SQL Server because you are running SQL Server 2005 which was released with those classes but I have seen 2000 users with probelms because 2000 does not have the last two options. So you need to know how many are implemented in INNODB but I think it is the first two.
|
| Caddre Monday, March 17, 2008 5:17 PM |
It appears to be a known and fixed bug in the MySQL .NET connector: http://svn.mysql.com/fisheye/changelog/connector-net/?cs=1203
Too bad there isn't a build with this fix in it yet, so I'll have to wait for it I suppose. |
| J Siegmund [MCTS] Monday, March 17, 2008 6:00 PM |
(This is not fixed in 5.2.2 i think. Can't see the changes anywhere in svn (5.2 branch)
I got the above from the referenced bug but it is not a bug because INNODB does not have the last two options in version 5.0, so MySQL have closed the report because it is just a limitation of their product.
|
| Caddre Monday, March 17, 2008 7:04 PM |
I don't follow... When I use the specified connector, I can add the tables (without relations, but that's not a bug) but update and delete statements aren't generated. That's exactly what the bug describes, and as found in the changeset notes (http://svn.mysql.com/fisheye/browse/connector-net/branches/5.1/CHANGES) this bug was fixed by rburnett and had something to do with faulty code on the connector side. The changes aren't in the 5.2 branch but in the 5.1 branch. So I guess I need to wait untill those changes are merged with the 5.2 development branch somewhere along the development path. My best bet would be 5.2.2, but time will tell |
| J Siegmund [MCTS] Monday, March 17, 2008 7:30 PM |
Hi. I'm brazilian. i'd the same problem here. i'd posted in my blog. Please, take a look at my solution.
http://primeiroprograma.blogspot.com/2008/06/resolvendo-erro-indefinido-no-mysql.html
Bye
|
| Eder Almeida Costa Friday, June 06, 2008 3:57 PM |
Hi Eder,
Here is the new MySQL connector and BTW I could not read you solution because it is in Portuguese could you please post English version. Obregado.
http://dev.mysql.com/doc/refman/5.1/en/connector-net-examples-mysqldataadapter.html
Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA |
| Caddre Thursday, December 18, 2008 3:46 AM |
index > Windows Forms Data Controls and Databinding > MySQL .NET Connector and DataSet designer issue