Solved

ASP Classic: Problem with DSN-less Connection to MySQL

Posted on 2007-03-30
4
884 Views
Last Modified: 2008-02-01
I'm trying to connect ASP classic to MySQL, running on localhost. Here's my setup:
  -  ASP is on XP Pro IIS 5.1 at port 1080 (localhost:1080)
  -  The MySQL server is part of WAMP at port 80.
  -  Installed the MySQL ODBC 3.51 Driver
  -  Although I'm trying to use a DNS-less connection, I have set up a System DSN named "MySQL" using the MySQL ODBC 3.51 Driver.  The parameters are: localhost, root, (no password), and the database name "mydata".
  - Tested the User DSN, and it connects OK.
  - Restarted computer, rechecked DSN; connects fine to the database "mydata" to be sure everything works.

    The ASP page runs fine as a "hello world" script (localhost:1080/helloworld.asp).  
   
   However, it gives an error message when I add the database connection string for a DSN-less connection.

----------------------
Update:  I just got it to work with this:

<%
Dim oConn, rs, sql
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "SERVER=localhost;" & _
                 "DATABASE=mydata;" & _
                 "USER=root;" & _
                 "PASSWORD=;"
[etc.]
%>

   and also with:

<%
Dim oConn, rs, sql
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "SERVER=localhost;" & _
                 "DATABASE=mydata;" & _
                 "USER=root;" & _
                 "PASSWORD=;"

   and also with:

<%
Dim cn, rs, sql
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "Server=localhost;" & _
                 "Port=3306;" & _
                 "Option=16384;" & _
                 "Stmt=;" & _
                 "Database=mydata;" & _
                 "Uid=root;" & _
                 "Pwd=;"

I also tried a remote connection, to a MySQL server out on the Internet, and it worked like this when calling the script from localhost:1080 :

oConn.Open "Driver={MySQL ODBC 3.51 Driver};" & _
           "Server=db1.mydatabaseserver.com;" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=mydata;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword"


But it won't work on my ISP server
   What if the ASP script is hosted on a Windows ISP server where I do not have access to installing the MySQL ODBC Driver nor a DSN?  Is there any other way to connect?
     The error I'm getting, when trying to use the script on an ISP server, is:

Microsoft-IIS/5.0
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
0
Comment
Question by:Randall-B
[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
  • 3
4 Comments
 
LVL 17

Accepted Solution

by:
akshah123 earned 250 total points
ID: 18823886
>>>So here's a revised question:  What if the MySQL server is remote, out on the Internet?  Would the following work?

Yes it would as long as you can normally access the same mysql server from the location of your windows server.  Sometimes, mysql servers are kept behind a firewall so that only a limited number of IPs are allowed access.  Therefore, you just need to make sure that your user is allowed a remote access.

>>>What if the ASP script is hosted on a Windows server where I do not have access to installing the MySQL ODBC Driver nor a DSN?
You definately need to install the ODBC drivers for mysql.  However, I don't think you need to create a user dsn.

0
 

Author Comment

by:Randall-B
ID: 18823940
OK, when posted the ASP script to the ISP windows server, I get this error message:

   Microsoft-IIS/5.0
    Microsoft OLE DB Provider for ODBC Drivers
    error '80004005'
    [Microsoft][ODBC Driver Manager] Data source
    name not found and no default driver specified

Does it look like they don't have the MySQL ODBC driver installed?  If that's the case, I guess I'm stuck unless I can get them to install the driver on their server?
    What if I use MS SQL instead?  Do you know if that would also require an ODBC driver to be installed?
0
 

Author Comment

by:Randall-B
ID: 18824064
Or could SQLite work somehow?
0
 

Author Comment

by:Randall-B
ID: 18825400
I have decided to use MS SQL Server, which is available on that server and apparently has the right ODBC connections installed.  So I used the trial version of DBManager Professional to import my MySQL tables into MS SQL, and it seems to be working OK.  Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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