Link to home
Start Free TrialLog in
Avatar of STP8377
STP8377

asked on

.asp connection string to MySQL WordPress database

Can somebody please help me figure out 2 things.
1) what should I use for a  database connection string to connect a .asp page to a WordPress MySQL database.  I have asked the web host for a URL or IP address for the database and they refuse to get me any information.
2) should I put the connection.asp   file and the  .asp page in the httpdocs  folder?  Or, in the httpdocs/WordPress folder?

the information below is from my wp-config.php

/** The name of the database for WordPress */
define('DB_NAME', 'tcmassag_mysql05ca7bc461cb8285c7d0698c2a3eec46');

/** MySQL database username */
define('DB_USER', 'tcmas_rickDB');

/** MySQL database password */
define('DB_PASSWORD', 'MySecretPassword');

/** MySQL hostname */
define('DB_HOST', 'localhost');
Avatar of G_H
G_H
Flag of United Kingdom of Great Britain and Northern Ireland image

Are you running the ASP script on the same server ad the php?

GH
Avatar of STP8377
STP8377

ASKER

I copied the information below from http://webhostingworld.net/cheap_windows_hosting_details.php
from their information about "shared Windows Hosting".  It looks like the answer to your question is yes, ASP script and PHP are on the same server.

Number of MySql Databases             1
PhPmyadmin                         Yes
Number of MsSql Ddatabases (2005 / 2008)      1
Ms Sql Web Admin          My Little Admin
Ms Access Support      Yes
ODBC Support                       Yes

==== Server Side Scripts =====
ASP.NET 4.0, 3.5, 3.0, 2.0 & 1.1      Yes
PHP 4.4.7 & PHP 5.2.13      Yes
Server Side Includes (SSI)      Yes
Zend Optimizer 3.0.1      Yes
GD Library      Yes
ASP      Yes
Fastcgi           Yes
Python        Yes
CGI support      Yes
Perl 5.8.7         Yes
In that case, you should be able to use 'localhosts' in your connection string, as well as the username and password.

All ee need to know now is which ODBC driver they have installed. The most common ate 3.51 and 5.1. I'll post shortly.
OK here is a "modified for you" version of the script I use...

 
'' ## Dim Exerything
Dim DBServer, Database, DBPass, DBUser

'' ## Database settings
DBServer = "localhost"
DBUser   = "tcmas_rickDB"
DBPass   = "MySecretPassword"
DataBase = "tcmassag_mysql05ca7bc461cb8285c7d0698c2a3eec46"

Function CreateDatabaseConnection()
	Set Conn = Server.CreateObject("ADODB.Connection")
		'' ## Use ONE of these
		Conn.Open "driver={MySQL ODBC 5.1 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
		Conn.Open "driver={MySQL ODBC 3.51 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
End Function

Function CloseDatabaseConnection()
		Conn.Close
	Set Conn = Nothing
End Function

Open in new window


I always put this a directory away from public eyes, in a file called "database.asp" which is "included".

You do not always have the ability in ASP to go "up" a directory, but id you do use:

 
<!--#include file="../hidden/database.asp"-->

Open in new window


GH
Avatar of STP8377

ASKER

I need to clarify a couple of things. Do I copy all the code and replace the password with my real password and then save the file as connection.asp?  I can deal with the "include" and putting the connection file away after I get it working, but for testing purposes, do I put both the connection file and the .asp file in the httpdocs folder?  I tried putting both files in the httpdocs folder and in the sub folder named wordpress and get an error message with either location.  The code is for the file I am using to test the connection, let me know if there is an easier way to test the connection.
<!--#INCLUDE VIRTUAL="/connectionv3.asp" -->

<p>
Venue <a href="usersortv3.asp?OB=f_a">ASC</a> sort <a href="usersortv3.asp?OB=f_d">DESC</a><br>
Subject <a href="usersortv3.asp?OBt=l_a">ASC</a> sort <a href="usersortv3.asp?OB=l_d">DESC</a>
</p>


<%
DIM strOrderBy, tmpOrderBy
strOrderBy = Request.QueryString("OB")
Select Case strOrderBy
Case "f_a"
tmpOrderBy = "ORDER BY Subject ASC"
Case "fn_d"
tmpOrderBy = "ORDER BY Subject DESC"
Case "l_a"
tmpOrderBy = "ORDER BY Venue ASC"
Case "l_d"
tmpOrderBy = "ORDER BY Venue DESC"
Case Else
tmpOrderBy = "ORDER BY Subject DESC"
End Select

DIM mySQL, objRS
mySQL = "Select Subject, Venue FROM tutorials_meetings " & tmpOrderBy
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn

