How To: Use hosted mySQL DB instead of local SQL express DB?

allan_ravn · April 3rd, 2009, 07:38 PM

I have a webapplication made from scratch with using "Beginning ASP.NET 3.5: xxx" and after finishing my implementation I'd like to "test it" with a mySQL database that is another server.

I had the (simplistic?) idea that it would be enough to substitute my (working) local connection SQL express string:

Working :-)
<addname="local"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

With the one from the remote server:

Not working
<add name="local" connectionString="{MySQL ODBC 3.51 Driver};Server=ServerIP; Port=3306;Database=Allan_testDB;User=Allan_DB;Pass word=myPSW;Option=3;"
providerName="System.Data.SqlClient" />

And then be able to use the webapplication as if "nothing happened" (but of course now using the remote database which doesn't have any content).

First question
Can I assume that just exchanging the local connection string with the remote connection string will make my webapplication "see and use" the remote mySQL database without making any further modifications?

Second question
The above mySQL connection string doesn't work - the page throws the following exception:
Exception Details: System.ArgumentException: Keyword not supported: '{mysql odbc 3.51 driver};server'.

Any good opinions ans advice will be appreciated

. Br. Allan

Imar · April 4th, 2009, 05:41 AM

Hi there,

Unfortunately, this won't work, or won't be as simple as you think. Many of the built-in features of ASP.NET only work on SQL Server. These features include Membership, Roles, Profile and LINQ to SQL. The good news is, there are providers available for the provider based features like Membership, Roles and Profile. I never used them, so I can recommend on how to use or configure them.

Linq to SQL is only available for SQL Server so if you're using that you're stuck to SQL Server. You could look into the ADO.NET Entities Framework instead which does have (external) support for MySQL:

http://www.google.com/search?hl=en&q...ql&btnG=Search
http://virtuoso.openlinksw.com/datas...oNet35Provider
http://pattersonc.com/blog/index.php...et-mvc-part-i/

Again, I haven't used them so I can't advise on how to use them.

So, to answer your first question: Nope; you'll need to do more to make it work...

Cheers,

Imar

allan_ravn · April 8th, 2009, 06:36 PM

Hi again,

Thanks for the links + google searches (which you can also send using www.lmgtfy.com, for example like this: http://www.lmgtfy.com/?q=Books+by+Imar+Spaanjaars).

First I read and installed:
http://www.codeproject.com/KB/databa...pprovider.aspx
(installation went fine, but I haven't tried it out yet as I first want to make sure I can somehow retrieve something from my mySQL database)

Then I realized that I had to install the ODBC MySQL Connector:
http://dev.mysql.com/downloads/connector/odbc/3.51.html

However I did not know how to make VWD "see" the ODBC connector until I found this really great article, about how to setup an ODBC connection to a database:
http://www.depts.ttu.edu/ithelpcentr.../ODBCSetup.php

Now I can actually "see" my database and browse it through the solution explorer in VWD - however I'm not sure if I can just use the GridView control, drag it into an aspx page and bind it to a datatable (called GaveListe in the DB). I tried to do this (using my ODBC connection string) - but when I try to save this in "Save the configuration string to the application configuration file" I get the following error:

Database schema could not be retrived for this connection. Please make sure the connection settings are correct and the database is online. The conncetion has been disabled.
(however I can still press "ok" and get through the GridView guide for configuring the datasource)

But when I try to "run" my application I receive the following exception:

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.41-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[GaveListe]' at line 1
(which I kind of expected as the datasource configuration for the GridView did not complete error free)

Can I do it like I do it? (meaning setup GridView to use my mySQL connection string and still expect to be able to add "UPDATE, DELETE, etc" to the control) - Or do I need to write something more? (note that my DB is currently empty - It's just the table "GaveListe" that I have created without any content).

Br. Allan R

Imar · April 9th, 2009, 02:20 AM

Hi again,

I really don't know, as I never used VWD with MySQL toegether.

You could try asking this in the general ASP.NET Pro forum, or maybe in the MySQL forum; I am sure you're not the first one wanting this, or running into problems... ;-)

Imar

allan_ravn · April 14th, 2009, 03:09 PM

Thanks Imar - Damn you are quick to answer these posts :-)) Thanks a lot.