PDF (adobe.com)

Create a connection using a connection string



You can use a DSN-less connection to create an ODBC or OLE DB connection between your web application and your database. You use a connection string to create this kind of connection.

  1. Open an ASP page in Dreamweaver, and then open the Databases panel (Window > Databases).
  2. Click the Plus (+) button on the panel, select Custom Connection String from the menu, complete the options, and click OK.
  3. Enter a name for the new connection without spaces or special characters.
  4. Enter a connection string to the database. If you do not specify an OLE DB provider in the connection string—that is, if you don’t include a Provider parameter—ASP will automatically use the OLE DB provider for ODBC drivers. In that case, you must specify an appropriate ODBC driver for your database.

    If your site is hosted by an ISP and you don’t know the full path to your database, use the MapPath method of the ASP server object in your connection string.

  5. If the database driver specified in the connection string is not installed on the same computer as Dreamweaver, select Using Driver On Testing Server.
    Note: Macintosh users can ignore this step because all database connections use the application server.
  6. You can restrict the number of database items Dreamweaver retrieves at design time by clicking Advanced and entering a schema or catalog name.
    Note: You cannot create a schema or catalog in Microsoft Access.
  7. Click Test to connect to the database, and then click OK. If the connection fails, double‑check the connection string or check the settings for the testing folder Dreamweaver uses to process dynamic pages.

Connecting to a database on an ISP

If you’re an ASP developer working with a commercial Internet service provider (ISP), you often don’t know the physical path of the files you upload, including your database file or files.

If your ISP doesn’t define a DSN for you or is slow to do so, you must find another way to create the connections to your database files. One alternative is to create a DSN-less connection to a database file, but you can define such a connection only if you know the physical path of the database file on the ISP server.

You can obtain the physical path of a database file on a server by using the MapPath method of the ASP server object.

Note: The techniques discussed in this section apply only if your database is file-based, such as a Microsoft Access database where data is stored in an .mdb file.

Understanding physical and virtual paths

After using Dreamweaver to upload your files to a remote server, the files reside in a folder in the server’s local directory tree. For example, on a server running Microsoft IIS, the path to your home page could be as follows:

c:\Inetpub\wwwroot\accounts\users\jsmith\index.htm

This path is known as the physical path to your file.

The URL to open your file, however, does not use the physical path. It uses the name of the server or domain followed by a virtual path, as in the following example:

www.plutoserve.com/jsmith/index.htm

The virtual path, /jsmith/index.htm, stands in for the physical path, c:\Inetpub\wwwroot\accounts\users\jsmith\index.htm.

Find a file’s physical path with the virtual path

If you work with an ISP, you don’t always know the physical path to the files you upload. ISPs typically provide you with an FTP host, possibly a host directory, and a login name and password. ISPs also specify a URL to view your pages on the Internet, such as www.plutoserve.com/jsmith/.

If you know the URL, then you can get the file’s virtual path—it’s the path that follows the server or domain name in a URL. Once you know the virtual path, you can get the file’s physical path on the server using the MapPath method.

The MapPath method takes the virtual path as an argument and returns the file’s physical path and filename. Here’s the method’s syntax:

Server.MapPath("/virtualpath")

If a file’s virtual path is /jsmith/index.htm, then the following expression returns its physical path:

Server.MapPath("/jsmith/index.htm")

You can experiment with the MapPath method as follows.

  1. Open an ASP page in Dreamweaver and switch to Code view (View > Code).
  2. Enter the following expression in the page’s HTML code. 
    <%Response.Write(stringvariable)%>
  3. Use the MapPath method to obtain a value for the stringvariable argument.

    Here’s an example:

    <% Response.Write(Server.MapPath("/jsmith/index.htm")) %>
  4. Switch to Design view (View > Design) and enable Live Data (View > Live Data) to view the page.

    The page displays the physical path of the file on the application server, for example:

    c:\Inetpub\wwwroot\accounts\users\jsmith\index.htm

    For more information on the MapPath method, consult the online documentation that comes with Microsoft IIS.

Use a virtual path to connect to a database

To write a DSN-less connection string to a database file located on a remote server, you must know the physical path to the file. The following example is a typical DSN-less connection string for a Microsoft Access database:

Driver={Microsoft Access Driver (*.mdb)}; 
DBQ=c:\Inetpub\wwwroot\accounts\users\jsmith\data\statistics.mdb

If you don’t know the physical path of your files on the remote server, you can get the path by using the MapPath method in your connection string.

  1. Upload the database file to the remote server and make a note of its virtual path—for example, /jsmith/data/statistics.mdb.
  2. Open an ASP page in Dreamweaver, then open the Databases panel (Window > Databases).
  3. Click the Plus (+) button on the panel and select Custom Connection String from the menu.
  4. Enter a name for the new connection, without spaces or special characters.
  5. Enter the connection string and use the MapPath method to supply the DBQ parameter.

    Suppose that the virtual path to your Microsoft Access database is /jsmith/data/statistics.mdb; the connection string can be expressed as follows if you use VBScript as your scripting language:

    “Driver={Microsoft Access Driver (*.mdb)};DBQ=” & Server.MapPath¬ 
("/jsmith/data/statistics.mdb")

    The ampersand (&) is used to concatenate (combine) two strings. The first string is enclosed in quotation marks and the second is returned by the Server.MapPath expression. When the two strings are combined, the following string is created:

    Driver={Microsoft Access Driver (*.mdb)}; 
DBQ=C:\Inetpub\wwwroot\accounts\users\jsmith\data\statistics.mdb

    If you use JavaScript, the expression is identical except that you use a Plus (+) sign instead of an ampersand (&) to concatenate the two strings:

    “Driver={Microsoft Access Driver (*.mdb)};DBQ=” + Server.MapPath¬ 
("/jsmith/data/statistics.mdb")
  6. Select Using Driver On Testing Server, click Test, and then click OK.
    Note: Macintosh users can ignore this step because all database connections use the application server.
    Note: If the connection fails, double-check the connection string or contact your ISP to make sure that the database driver you specified in the connection string is installed on the remote server. Also check that the ISP has the most recent version of the driver. For example, a database created in Microsoft Access 2000 will not work with Microsoft Access Driver 3.5. You need Microsoft Access Driver 4.0 or later.
  7. Update the database connection of existing dynamic pages (open the page in Dreamweaver, double-click the recordset name in the Bindings panel or Server Behaviors panel, and select the connection you just created from the Connection menu) and use the new connection with any new page you build.