DO WHILE NOT objRS.EOF
%>

<%=objRS("Subject")%> <%=objRS("Venue")%><br>

<%
objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

Open in new window

For testing, I would be temped to put all of the code in a single file, just so as to exclude all possible errors. Put this file into the httpdocs folder:

 
'' ## Connection Test

'' ## Dim Exerything
Dim Conn, DBServer, Database, DBPass, DBUser
Dim sqlA, rsA

'' ## Database settings
DBServer = "localhost"
DBUser   = "tcmas_rickDB"
DBPass   = "MySecretPassword"
DataBase = "tcmassag_mysql05ca7bc461cb8285c7d0698c2a3eec46"

CreateDatabaseConnection

'' ## Creta some sql
sqlA = "SELECT Subject, Venue FROM tutorials_meetings"
'' ## Set the recordset
Set rsA = Conn.execute(sqlA)
'' ## Loop the recordset
while NOT(rsA.EOF)
'' ## Write something to screen
response.write rsA("Subject") & "<br />"
rsA.movenext
wend
rsA.close
Set rsA = nothing

CloseDatabaseConnection

Function CreateDatabaseConnection()
	Set Conn = Server.CreateObject("ADODB.Connection")
		'' ## Use ONE of these
		Conn.Open "driver={MySQL ODBC 5.1 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
		Conn.Open "driver={MySQL ODBC 3.51 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
End Function

Function CloseDatabaseConnection()
		Conn.Close
	Set Conn = Nothing
End Function

Open in new window


...If this gives you an error, post the error message, as it may help us get to the bottom of the problem...

GH
Avatar of STP8377

ASKER

I put all of the code above into a file (with a text editor) and changed the password to my real password and named the file        test.asp  
When I put the file in the httpdocs folder and put this in the browser url address  http://tcmassagereviews.com/test.asp
the file contents displayed in the browser window, then I put the same file in the httpdocs/wordpress subfolder:    http://tcmassagereviews.com/wordpress/test.asp
and got a 404 Not Found error:
The requested document was not found on this server.      Web Server at tcmassagereviews.com
If you put this into the httpdocs folder, and the files shows as text in your browser, then the server is not setup to (process and) serve ".asp" pages.

Check this with their tech support.

GH
Avatar of STP8377

ASKER

I submitted a ticket to www.webhostingworld.net      I don't see the file contents/text in the browser when I run the .asp pages from the WordPress folder, does that indicate anything?
Because you got a 404, there is something else going on there. Can you upload a PHP info file to each location, and view the results?
Avatar of STP8377

ASKER

User generated image User generated image
Avatar of STP8377

ASKER

You can view the php info files here:
http://tcmassagereviews.com/wordpress/phpinfo.php
and here:
http://tcmassagereviews.com/phpinfo.php

the are exactly the same except for the entry in the httpdocs folder is:
Server API  CGI/FastCGI  
and the same entry in the httpdocs/wordpress folder is:
Server API  ISAPI  
Avatar of STP8377

ASKER

Disregard my comments about both versions being the same, I didn't realize that there was a lot more to the file/info than the images I uploaded.
Do you think the connection string needs to specify the website IP address instead of "localhost"  ?
OK, your post #34881200. This is all about PHP. References to the "Server API" are "how" the windows server serves PHP pages. This has nothing to do with ASP. It does however show that both places are having PHP executed.

For your post #34881217. MySQL authentication uses "user name". "password" and "place" to decide if it is going to give you access. As your PHP scripts are connecting to "localhost" your ASP should too. If not, it is possible that the request for access will look like it is coming from "outside", and there for your username and password may not work.

While you are waiting for your support ticket to come back, lets try a simple test. save the following as "write.asp" and upload it to both locations:

<%
response.write "Hello World!"
%>


Lets see what you get when you browse to each location...

GH

Avatar of STP8377

ASKER

Thanks again for your help.  The support ticket said: "we have asp.net installed on the server. It works for application and httpdocs too.

I put your Hello World file in both locations, you can view the results below.  It works in httpdocs (the first URL below), but not in the WordPress folder.
http://tcmassagereviews.com/write.asp
I get the 404 not found error in the wordpress folder below
http://tcmassagereviews.com/wordpress/write.asp
OK, so the Word Press directory has something special going on with it. We can view a PHP page you put in there, but not an ASP page...

So we know we can run ASP in the root, but not the wordpress directory.

As I see it, we have two options:

1. go back to the host and ask them to prove the hello world does work in the wordpress folder.

2. Try to run a database script from the httpdocs directory.

Probably best to do both.

