Link to home
Start Free TrialLog in
Avatar of Randall-B
Randall-B

asked on

ASP Classic: Problem with DSN-less Connection to MySQL

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
ASKER CERTIFIED SOLUTION
Avatar of akshah123
akshah123
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Randall-B
Randall-B

ASKER

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?
Or could SQLite work somehow?
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.