View Full Version : ASP connect to remote MySQL

08-23-2004, 03:52 AM
I have been searching for 6 hours for an answer to this question.. If someone could please give me a hand here I would greatly appreciate it.

I recently migrated from a Unix server to a Windows server to start learning ASP.. (I currently program in PHP) Everything is going fine except I can't for the life of me connect to a MySQL server that is hosted on my domain.

I CAN NOT use a DSN to connect. My host does not allow it for security reasons. So I am only able to look for DSN-less connect strings. I also can not use an IP address because there is no IP that points directly to my domain.

I am doing all of my testing on my remote server, so I shouldn't be looking for any drivers to install on my computer or anything (as far as I understand it).

I would post a code snippit of what I have been using to connect, but I have tried probably 40 variations and had different problems with all of them.. Would anyone out there please post the code that you use to connect, as well as what you use to pull down a recordset and display the information so that I can get something working here.

My database name is "transportation" and my table name is "domestic" if that makes any difference.. Thank you greatly in advance for any and all information you can give me to help out!



08-23-2004, 04:17 AM
Still working on it =) A little update, I did some more looking on this site and have this code in right now..

Dim objRS,objConn

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={MySQL ODBC 3.51 Driver};SERVER=server;PORT=3306;DATABASE=database;USER=user;PASSWORD=pass;OPTION=3;"

set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "Select * from domestic", objConn

do until objRS.EOF
for each x in objRS.Fields
Response.Write(" = ")
Response.Write(x.value & "<br />")
Response.Write("<br />")


It comes up with this error:

ADODB.Recordset error '800a0e7d'

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/test.asp, line 11

Did I connect to the database and just have my recordset in wrong? This may not help any but it's what I've got =)

Thanks again!

08-23-2004, 10:50 PM
Try this link (http://www.devarticles.com/c/a/ASP/Using-MyODBC-To-Access-Your-MySQL-Database-Via-ASP/3/) which I found in the Connector/ODBC with Microsoft VBA and ASP (http://dev.mysql.com/doc/connector/odbc/en/faq_5.html#MSVBA) section of the MySQL Connector/ODBC FAQ (http://dev.mysql.com/doc/connector/odbc/en/faq_toc.html).

Also, The Connector/ODBC Programmer's Reference Manual (http://dev.mysql.com/doc/connector/odbc/en/manual.html) has a good example of using ADO as follows (scroll down to C.1 in the above link to see the original example):

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=3"


Additionally, running your exact error message thru Google (http://www.google.com/search?hl=en&ie=UTF-8&q=ADODB.Recordset+error+%27800a0e7d%27+) gave me a ton of links so they may give you more leads on a fix.

Good luck! (Yes, I registered to give you this answer :thumbsup: )

EDIT: I forgot to mention that you don't mention that the ODBC Connector 3.51 is actually installed on the server with MySQL on it. I assume it is, but you never know. ;)

08-24-2004, 06:59 AM
In short, you were not opening the connection. You just created it but not opened it.

Dim objRS,objConn

Set objConn=Server.CreateObject("ADODB.Connection")
objConn.ConnectionString="DRIVER={MySQL ODBC 3.51 Driver};SERVER=server;PORT=3306;DATABASE=database; USER=user;PASSWORD=pass;OPTION=3;"

set objRS = Server.CreateObject("ADODB.Recordset")

08-24-2004, 09:18 AM
Wow man, I was reading up on that option command. I didn't know what it was, and I don't think I have ever used it.
Option 3 = "The client can't handle that Connector/ODBC returns the real width of a column." - & - "The client can't handle that MySQL returns the true value of affected rows. If this flag is set then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work."

In my code when I connect to my local MySQL server, I just have:
"Driver=MySQL ODBC 3.51 Driver;Server=localhost;UID=Name;PWD=pass;Database=MyDataBase"
The difference is, of course, you're server isn't on the same host.
If your MySQL host and Web host were the same, you could use localhost. I guess it wouldn't matter what my options were because the connection and the connector are using the same driver. Am I right?

Well anyways, try the ip your domain is hosted on, or even the nameserver, that might work. I don't know exactly, I have never had this problem.

Good luck! :)

08-24-2004, 04:49 PM
what about the connection string, what should that look like if the database was in access? I also saw examples using the UserManager.Server object just to get access to the remote database, do you guys have any knowledge of this object? curiously awaiting a response. :D

08-24-2004, 11:20 PM
Set oConn = Server.CreateObject("ADODB.Connection")
StrConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db.mdb") & ";"
oConn.open StrConn