I have commented-out one of the connection strings, if it does not work, try the other one. This should hopefully give us an error message at least...

 
response.write "Easy As: "
'' ## Dim Exerything
Dim DBServer, Database, DBPass, DBUser

'' ## Database settings
DBServer = "localhost"
DBUser   = "tcmas_rickDB"
DBPass   = "MySecretPassword"
DataBase = "tcmassag_mysql05ca7bc461cb8285c7d0698c2a3eec46"

response.write "1"
Call CreateDatabaseConnection()
response.write "2"
Call CloseDatabaseConnection()
response.write "3"

Function CreateDatabaseConnection()
	Set Conn = Server.CreateObject("ADODB.Connection")
		'' ## Use ONE of these
		Conn.Open "driver={MySQL ODBC 5.1 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
'		Conn.Open "driver={MySQL ODBC 3.51 Driver};server=" & DBServer & ";uid=" & DBUser & ";pwd=" & DBPass & ";database=" & Database
End Function

Function CloseDatabaseConnection()
		Conn.Close
	Set Conn = Nothing
End Function

Open in new window


GH
Avatar of STP8377

ASKER

you can view the results, I named the files easy3-51.asp  and  easy5-1.asp  and commented out one of the a different connection string in each one.
http://tcmassagereviews.com/easy5-1.asp    displays the file contents
http://tcmassagereviews.com/wordpress/easy5-1.asp     error 404 not found  The requested document was not found on this server.
http://tcmassagereviews.com/wordpress/easy3-51.asp   error 404 not found
http://tcmassagereviews.com/easy3-51.asp     displays the file contents

I will create another support ticket right now for the Hello World in the WordPress folder
Avatar of STP8377

ASKER

Is it possible to run the .asp page from the httpdocs folder and make it retrieve data from the wordpress database?   Can .asp retrieve data from a subfolder like that?  I don't even think the wordpress database is actually in the wordpress folder but I don't know where it is located.
Avatar of STP8377

ASKER

Another Option:  Maybe I could install a new instance of wordpress and this time -not- install it into a sub-folder?  I think that is possible, I think the default was that it installed into a sub-folder and I did not notice at the time that I was running the installation.  I would need to move the website and delete the sub folder version of wordpress but that's ok.
Change the password in the easy files as they ate displaying online.

ASKER CERTIFIED SOLUTION
Avatar of G_H
G_H
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your Post: #34883374, delete these files NOW. Then change your password for a new one.

As a "Good Deed" I have taken a backup of your MySQL Database, but I do not know if anyone had messed with it, by the time I had. 20:45 GMT. If you need this file, or need it restored, PM me.

Your Post: #34883398, "MySQL database" is *NOT* IN the Word Press folder. MySQL is on the "Server", and can be accessed by anybody with the right user name and password. As my post above shows, I am able to access your Database from My Server, here in my office!

Your Post #34883522, This should not be necessary. You may need to tell us more about what you are actually trying to achieve before we decide... But probably not.
 
Avatar of STP8377

ASKER

Thanks again.  I deleted the files and changed my password.  I submitted a ticket to the hosting company about the Hello World files not working in the WordPress folder and they fixed the problem.  Before the hello world file only ran in the httpdocs folder now it runs in both.  I named the file helloworld.asp, you can see the results below.  Should I delete these two files right away?
I also backed up the database, just coincidentally.
http://tcmassagereviews.com/helloworld.asp
http://tcmassagereviews.com/wordpress/helloworld.asp
I hope I got both password changed correctly.

I put your new code in a file named new.asp and put it in both folders.  I get a 500 internal server error
Ok good. Now we have ASP enabled everywhere we stand a better chance.

Two things now:
Do you just get 500 or does it have mote detail? If no extra detail make sure your browser has friendly error messages turned off. Then post the full error.

Also, change from 3.51 to 5.1. Ad we don't know what they are using...

GH
Avatar of STP8377

ASKER

The error message is below, same message for both folders.  I switched from 3.51 to 5.1 and the error message is the same.
=================
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error.
More information about this error may be available in the server error log.
OK, almost exactly as the message says...

Download your W3C logfile, and search for "new.asp", lets see if there is more information in there...

GH
Avatar of STP8377

ASKER

I have attached the file, I think this is the right file, let me know if it is not. u-ex110213.log
Avatar of STP8377

ASKER

I also found this file on the server, it looks like it is used for testing, I am including the code in case you think it might be helpful.
<%@ Language=VBScript%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%
mes=""
IsSuccess = false

sServer = Trim(Request.Form("txtServer"))
sPort = Trim(Request.Form("txtPort"))
sUser = Trim(Request.Form("txtUser"))
sPassword = Trim(Request.Form("txtPassword"))

