Connecting to mySQL database

gvee · #1 (**permalink**) 02-22-08, 13:41

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

gvee · #2 (**permalink**) 02-25-08, 08:52

Any advances on this one?

myle · #3 (**permalink**) 02-25-08, 15:51

have you created a ODBC connection ???

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

gvee · #4 (**permalink**) 02-26-08, 13:00

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.

Marvels · #5 (**permalink**) 02-28-08, 04:23

still you say Server=localhost;

Try ip adress of server / mysql db

gvee · #6 (**permalink**) 02-28-08, 04:46

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

Marvels · #7 (**permalink**) 02-28-08, 05:14

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
%>

gvee · #8 (**permalink**) 03-05-08, 08:19

Finally got this one sorted: turns out the hosts forgot to install the mysql odbc drivers...

Thanks for everyones help

Internet Services

Web Development

Digital Marketing

Consumer Tech