Solved

ASP Classic: Problem with DSN-less Connection to MySQL

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now