if Request("__action")="TestDB" then
  TestDB()
end if

Sub TestDB()

  Err.Clear()
  on error resume next
  Set objConn = Server.CreateObject("ADODB.Connection")
  if len(Err.Description)<>0 then
    mes = " " & Err.Description & " MySQL connection can't be established!"
  else
    objConn.ConnectionString = _
    "DRIVER={MySQL ODBC 3.51 Driver};PORT=" & sPort & _
    ";SERVER=" & sServer & _
    ";UID=" & sUser & _
    ";PWD=" & sPassword
    objConn.Open
    if len(Err.Description)<>0 then
      mes = " " & Err.Description & " MySQL connection can't be established!"
    else
      mes = " MySQL connection succesfull established!"
      IsSuccess = true
    end if
  end if
  Set objConn = Nothing
End sub

Sub Alert(html)
  if IsSuccess then
    Response.Write "<div class='testRelults' id='testSuccessful'><span class='testResult'>Success:</span>" & html & "</div>"
  else
    Response.Write "<div class='testRelults' id='testFailed'><span class='testResult'>Fail:</span>" & html & "</div>"
  end if
End Sub
%>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>ASP test page.</title>
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Cache-Control" content="no-cache" />
<link rel="shortcut icon" href="favicon.ico" type="image/x-icon" />
<link rel="icon" href="favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../../css/style.css" />
<link rel="stylesheet" type="text/css" href="../../css/tabs.css" />
<!--[if lte IE 7]><style type="text/css">#tabs li, #tabs a { display: inline; zoom: 1; } #tabs li { margin: 0; }</style><![endif]-->
<script type="text/javascript" src="/header.js"></script>
<script type="text/javascript">
//<![CDATA[
writeCopyFlag();
//]]>
</script>
</head>
<body>
<div class="page">
  <form id="form1" action="test_mysql.asp?__action=TestDB&tp=<%= rnd(1)*100*timer %>" method="POST">
  <input id="__action" type="hidden" value="" />
  <div class="header clear">
    <script type="text/javascript">
    //<![CDATA[
      if (window.product_copyrights) { writeHeader(window.plesk_promo.virtuozzo); }
    //]]>
    </script>
  </div>
  <div class="wrapper test">
    <div class="pathbar"><a href="/index.html">Site Home Page</a> &gt;</div>
    <h2>ASP possibilities test page</h2>
    <div id="tabs">
      <ul>
        <li class="first" id="current"><a href="test_mysql.asp"><span>MySQL</span></a></li>
        <li><a href="test_mssql.asp"><span>MSSQL</span></a></li>
        <li><a href="test_msaccess.asp"><span>MS Access</span></a></li>
        <li class="last"><a href="test_mail.asp"><span>E-Mail</span></a></li>
      </ul>
    </div>
    <!-- MySQL server -->
    <div class="tab-content clear">
      <p>This page allows to check the connection possibility between the SQL client on your host and one of remote database server. You should have working accounts on the database servers you want to test. Here you can test the connection possibility with the MySQL server.</p>
      <% if len(mes) > 0 then	Alert(mes) end if %>
      <fieldset>
        <legend id="LegendName">Test MySQL Connection</legend>
        <table class="formFields" cellspacing="0" width="100%">
          <tr>
            <td class="name"><label id="lblSource" for="txtServer">Server</label></td>
            <td><input type = text name="txtServer" size = "25" value = "<% Response.Write(sServer) %>"></td>
          </tr>
          <tr>
            <td class="name"><label for="txtPort">Port</label></td>
            <td><input type = text name="txtPort" MaxLength="4" size="5" value="<% if len(sPort)=0 then Response.Write("3306") else Response.Write(sPort) end if%>"></td>
          </tr>
          <tr>
            <td class="name"><label for="txtUser">User name</label></td>
            <td><input type = text name="txtUser" size="25" value = "<% Response.Write(sUser) %>"></td>
          </tr>
          <tr>
            <td class="name"><label for="txtPassword">Password</label></td>
            <td><input type = password  name="txtPassword" size="25"></td>
          </tr>
        </table>
      </fieldset>
      <div class="buttonsContainer">
        <div class="commonButton" id="DBTestButton" title="Test"><button type="submit" name="bname_ok">Test</button><span>Test</span></div>
      </div>
    </div>
  </div>
  </form>
  <div class="footer">
    <div class="footer-area">
    <script type="text/javascript">
    //<![CDATA[
      if (window.product_copyrights) { writeFooter(window.plesk_promo.virtuozzo); }
    //]]>
    </script>
    </div>
  </div>
</div>
</body>
</html>

Open in new window

OK, this tells me that they have got the 3.51 driver installed, so let stick with that. Delete the 5.1 line.

I can also see that you are connecting to the Database Server using the code, which is also good.

The next thing is the response from the MySQL Server, which is saying:

Access_denied_for_user_'tcmas_rickDB'@'WIN-OHYAI7ESQG3'_(using_password:_YES)

This tells me that the username / password combination is wrong. I have managed to connect with the original password, so I would suggest:

First, try to use the old password. I think this will let you connect.

Second, change the MySQL database password, as this one has been in the public domain.

GH
Avatar of STP8377

ASKER

I filled out the code previously submitted and I get this page, what do I use for the server name?

 User generated image
Post #34887609, use your domain name. I am sure I do not need to tell you, but the user name in the image is wrong...

Have you tried the old password in the "new.asp" file? I am sure this will work

GH
Avatar of STP8377

ASKER

Should I keep the server as:      DBServer = "tcmassagereviews.com"
this is what it shows in the plesk panel:      Server: localhost:3306
or should I try just   localhost

To change the database password, i change the password in the wp-config.php file right?  
Try my code (new.asp) first, before anything else. (with the old password).

I mean it!!

AFTER that, try:
DBServer = "localhost"

I think this will work too...

To change the MySQL password, you may have to speak to your host, or you may be able to do this in your "Control Panel".

Changing the password in "wp-config.php" changes the password USED by WP to connect to the database. not the password the database uses to protect itself.

You will need to change the wp-config.php file, but not until after you have sorted the MySQL password elsewhere...

GH
Avatar of STP8377

ASKER

It works!     just as you said, the new code, etc..  In both folders  httpdocs and the wordpress folder
Brilliant! OK, now test with the DBServer set to localhost, just to check...
Avatar of STP8377

ASKER

Still working in both folders with  DBServer set to localhost

I added your code from earlier to display some data from the database, just to see and it worked, you can see the results at:    http://tcmassagereviews.com/newd.asp
(the file is named newd because I started with newa, newb, newc)
=================== code added =============
'' ## Creta some sql
sqlA = "SELECT Subject, Venue FROM tutorials_meetings"
'' ## Set the recordset
Set rsA = Conn.execute(sqlA)
'' ## Loop the recordset
while NOT(rsA.EOF)
'' ## Write something to screen
response.write rsA("Subject") & "<br />"
rsA.movenext
wend
rsA.close
Set rsA = nothing
OK, now we have fully working code, using the principal of minimum changes, we need to get the Database Password changed.

I can still connect to your database, from here, so I know the current password is the one which was diplayed on line for a while.

I am not sure how you go about changing this, but I guess you have a "Hosting Control Panel" where you can change the "MySQL Database Password". IF this is the case, this needs doing next.

You should be able to prove that the change has been made, because the ASP script will not work any more.

Please post back when you have done this...

GH
Avatar of STP8377

ASKER

Still waiting for hosting company to figure out how to change the password.
Lol, that's a good advert for webhostingworld.
Avatar of STP8377

ASKER

whw tells me that the database password cannot be changed.  They say the only option is to delete or uninstall WordPress and do a fresh installation.    Unless I find out differently, I will do another backup just to be safe and uninstall/reinstall and then upload/restore my backup.  I've tried changing every password that know of and I tried all the Google  and Wordpress support forum suggestions.
Avatar of STP8377

ASKER

I will close this question soon.  I really, really appreciate the huge amount of effort and troubleshooting you did G H.  Thank for sticking it out and getting this fixed.  
What do you think was the problem?  Or, was it a lot of little things, syntax, etc.

I will take care of the password problem in the next few days, I will do one of several options, I installed wp into a subfolder by mistake so I will most likely backup/copy everything and then wipe everything out and reinstall wp at the main level.
I think there were lots of issues here...

1. Poor support from the hosting company - not telling you the server address (you.domain or localhost).
2. Poor support from the hosting company - not telling you which connector (3.51) they have installed.
3. Poor advice from GH (LOL) - not telling you to put your code inside <%%>
4. Poor service from the hosting company, not providing ASP on all of your web-space.
5. Your poor understanding of code (don't take this the wrong way - we all start at the beginning). You are also doing the right thing, which is learning from the process, that is good to see. Too many people ask questions here to *just* get a solution, whereas they would be better off learning something!

So on the plus side...

1. I think the code provided will help you connect to your database.
2. I think you have learnt about how these connections done, and now understand the terminology.
3. I think you have probably learnt a lot about de-bugging as well...

So good luck with your PW change...

Happy programming,

GH