Note:

The user interface has been simplified in Dreamweaver CC and later. As a result, you may not find some of the options described in this article in Dreamweaver CC and later. For more information, see this article.

About ASP database connections

An ASP application must connect to a database through an open database connectivity (ODBC) driver or an object linking and embedding database (OLE DB) provider. The driver or provider acts as an interpreter that lets the web application communicate with the database. The following table shows some drivers you can use with Microsoft Access, Microsoft SQL Server, and Oracle databases:

Database

Database driver

Microsoft Access

Microsoft Access Driver (ODBC)

Microsoft Jet Provider for Access (OLE DB)

Microsoft SQL Server

Microsoft SQL Server Driver (ODBC)

Microsoft SQL Server Provider (OLE DB)

Oracle

Microsoft Oracle Driver (ODBC)

Oracle Provider for OLE DB

You can use a data source name (DSN) or a connection string to connect to the database. You must use a connection string if you’re connecting through an OLE DB provider or an ODBC driver not installed on a Windows system.

A DSN is a one-word identifier, such as myConnection, that points to the database and contains all the information needed to connect to it. You define a DSN in Windows. You can use a DSN if you’re connecting through an ODBC driver installed on a Windows system.

A connection string is a hand-coded expression that identifies the database and lists the information needed to connect to it, as shown in the following example:

Driver={SQL Server};Server=Socrates;Database=AcmeMktg; 
UID=wiley;PWD=roadrunner

Note:

You can also use a connection string if you’re connecting through an ODBC driver installed on a Windows system, but using a DSN is easier.

About OLE DB connections

You can use an OLE DB provider to communicate with your database (OLE DB is available only on Windows NT, 2000, or XP). Creating a direct database-specific OLE DB connection can improve the speed of your connection by eliminating the ODBC layer between your web application and the database.

If you don’t specify an OLE DB provider for your database, ASP uses the default OLE DB provider for ODBC drivers to communicate with an ODBC driver, which in turn communicates with the database.

Different OLE DB providers exist for different databases. You can obtain OLE DB providers for Microsoft Access and SQL Server by downloading and installing the Microsoft Data Access Components (MDAC) 2.5 and 2.7 packages on the Windows computer running IIS. You can download the MDAC packages for free from the Microsoft website.

Note:

Make sure you install MDAC 2.5 before installing MDAC 2.7.

You can download OLE DB providers for Oracle databases from the Oracle website.

In Dreamweaver, you create an OLE DB connection by including a Provider parameter in a connection string. For example, here are parameters for common OLE DB providers for Access, SQL Server, and Oracle databases, respectively:

Provider=Microsoft.Jet.OLEDB.4.0;... 
Provider=SQLOLEDB;... 
Provider=OraOLEDB;...

For the parameter value of your OLE DB provider, see your provider vendor’s documentation, or consult your system administrator.

About connection strings

A connection string combines all the information your web application needs to connect to a database. Dreamweaver inserts this string in your page’s server-side scripts for later processing by your application server.

A connection string for Microsoft Access and SQL Server databases consists of a combination of the following parameters separated by semicolons:

Provider

Specifies the OLE DB provider for your database. For example, here are parameters for common OLE DB providers for Access, SQL Server, and Oracle databases, respectively:

Provider=Microsoft.Jet.OLEDB.4.0;... 
Provider=SQLOLEDB;... 
Provider=OraOLEDB;...

For the parameter value of your OLE DB provider, see your provider vendor’s documentation, or consult your system administrator.

If you don’t include a Provider parameter, then the default OLE DB provider for ODBC is used and you must specify an appropriate ODBC driver for your database.

Driver

Specifies the ODBC driver to use if you don’t specify an OLE DB provider for your database.

Server

Specifies the server hosting the SQL Server database if your web application runs on a different server.

Database

The name of a SQL Server database.

DBQ

The path to a file-based database such as one created in Microsoft Access. The path is the one on the server hosting the database file.

UID

Specifies the user name.

PWD

Specifies the user password.

DSN

The data source name, if you use one. Depending on how you define the DSN on your server, you can omit the connection string’s other parameters. For example, DSN=Results can be a valid connection string if you define the other parameters when you create the DSN.

