In order to connect to MySQL database you need to use the MySQL ODBC 5.1 Driver. The driver can be downloaded from http://dev.mysql.com/downloads/connector/odbc/5.1.html.
Please note:
- MySQL ODBC Driver 5.1.4 has problems writing to columns of type TIMESTAMP. See ODBC TIMESTAMP string format not handled properly by ODBC driver
- MySQL ODBC Driver 5.1.5 fixes the TIMESTAMP problem but causes problems in Visual
Studio sql designer. The list of database tables is empty. This problem has been
resolved in MySQL ODBC Driver 5.1.6. See
Session get table names raises error
Steps to create connection for MySQL in Visual Studio:
- Open the Configuration dialog of SmartDataSource
- Select New Connection... button.
- Change the Data Source to be ODBC Data Source
- Select the "Use connection string" radio button and select the Build button.
- Select the New button in order to build the data source
- For driver select MySQL ODBC 5.1 Driver
- Fill in the information, select your database, server, user, password
It is important to explicitly select your database from the Database dropdown field.
- Select Details button and make sure to check "Return matched rows instead of affected rows" flag. This is required in order the MySQL ODBC driver to return properly the number of affected rows when updating the row with the same data. See Knowledge article PRB: 'No data was affected' error when updating with MySQL ODBC driver
- Confirm the dialogs.
- You can also save the connection in Web.config by selecting the Save in Web.Config button.
