Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Unanswered: can I use asp and ODBC driver to connect to MySql database

    I'm totally new to programming and could really do with some help, I just migrated my access db to mysql and I'm trying to figure out how change my connections and code for mysql, I was told that I would have to use php, but I also read that it can be done with asp using an ODBC driver which I have installed on my server. If I use the ODBC driver would I be able use the same SQL that I used to connect to the access DB? what other issues would there be?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    although I have never used MySQL in ASP I can see no reason why you can't use it.

    I'm not sure that you will neccesarily be able to use the same SQL as you used in JET - there are differences bewteen all implementations of SQL, JET is probably more quirky than most.

    the main area you are liable to hit problems is in string matching, wildcards and string delimiters.

    just switching to MySQL (or any server is going to give a significant performance enhancement, to get the best you need to optimise your SQL to meet the servers requirements. With the introduction of V5 MySQL (and its support for stroed procedures, functions and triggers) there is little to distinguish it from db's like SQL server). If you have Version 5 of mYSQL then you should consider shifting mopst if not all of your SQL into the db as stored procs/functions.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2005
    Posts
    31
    Thanks for the reply healdem, I had a few problems with it but I eventually got it, only needed to make minor changes to the code. I'm really jumping in at the deep end here because I know very little about asp, sql, and databases, so I'm learning as I go.
    I tried around 7 different odbc connections, this is the one that worked (just in case someone else is trying to do it)

    Code:
    <%  @LANGUAGE = VBScript %>
    <% Option Explicit %>
    <% 
    Dim sConnection, objConn , objRS 
    
    'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;"); 
    
    
        sConnection = "Driver={MySQL ODBC 3.51 Driver};" & _ 
            "Server=mysql.hosting365.ie;" & _ 
            "Database=DB NAME;" & _ 
            "Uid=USER ID;" & _ 
            "Pwd=USER PASSWORD;" 
    
    
    Set objConn = Server.CreateObject("ADODB.Connection") 
    
    objConn.Open(sConnection) 
    
    Set objRS = objConn.Execute("SELECT ProductName, Grade FROM comics")
    
    
    While Not objRS.EOF
    Response.Write objRS.Fields("ProductName") & ", " & objRS.Fields("Grade") & "<br>"
    'Response.Write & " "
    objRS.MoveNext
    Wend 
    
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
    I really got stuck on the ODBC connection, I kept on putting the address of my server in
    Code:
    'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.hosting365.ie;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;");
    but apparently thats how you do it if your accessing a database remotely,
    since my database was on the same server as my site SERVER=localhost; is the correct one.

    I would like someone else to confirm this, I'm very new mysql and odbc so I could be wrong

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Just as a heads up, using asp and mysql can be kind of quirky. This link does a decent job explaining and solving a huge problem I ran into a while ago using COUNT(). You also might want to check out the mysql docs on the subject.

  5. #5
    Join Date
    Apr 2006
    Posts
    1
    well, but, TO DO THIS, must have a drive odbc in pc.
    anyone has it?


    Quote Originally Posted by MAZER
    Thanks for the reply healdem, I had a few problems with it but I eventually got it, only needed to make minor changes to the code. I'm really jumping in at the deep end here because I know very little about asp, sql, and databases, so I'm learning as I go.
    I tried around 7 different odbc connections, this is the one that worked (just in case someone else is trying to do it)

    Code:
    <%  @LANGUAGE = VBScript %>
    <% Option Explicit %>
    <% 
    Dim sConnection, objConn , objRS 
    
    'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;"); 
    
    
        sConnection = "Driver={MySQL ODBC 3.51 Driver};" & _ 
            "Server=mysql.hosting365.ie;" & _ 
            "Database=DB NAME;" & _ 
            "Uid=USER ID;" & _ 
            "Pwd=USER PASSWORD;" 
    
    
    Set objConn = Server.CreateObject("ADODB.Connection") 
    
    objConn.Open(sConnection) 
    
    Set objRS = objConn.Execute("SELECT ProductName, Grade FROM comics")
    
    
    While Not objRS.EOF
    Response.Write objRS.Fields("ProductName") & ", " & objRS.Fields("Grade") & "<br>"
    'Response.Write & " "
    objRS.MoveNext
    Wend 
    
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
    I really got stuck on the ODBC connection, I kept on putting the address of my server in
    Code:
    'sConnection = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=mysql.hosting365.ie;PORT=3306;DATABASE=DB NAME; USER=USER ID;PASSWORD=USER PASSWORD;OPTION=3;");
    but apparently thats how you do it if your accessing a database remotely,
    since my database was on the same server as my site SERVER=localhost; is the correct one.

    I would like someone else to confirm this, I'm very new mysql and odbc so I could be wrong

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242

  7. #7
    Join Date
    May 2006
    Location
    philippines
    Posts
    1
    hi there.. im lia we have connected to our MySQL server, however we encounter a problem while opening our MySQL database, according to the msg. box appeared, the connection to MySQL server failed.. then we tried to connect it again, and we successfully did.. however when i tried to open the database again its the same msg. box i encountered.. can you please help me out with this?.. thanks in advance..

Posting Permissions

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