Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Connecting to mySQL database

    I can't get my database connection to work!
    According to my host; the mySQL database is local to the machine running the webhost.
    Here's what I've tried so far; just a simple connection test.
    Code:
    <%@ Page Language="VB" aspcompat="true" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <script runat="server">
    
    </script>
    
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Connection Test</title>
    </head>
    <body>
        
        <%
            Dim conn, rs As Object
            
            conn = Server.CreateObject("ADODB.Connection")
            
            conn.Open("Driver={mySQL ODBC 3.51 Driver}; Server=localhost; Port=3306; Database=websitedb; Uid=website; Pwd=xxxx;")
           
            rs = Server.CreateObject("ADODB.Recordset")
            rs.Open("SELECT 1", conn)
            
            Do Until rs.EOF
                Response.Write(rs.Fields(0).Value)
            Loop
            
            rs.Close()
            conn.Close()
            
            rs = Nothing
            conn = Nothing
        %>
        
    </body>
    </html>
    Which throws this error
    Code:
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    On the line highlighted

    And I'm afraid I'm stumped as to what to try from here! My gut reaction was that my hosts don't have the mySQL driver installed; but that surely can't be right, considering that I'm using their mySQL db!

    Any ideas are truly welcome
    Last edited by gvee; 02-22-08 at 14:48.
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Any advances on this one?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    have you created a ODBC connection ???

    in the Administrative Tools -> Data Source(ODBC)
    on the server
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for the response myle.

    I've not created an ODBC DSN for 2 reasons
    1) I was trying to do it unbound
    2) My only options through the admin panel (I don't have direct access to the server - just the CP) is to create an Access, Excel, MS SQL or Text connection.
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    you do not have mysql

    still you say Server=localhost;

    Try ip adress of server / mysql db
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The database resides on the same server as the wwwroot, which is why I am using localhost.

    I will try the IP when I can.

    Cheers
    George
    Home | Blog

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Seems your missing a few imports

    First try 2nd 1 if that dosn't do it you'll have 2 import stuff
    BTW your code resides on a connection with dsn, this 2nd one don't

    Code:
    <%@ Page CompilerOptions='/R:"C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.data.odbc.dll"'%>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="Microsoft.Data.Odbc" %>
    <HTML>
    <HEAD>

    example
    Code:
    <% 
    'declare the variables 
    Dim Connection
    Dim ConnectionString
    Dim Recordset
    Dim SQL
    
    'declare the SQL statement that will query the database
    SQL = "SELECT * FROM TABLE_NAME"
    
    'define the connection string, specify database driver
    ConnString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=Your_Mysql_DB; " &_
    "UID=mysql_username;PASSWORD=mysql_password; OPTION=3"
    
    'create an instance of the ADO connection and recordset objects
    Set Connection = Server.CreateObject("ADODB.Connection")
    Set Recordset = Server.CreateObject("ADODB.Recordset")
    
    'Open the connection to the database
    Connection.Open ConnString
    
    'Open the recordset object executing the SQL statement and return records 
    Recordset.Open SQL,Connection
    
    'first of all determine whether there are any records 
    If Recordset.EOF Then 
    Response.Write("No records returned.") 
    Else 
    'if there are records then loop through the fields 
    Do While NOT Recordset.Eof   
    Response.write Recordset("FIRST_FIELD_NAME")
    Response.write Recordset("SECOND_FIELD_NAME")
    Response.write Recordset("THIRD_FIELD_NAME")
    Response.write "<br>"    
    Recordset.MoveNext     
    Loop
    End If
    
    'close the connection and recordset objects freeing up resources
    Recordset.Close
    Set Recordset=nothing
    Connection.Close
    Set Connection=nothing
    %>
    Last edited by Marvels; 02-28-08 at 06:18.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Finally got this one sorted: turns out the hosts forgot to install the mysql odbc drivers...

    Thanks for everyones help
    George
    Home | Blog

Posting Permissions

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