Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New to the CF scene
    Join Date
    Aug 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP connect to remote MySQL

    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!

    Cheers,

    Edwa5823

  • #2
    New to the CF scene
    Join Date
    Aug 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Still working on it =) A little update, I did some more looking on this site and have this code in right now..

    Code:
    <%
    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(x.name)
           Response.Write(" = ")
           Response.Write(x.value & "<br />") 
        next
        Response.Write("<br />")
        objRS.MoveNext
    loop
    
    objRS.close
    objConn.close
    %>

    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!

  • #3
    New Coder
    Join Date
    Aug 2004
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this link which I found in the Connector/ODBC with Microsoft VBA and ASP section of the MySQL Connector/ODBC FAQ.

    Also, The Connector/ODBC Programmer's Reference Manual has a good example of using ADO as follows (scroll down to C.1 in the above link to see the original example):

    Code:
    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"
    
    conn.Open
    Additionally, running your exact error message thru Google 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 )

    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.
    Last edited by BuddhaMan; 08-23-2004 at 10:01 PM.

  • #4
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,048
    Thanks
    0
    Thanked 251 Times in 247 Posts
    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;"
    objConn.Open

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

  • #5
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    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!

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Northern NJ
    Posts
    404
    Thanks
    0
    Thanked 1 Time in 1 Post
    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.
    I would rather be a lion for a day than a lamb that lives forever.

  • #7
    Senior Coder Morgoth's Avatar
    Join Date
    Jun 2002
    Location
    Ontario, Canada Remaining Brain Cells: 6
    Posts
    1,402
    Thanks
    2
    Thanked 1 Time in 1 Post
    Code:
    Set oConn = Server.CreateObject("ADODB.Connection") 
    StrConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("db.mdb") & ";" 
    oConn.open StrConn


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •