how-to #11: using Server.MapPath and a DSN-less connection in Dreamweaver

The previous two pages showed how to create DSN-based and DSN-less database connections using ASP, and open a recordset. So you create your connection using the method of your choice, and it works fine on your local server. And then you want to upload the site to your web host, which is when you run into a problem.

If you’re using a DSN, you will have to ask your web host to create the equivalent DSN on the server itself. Some hosts won’t do that, or will only allow you one or two DSNs, which is very restricting. The answer, of course, is to use a DSN-less connection, because you can have as many of those as you like.

Let’s assume you have a site called ‘ourclub’ with an Access membership database called ‘club.mdb’ located in a subdirectory of your site called ‘dbase’. The site structure might look like this, where ‘index.asp’ is in the root of the site ‘club’:

index.asp
Connections (folder)
|
--> conn_ourclub_noDSN.asp
dbase (folder)
|
--> club.mdb
html (folder)
|
--> details.asp

If you are using Dreamweaver MX or later you can use the custom connection dialog box to create a DSN-less connection, and Dreamweaver creates an ASP file – the ‘conn_ourclub_noDSN.asp’ file in the diagram above – that looks something like this:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_Club_noDSN_STRING
MM_Club_noDSN_STRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\inetpub\wwwroot\ourclub\dbase\club.mdb"
%>

This works very well on your system. It solves the DSN problem, but adds another, because this connection string needs the absolute path to the database file. Of course, you know what this on your system… but you cannot know what it is on the remote web host. This is easy to solve. The ASP Server object has a MapPath method which returns the absolute path to a file. All we do is substitute the last but one line in the above code for this:

MM_Club_noDSN_STRING = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("dbase/club.mdb")

The file is then included by Dreamweaver, using a server-side include, in all your files which use this connection. Now, this works fine when ‘index.asp’ accesses the database, but fails when ‘details.asp’, which is in its own sub-directory, tries to do so. Why?

The Server.MapPath example above is passed a relative path, that is, relative to the script file which calls it. In the case of index.asp, which is in the root folder, the database is found in the folder ‘dbase’ which is a sub-directory of the root folder, and therefore works fine. When details.asp, which is located in the ‘html’ folder, calls the method, the server tries to find the database in a sub-directory of the html folder – and obviously, cannot do so. You’ll get an error which is a bit confusing – instead of telling you that it can’t find the database, it tells you it is unable to create a registry token for it. But that’s all it means – it can’t find the database.

The obvious solution to this is to alter the method call so that it reads:

Server.MapPath(“../dbase/club.mdb”)

But the obvious solution is wrong, and will generate an ASP error as Server.MapPath() cannot use the double-dot notation to indicate the parent directory. This is quite apart from the fact that you would need different connection files depending on where the script file was located in relation to the database.

Fortunately, the solution is simple. You can make Server.MapPath use a virtual path which starts from the root of the site. It’s a very simple change:

Server.MapPath(“/club/dbase/club.mdb”)

The initial slash in the string tells the server that it is to consider this a path from the root folder. This connection string will then work in all pages, no matter where they are in relation to the actual database file: the correct physical location of the database will always be returned.

(Actually, there’s a slight catch here. This should work on any remote host you are using but may fail on your local system. If you set up a web server on your local machine and use IIS as it comes out of the box, the default web site is located at c:\inetpub\wwwroot. The ‘club’ site is created in a sub-folder of the root site, which is why the full virtual path is passed in Server.MapPath. But if like me you put your web sites in another folder – I use ‘d:\sites’ then you need to make this the home directory of the default web site, or else Server.MapPath will return the wrong path.)

Problem solved? Not quite, because now you can’t use this connection with any of the server behaviours in Dreamweaver. If you try, Dreamweaver will complain that you aren’t connected to the database. Again, the solution is simple. When setting up a connection in Dreamweaver, you have to specify whether Dreamweaver will connect using the driver on the local machine, or connect using the testing server. Normally, this doesn’t make any difference and probably most people choose the local driver option as it is the default. But if you are going to use Server.MapPath like this, you need to specify that the connection must work through the testing server, so you must change your connections accordingly.

And finally… there’s still a potential problem. Each page using the connection file will have to load it when the page runs using a server-side include, such as this:

<!--#include file="Connections/conn_ourclub_noDSN.asp" -->

This is how the SSI would appear in index.asp as Dreamweaver writes it (in its default configuration). For details.asp, the SSI would look like this:

<!--#include file="../Connections/conn_ourclub_noDSN.asp" -->

This all works fine on your testing server, but when you upload it to the remote host, when details.asp runs it fails with the following error:

Active Server Pages, ASP 0131 (0x80004005)
The Include file '../Connections/conn_ourclub_noDSN.asp' cannot contain
'..' to indicate the parent directory.
/club/html/details.asp, line 2

So you look on the net and find that the double-dot notation is legal syntax in SSI statements. What’s the problem?

It turns out that there is a setting in IIS called ‘Enable parent paths’. Normally this is turned on, to allow the double-dot relative path notation, but it is possible to turn it off, and most shared web hosts do so for security reasons. Once again, there is a solution, and as with Server.MapPath it is to use a full virtual path rather than a relative path. You do it like this:

<!--#include virtual="/club/Connections/conn_club_noDSN.asp" -->

Notice the keyword ‘virtual’ and the full path from the web site root. You have to change all the includes manually, but it will work on both remote and local hosts when you do.