Does MySQL with ASP pages work on Testing server?

Please could someone tell me if you can use MySQL with ASP pages and view the results on the testing server on my computer.

I get an error message

Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I am using Dreamweaver MX2004 and for the local connection I can see the MySQL tables and fields, but for the testing server connection I get the error

Using PHP pages I can use the testing server, so why not with ASP pages.

Advice would be appreciated

I have used Dreamweaver MX 2004, MySQL and ASP to build my companies Intranet. I am running it off a Windows 2000 machine with IIS5 with no difficulties.

You must have the MySQL MyODBC drivers installed on the web server. You can get these from mysql.org website.

Although I usually use DSN-less connections in my ASP work I decided on a DSN for this job. (I can't remember why). So i went to Start->Settings->Control Panel->Administrative Tools->Data sources
In here I created a new System DSN using the MyODBC driver and the address etc of the machine running MySQL.
I set this DSN up on both the testing server and my development machine (WinXP). Obviously the DSN on my machine pointed to the MySQL installation on the testing machine.

If you are using DSN-less connections you may need to check that your connection string in the folder Connections is pointing to the correct machine with MySQL on it (and not localhost) as you may need a different connection string on your development machine than your testing and production machines.

I have found it easy to work with ASP and MySQL with only some slight changes to SQL. e.g. DELETE * FROM becomes DELETE FROM

I am hoping to begin working with ASP.NET and MySQL in the next few weeks.

Post any further questions here and I will answer if I can.

Thank you for advising that the testing server should work.

I have installed MySQL and MyOBDC. I have created a system DSN which tests OK.

In Dreamweaver I can use the DSN connection for the "On this machine" option and this tests OK and connects. I can design pages and access the MySQL database without any problems.

If I use the DSN "On Testing Sever" option, I get the error message. In setting up the DSN connection for the testing server option I can see a list of DSN connections and select the one I need. Why I can see the DSN connection in the setup but receive an error that the DSN can not be found when you test the connection I am not sure.

On my computer I have not set a password for the MySQL database, would this cause a problem?

I think you should run through a checklist of possibilities to rule out any difficulties. I have Dreamweaver set up as: Using Local DSN on my development machine. I then installed MySQL on my testing server and installed MyODBC driver on BOTH machines. I then set up a system DSN on BOTH machines using the driver and named both DSN's the same so that my connection string would work on both the development machine and the testing machine.
This is my connection string:
Dim MM_connMyIntranet_STRING
MM_connMyIntranet_STRING = "dsn=MyIntranet;uid=jonny;pwd=mypwd;"

If you have not set up a password you could use "dsn=MyIntranet;uid=root;pwd=;"
Although for security you should add a root password and possibly some new users for each database. I use MySQL-front as the GUI.

so I would check the following:
1. Is MyODBC driver installed on testing server?
2. Is DSN name the same on both machines?
3. Is there definitely a System DSN on the testing machine using the MyODBC driver and named the same as the DSN in your connection string?
4. Is username and password in Connection string applicable to the MySQL server on the testing machine?
5. Have you tried re-installing the MyODBC driver on the testing server?

It sounds as though the problem is one about the MyODBC driver on the testign machine, or the setup of the DSN on that machine. However if all else fails try reinstalling the driver as I had to do this once when things stopped working.

This may be a stupid question, but it may be the cause of the problem

You ask

Is MyODBC driver installed on testing server?

How do I install the MyODBC driver on the testing server? I have the driver installed on the computer and can use it to create a system or user DSN. Is installing it on the testing server a different installation to installing on the computer.

I am using only one computer, although I can change that and work on a network with a second computer

OK, sorry - I am using two machines - a development machine and a testing machine.
If you have one machine it shouldn't be a problem. I assume you are using IIS and that you then installed MySQL onto the same machine and then MyODBC driver. Then added the system DSN.

Then in Dreamweaver added a new connection and specified the name of the DSN, your username and password.

Then launching the pages in IIS e.g. http://localhost/

If you have done all this and it is still causing problems try re-installing the MyODBC driver.

Where is your error message appearing? Is it in your web browser or in Dreamweaver?

In Dreamweaver under the Application panel and databases tab can you branch out the tables in your MySQL database?
In Deramweaver under Files and Local View you have some asp files that use the db connection?
Then open a browser and point to these files e.g. http://localhost

Does any of this help or am I talking gibberish?

No you are not talking gibberish.

I can create a connection on this machine in Dreamweaver using the DSN connection. I can see the tables and create pages and fields.

Using a coonection on testing server in Dreamweaver lets me see the list of DSNs available and I can select the right DSN, but on trying test, I get the error DSN not found. Why I can see the list but not find it when you try to connect I am not sure.

If I try to use the pages in IIS and launch from there the site works for HTM pages but not for ASP pages, same problem can not find DSN

If I go to a MS Access connection to an Access database, testing on the testing server works. It is only with MySQL that I have the problem ( I was originally using Access and then converted the site to MySQL and these pages work on my ISP host)

Have you tried reinstalling the MyODBC driver? it only takes a few seconds and it may be the problem, especially if it works OK for Access but not for MySQL!

I have un-installed and re-installed. Also have re-booted the computer after this. Still the same problem. I have stopped and re-started both MySQL and IIS

I'm running out of ideas here :-)
Have you tried re-creating the DSN or maybe creating a new one with a different name and amending the connection string in Dreamweaver to the new DSN ?

It definitely does work though - MySQL on IIS using ASP! Our intranet is running like this.

Thanks for your effort and trying to help. At least I know that the testing server should work with MySQL and MyODBC. In that you can see the DSN in the setup of a DSN connection on the testing server, but get an error that the DSN is not found when you test, it is either something very simple or I need to un-install and re-install my IIS and see if that solves the problem

I have solved the problem.

I went in with regedit to the ODBC connections and changed the permissions to allow full control and hey presto, the DSN connections worked on the testing server

Thanks for your time and effort to help which have been much appreciated