Connection strings for other kinds of databases may not use the parameters listed above, or will have different names or uses for the parameters. For more information, see your database vendor’s documentation, or consult your system administrator.

Here’s an example of a connection string that will create an ODBC connection to an Access database called trees.mdb:

Driver={Microsoft Access Driver (*.mdb)}; 
DBQ=C:\Inetpub\wwwroot\Research\trees.mdb

Here’s an example of a connection string that will create an OLE DB connection to a SQL Server database called Mothra located on a server called Gojira:

Provider=SQLOLEDB;Server=Gojira;Database=Mothra;UID=jsmith; 
PWD=orlando8

Create a connection using a local DSN

Note:

This section assumes you have set up an ASP application. It also assumes a database is set up on your local computer or on a system to which you have network or FTP access.

You can use a data source name (DSN) to create an ODBC connection between your web application and your database. A DSN is a name containing all the parameters needed to connect to a specific database using an ODBC driver.

Because you can only specify an ODBC driver in a DSN, you must use a connection string if you want to use an OLE DB provider.

You can use a locally defined DSN to create a database connection in Dreamweaver.

  1. Define a DSN on the Windows computer running Dreamweaver.

    For instructions, see the following articles on the Microsoft website:

  2. Open an ASP page in Dreamweaver, and then open the Databases panel (Window > Databases).
  3. Click the Plus (+) button on the panel and select Data Source Name (DSN) from the menu.
  4. Enter a name for the new connection, without spaces or special characters.
  5. Select the Using Local DSN option and choose the DSN you want to use from the Data Source Name (DSN) menu.

    If you want to use a local DSN but haven’t defined one yet, click Define to open the Windows ODBC Data Source Administrator.

  6. Complete the User Name and Password boxes.
  7. 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.

  8. 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 test folder Dreamweaver uses to process dynamic pages.

Create a connection using a remote DSN

Note:

This section assumes you have set up an ASP application. It also assumes a database is set up on your local computer or on a system to which you have network or FTP access.

Note:

Dreamweaver can retrieve only server DSNs created with the Windows ODBC Data Source Administrator.

You can use a DSN defined on a remote computer to create a database connection in Dreamweaver. If you want to use a remote DSN, the DSN must be defined on the Windows computer running your application server (probably IIS).

Note:

Because you can only specify an ODBC driver in a DSN, you must use a connection string if you want to use an OLE DB provider.

  1. Define a DSN on the remote system running your application server.

    For instructions, see the following articles on the Microsoft website:

  2. Open an ASP page in Dreamweaver, then open the Databases panel (Window > Databases).
  3. Click the Plus (+) button on the panel and select Data Source Name (DSN) from the menu.
  4. Enter a name for the new connection, without spaces or special characters.
  5. Select Using DSN On Testing Server.

    Note:

    Macintosh users can ignore this step because all database connections use DSNs on the application server.

  6. Enter the DSN or click the DSN button to connect to the server and select the DSN for the database you want, then complete the options.
  7. Complete the User Name and Password boxes.
  8. 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.

  9. 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.

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 Live view (View > Live View) 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.

Edit or delete a database connection

When you create a database connection, Dreamweaver stores the connection information in an include file in the Connections subfolder in the site’s local root folder. You can edit or delete the connection information in the file manually or as follows.

Edit a connection

  1. Open an ASP page in Dreamweaver and then open the Databases panel (Window > Databases).
  2. Right-click (Windows) or Control‑click (Macintosh) the connection and select Edit Connection from the menu.
  3. Edit the connection information and click OK.

    Dreamweaver updates the include file, which updates all the pages in the site that use the connection.

Delete a connection

  1. Open an ASP page in Dreamweaver and then open the Databases panel (Window > Databases).
  2. Right-click (Windows) or Control‑click (Macintosh) the connection and select Delete Connection from the menu.
  3. In the dialog box that appears, confirm that you want to delete the connection.

    Note:

    To avoid receiving errors after deleting a connection, update every recordset that uses the old connection by double-clicking the name of the recordset in the Bindings panel and choosing a new connection.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy