Menu
I am forced to move an old website that has been running well for years on a remote windows server using classic ASP (VBScript) and MS access databases.
The new hosting company (123-reg) does not support MS Access on their Windows server, so I am now using MySQL. I have used MySQL successfully on other sites but always using PHP rather than classic ASP, so I have never connected to a MySQL database from ASP before.
The website has hundreds of pages and I was hoping that all I had to do was change the connection strings. However I now get a 500 error
80004005|[Microsoft] ODBC_Driver_Manager]_Data_source_name_not_found_and_no_default_driver_specified
which has stumped me.
I have tried
set Conn=server.createobject("ADODB.CONNECTION")
set rs = server.createobject("ADODB.Recordset")
conn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & servername & "; DATABASE=" & database & "; UID=" & username & ";PASSWORD=" & pword & "; OPTION=3;"
I have also tried:
conn.open "DRIVER={SQL Server};SERVER=" & servername & "\SQLEXPRESS;DATABASE=" & database & ";UID=" & username & ";PWD=" & pword"
and after reading this previous answer to a similar queation:
conn.open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=" & servername & "; DATABASE=" & database & "; UID=" & username & ";PASSWORD=" & pword & "; OPTION=3;charset=utf8;"
and still get the same error.
I have hunted the Internet and this site but whenever I find information it assumes that I am using my own server, which I am not. The page at: https://support.microsoft.com/en-gb/kb/306345 suggests that I make changes to the server registry, which of course I am unable to do, and in any case I find it hard to accept that a company as large as 123-reg has to change things on their server to allow me to connect to a database.
I therefore come to the conclusion that I have something wrong (probably something simple) with my connection coding.
I did raise a ticket with 123-reg but they told me it is a coding problem and they do not help with that, which reinforces my theory.
I would be deeply appreciative for any help on this. I do plan to re-write the site in PHP now and I know that I can make that work well, but as there are hundreds of pages to code it will take me months and I am looking for a quick fix to get the site live again using the current pages whilst I re-write the whole site in PHP.
Many thanks
Tog
Update: 11th January 123-reg have come back to me and said the ODBC version is 5.1.8. I have changed the connection string but still get a 500 error, being:
HTTP/1.1 500 Internal Server Error Cache-Control: private Content-Length: 250 Content-Type: text/html Server: Microsoft-IIS/7.5 Set-Cookie: ASPSESSIONIDCCQSTSCA=FLAJJCCDFENAOEMMCGFGNPCF; path=/ X-Powered-By: ASP.NET Date: Wed, 11 Jan 2017 14:46:07 GMT Connection: close An error occurred on the server when processing the URL
There is no line number and no error description.
I have written a simple page to test the connection and my full code is:
<%@ Language=VBScript %>
<%
option explicit
Response.Buffer = true
'=============================
'database connection variables
dim servername, username, pword, database
servername = "cust-mysql-123-19" 'The name of the Server
username = "atourist" 'The username for the database
pword = "redacted for this post" 'The password for the database
database = "atourist" 'The database to use
'============================
dim rs
dim strSQL
dim conn
dim ds
dim county
dim numrec
county="Grampian"
%>
<html>
<head>
<title>Test Page</title>]
</head>
<body>
<%
' connect to database and retrieve recordset
set conn=server.createobject("ADODB.CONNECTION")
set rs = server.createobject("ADODB.Recordset")
strSQL = "select * from ukgh where county='" & county & "' ORDER by random ASC"
conn.open "DRIVER={MySQL ODBC 5.1.8. Driver}; SERVER=" & servername & "; DATABASE=" & database & "; UID=" & username & ";PASSWORD=" & pword & "; OPTION=3;"
set rs = conn.Execute(strSQL)
'===============================
ds=rs.GetRows()
rs.close
conn.close
set rs = nothing
set conn = nothing
'display records
for Numrec =LBound(ds, 2) To UBound(ds, 2)
response.write DS(1, numrec) & ", " & DS(2, numrec) & "<br />"
Next
%>
</body>
</html>
<%
response.end
%>
260
You will need to download and install the MySQLConnector in order to connect to the MySQL database in ASP.Net. After installation is complete you need to open Windows Explorer and look for the MySql installation in the Program Files folder of your Windows drive.
In order to connect MySQL database to a C# application, MySQL provides a series of classes in the MySQL Connector/Net. All the communication between a C# application and the MySQL server is routed through a MySqlConnection Object.
If your host is a x64 box, try both x64 connection strings with Provider=MSDASQL and MySQL ODBC 5.2w Driver (not sure if you shoud mention "8." in "5.1.8." - "5.1" or "5.2w" should be fine). If it will not help - check if 123-reg provides DSN for databases (you use DSN-less connection) or try to connect with IP of the database.
To ensure this is an asp problem, try to connect from a test php script (Example [1], [2]).
116
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
