Hi Guys.
I have had a number of issues trying to get mysql to work with Visual Studio .NET and Crystal Reports. FYI it is based on the fact that all three different companies (and in particular Microsoft) do things differently. Microsoft doesn't want to help people who have their VS.NET software if they are not going to buy MS-SQL server, and have no intention of helping you anyway unless you pay their overbloated prices just to give THEM some information.
The SERVER EXPLORER was written with only Microsoft and Oracle based databases in mind, but that doesn't mean that you cant do a run-around.
Once you deal with making the connection available in server explorer you will be faced with another issue which I shall explain later.
However, to make the connection in server explorer you need to do the following:
* first of all you need to install the MyODBC connector 3.51 (or latest) on the development machine (NB. you can find this at
http://www.mysql.com/products/connector/odbc/ )
* Create a datasource in Control Panel/Administrative Tools with a connection to your database. This data source is going to be used purely for Server Manager and you dont need to worry about creating the same data source on your clients PC when you have made your VS.NET application (Unless you want to) - I dont want to cover this in this answer, too long. For the purpose of this explanation I will pretend that you created a MyODBC data source called 'AADSN' to database 'noddy' on mysqlserver 'SERVER01' and have a root password of 'fred'. The server can be either the Computer Name (found in Control Panel/System/Computer Name), or alternatively it can be the IP Address.
NB. Make sure that you test this connection before continuing with this explanation.
* open your VS.NET project
* go to server explorer
* right-click on 'Data Connections'
* select 'Add Connection'
* In DataLink Properties, go to the provider tab and select "Microsoft OLE DB Provider For ODBC drivers"
* Click Next
* If you previously created an ODBC data source then you could just select that. The disadvantage of this is that when you install your project application on the client machine, the same data source needs to be there. I prefer to use a connection string. This should look something like:
DSN=AADSN;DESC=MySQL ODBC 3.51 Driver DSN;DATABASE=noddy;SERVER=SERVER01;UID=root;PASSWORD=fred;PORT=3306;SOCKET=;OPTION=11;STMT=;
If you omit the password from the connection string then you must make sure that the datasource you created (AADSN) contains a password.
I am not going to describe what these mean, you can look in the documentation for myodbc for that, just ensure that you get a "Connection Succeeded" message when you test the datasource.
*OK, so the database should appear in the server manager, now for the next problem.
*open the item/data connection. Your tables should appear in a list.
*Try to drag and drop a table onto the dataset.
*Eh, error:
The wizard detected the following problems when configuring the data adapter:
Details: Error in select clause: Expression Near ""
Error in From clause: Expression Near ""
Unable to parse text
I had this issue for some time and had to use a Microsoft Service Call in the process! So, since they charge us for information because they don't supply the answers in their 'Knowledge Base', I am going to relieve you of this problem and hopefully it will give me some good karma in the future.
The reason that you cannot dragdrop your item onto the dataset is because when the table description is passed to the dataset from server explorer, server explorer uses double quotes instead of mysql's single quotes. The way around this is to restart the mysql server using the --ansi-quotes parameter, so the mysqld statement should look something like this:
mysqld -uroot -ppassword --ansi-quotes --console
Obviously this is just an example to show you how to insert the parameter into the mysqld statement. Your mysqld command may look different. Another issue is that if you have sql statements in your code they may only accept the double quotes, not the single quotes, so expect more of the & chr(34) & episode's typical of Visual basic.
Anyway, once you restart the mysql server and try and drag drop the item from server explorer onto the dataset you still get the same message but the item does appear on the dataset. Ignore this error every time you have to do this - the problem is errored to screen even though the item does appear in the dataset.
So, theres your answer. There are a few other items that should be brought to your attention:
* it is advisable to use the mysql.connector.net v1 class if you wish to write a database application in visual studio. There is currently no GUI interface for it, but its the only one I have felt happy with so far, and its free from the mysql website:
http://dev.mysql.com/downloads/connector/net/1.0.html
* If you are planning on writing a database that needs to retain data in different character sets (French, Greek, Italian, Hungarian, Danish, Chinese, Japanese), beware! I have had to store these as BLOB fields because the Unicode characters in Windows do not match the MySQL unicode characters and so cannot be reproduced correctly if you try and store unicode characters using a Visual Studio.NET application. This issue is very messy. The disadvantage of storing these items as a blob is that if you wish to run a where clause or order by on a blob field you will get incorrect results. If this issue is resolved then I would consider using MySQL, but for now there is little being done to integrate MySQL with Visual Studio in the case of Unicode and standards.
Sorry for the bigggg explanation
Hope this helps
Nick.