?
Solved

ODBC Connection Failure to MySQL

Posted on 2004-09-06
9
Medium Priority
?
396 Views
Last Modified: 2008-03-06
I have installed mysql-4.1.4-gamma-win on a win2k pro pc and a win2k server both of which are used for development and testing.  SQl Server 2000 is installed on the server and is in use at the moment. I have had a few minor problems which I have worked through ok. I have copied a database with data from MSSQL to MySQL on both pcs.

My problem is that an ASP page run on the server will not connect. The error is "Data source name not found and no default driver specified". I have tried DSN and DSN-less connects. When I tested the DSN definition it connected fine on both pcs. The same ASP page works perfectly from the win2k pro pc, connecting to either the database locally or remotely on the server. In fact the page run on the pc from the server works great. I have had the above error before, usually when I have not setup a DSN definition or have mis-spelled the name. Connecting still works fine to the MSSQL server so I don't think it's something general with ODBC.

I have spent 2 days now "searching the web" for a solution to this problem so any help you can offer me would be greatly appreciated. Thanks Wayne.
0
Comment
Question by:rwayner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

by:casstd
ID: 11993975
Hi,

       As you mentioned the common cause of the problem is in the connection string. You have mis-spelled the connction object in your connection string definition. Look for the connection string here.

dim Dbobj, DbobjMembers
Dbobj =  "Provider=sqloledb;Data Source=(local);Initial Catalog=PMS;User ID=PMSADMIN;Password=PMSADMIN"
Set DbobjMembers = Server.CreateObject("ADODB.CONNECTION")                ' may be you mis-spelled this object name
DbobjMembers.Open Dbobj

http://www.connectionstrings.com/

0
 

Author Comment

by:rwayner
ID: 12000329
Hi,

Thanks for your reply.

Unfortunately the oledb drivers for MySQL are defunct according to the experts including MySQL themselves so I will have to use ODBC.

Just to give a little more info I am using the same connection string from both pcs. Just changing the server name and user/password when connecting remotely.

These are the javascript definitions of the connection strings.
var ConnStr = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=Logging; UID=root; PWD=rootpw; Option=3"
This works on the pc but not the server. It also works if this is run on the pc from the server.

var ConnStr = "Driver={MySQL ODBC 3.51 Driver}; Server=GDI-SERVER; Database=Logging; UID=user; PWD=user; Option=3"
This works from the pc to connect to the database on the server.

var ConnStr = "dsn=Logging;uid=root;pwd=rootpw;"
This works on the pc but not the server. Both dsn definitions test ok for connectivity when being defined.

I am going to try a different pc. This will be an xp pro pc. I possibly may be able to get acces to another win2k server. I will post again if I get any more info.
0
 
LVL 12

Expert Comment

by:GoofyDawg
ID: 12001641
Do you have the latest MDAC from Microsoft? Also, have you tried creating a connection with the Data Sources (ODBC) tool in Windows? That might reveal basic connection problems that you could then easily resolve as opposed to trying to futz with ASP connection strings. Just a thought...

GoofyDawg
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rwayner
ID: 12003073
Thanks for your thoughts.

The pc has version 2.8 and the server has 2.6 which I have just updated to 2.8.  Yes. When defining a data source on pc and server the test connection is successful. It seems to be only when connecting from ASP that the issue arises.
0
 
LVL 12

Expert Comment

by:GoofyDawg
ID: 12003364
Have you then tried to use that DSN instead of the connection string? Now this is important: You have to try the connection from the web server if you can. If you can create a remote desktop connection to the web server, then create the DSN there, you should then be able to use the DSN you create.

GoofyDawg
0
 

Author Comment

by:rwayner
ID: 12003754
Yes I have tried a DSN connection which are defined on the server. A test connection works ok when defining the DSN.

I've just been able to try a connection from visual basic on the server. This works fine.
I used this connection string which will not work from ASP.
 "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=Logging; UID=root; PWD=rootpw; Option=3"
whcih leads me to think that I have got something, maybe security, setup wrong for the IIS user for MySQL.
0
 

Author Comment

by:rwayner
ID: 12041036
I have discovered the problem.

The IUSR-Local_Machine user needs read permission for the registry keys HKLM\SOFTWARE\ODBC\ODBCINST.INI for dsn-less connections and HKLM\SOFTWARE\ODBC\ODBC.INI for dsn connections.

Thanks for your help.
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12743121
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question