Solved

ASP Classic: Problem with DSN-less Connection to MySQL

Posted on 2007-03-30
4
875 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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