unable to connect to mysql database using asp

I'm using Windows XP Pro and I'm trying to connect to a MySQL Database using the following asp code:

<%@ Language=VBScript %>
<%
Dim my_conn, strSQL, rs

Set my_conn = createobject("ADODB.Connection")

my_conn.open = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& "DATABASE=test;"_
& "UID=root;"_
& "PWD=;"

strSQL = "SELECT * FROM names"

Set rs = conn.Execute(strSQL)

Do while not rs.eof ' Do until it reaches end of db

Response.Write "User ID# " & rs("ID") & "<br>"
Response.Write "FirstName: " & rs("FirstName") & "<br>"
Response.Write "LastName: " & rs("LastName") & "<br>"

rs.MoveNext 'Next record
loop

my_conn.close ' Close database connection
Set my_conn = nothing 'obj variable released

%>

I'm getting the following error in Explorer:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10022)
/test99.asp, line 10

I'm able to connect to the database using mysql -u root -h localhost (i have not setup any passwords yet) and I'm also able to connect to the database from Borland C++ Builder. But I'm not able to connect using the above code and I can not figure out what is going on. Could someone please look it over and let me know if they see the problem.

Thanks,
Dave

Do a search in this forum. I believe this question has been answered several times.

Believe me I searched this forum and every other forum I could find on the net that discusses mysql and asp. I even setup a dsn connection and tried it that way. It still will not work. When I setup the dsn I clicked the test connection button and it connected with no problem. I'm using mysql 4.0.16-max-net, Windows XP and IIS. Here's my code using the dsn connection:


<%@ Language=VBScript %>
<%
Dim my_conn, strSQL, rs

Set my_conn = createobject("ADODB.Connection")

// I setup the mysql_dsn under the system DSN tab
my_conn.open = "DSN=mysql_dsn;"

strSQL = "SELECT * FROM names"

Set rs = conn.Execute(strSQL)

Do while not rs.eof ' Do until it reaches end of db

Response.Write "User ID# " & rs("ID") & "<br>"
Response.Write "FirstName: " & rs("FirstName") & "<br>"
Response.Write "LastName: " & rs("LastName") & "<br>"

rs.MoveNext 'Next record
loop

my_conn.close ' Close database connection
Set my_conn = nothing 'obj variable released

%>

and I get the following error message:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost' (10022)
/test99.asp, line 19


Any ideas on things I could check would be most appreciated.

Dave

I believe you haven't install MySQL ODBC 3.51 Driver, that's why you can't connect to MySQL Database from ASP. To check whether you have install the driver, you can goto:

Control Panel>Administration Tools>Data Sources(ODBC)>User DSN

If the driver had been installed, it should appear on the driver list....else you can download the driver from URL and install it. It should solve your problem....

You can also try creating DSN for that connection.If that work you shd be able to connect directly using ASP.

Check the account priveleges on the SQL server. You may need to specify that you have the right credentials to login.

Hi,

I ran your code OK, but only after correcting the conn.execute to my_conn.execute.