Building a Connection to a Database

---

When you use a database to store collected data, you must provide parameters about the database to Dreamweaver (and the browser, via the Web and application servers). That information minimally includes the type and location of the database. The type specifies the database driver used (for application server — database communication) and the location is the specific path to the database. The information is stored as a connection script. Dreamweaver builds a special Connections folder and saves the information in a page with the appropriate extension, for example. The server technology you choose affects the type of connection you build.

• DSN connection (Data Source Name) — A DSN is a single-word identifier or name containing all the parameters for connecting and communicating with a database. Parameters can include server name, path to the database, ODBC (Open Database Connectivity) Driver, and possibly, a user name and password. A DSN is an alias that requires a registry "lookup" to recover the information for the connection string.

• A Custom Connection String (DSN-less) Connection — Used to create an ODBC or OLE DB connection between the application server and the database. Provides the same information as might be given in a DSN but doesn't require lookup and is often considered more server "friendly" using fewer resources. Dreamweaver includes a reference to the connection page. When the page is processed, the include is processed first, before the rest of the logic for the current page. You must use a connection string when using an OLE DB provider or when an ODBC driver is not installed on a Windows system.

The database drivers you use may depend upon the database. For an ASP site, you would likely use the database types listed in Table 46-2.

Table 46-2: ASP Database Options

Database	Database Driver
Microsoft Access	Microsoft Access Driver (ODBC)
Microsoft SQL Server	Microsoft SQL Server Driver (ODBC)
Microsoft SQL SErver Provider (OLE DB)
Oracle	Microsoft Oracle Driver (ODBC)
Oracle Provider for OLE DB

Consult your ISP or hosting company for information about the required type of connection you'll need to make if you use a remote hosting solution. Many companies will create a DSN for a database that you upload to your virtual server based on the name you request and the path you provide to the database. Some companies may require a DSN-less connection and provide a specific driver and path to use when building a custom connection string. Of course, your hosting solution must allow using a database and support the server technology you choose. For example, you might not be able to use ASP if your site is hosted on a Unix-based server.

If you are using a local application server, you should first ensure that your system has the proper driver for your database. Consult your computer's help files for installing a database driver. To access a list of ODBC drivers, use one of the following methods:

• Windows 95/98/NT — Choose Start ® Settings ® Control Panel. Double-click the ODBC Data Sources icon, which could also be called ODBC or 32bit ODBC. Click the Drivers tab to see a list of drivers installed on the system.

• Windows 2000 — Choose Start ® Settings ® Control Panel ® Administrative Tools ® Data Sources. Click the Drivers tab.

• Windows XP — Choose Start ® Control Panel ® Performance and Maintenance ® Administrative Tools ® Data Sources [ODBC]. Click the Drivers tab.

  Note 

  If the required driver is not installed, you may need to download and install the Microsoft Data Access Components (MDAC) 2.5 and 2.6 packages. Download them for free from Microsoft's Web site. You'll need to install MDAC 2.5 before installing MDAC 2.6. If MDAC does not have an ODBC driver for your database, see your database vendor.

Adding a DSN connection in Dreamweaver

A DSN connection requires the creation of the registry lookup and varies depending upon your system. Consult your system's help files for creating a system DSN for local testing. Consult with your host provider to have your DSN set up. If developing locally and publishing to a remote location, be sure that the DSN setup in both locations uses the same name.

To add a DSN Connection in Dreamweaver, follow these steps:

1. Open a dynamic page in your site.

2. Open the Application panel group and click the Databases tab.

3. Click the Add (+) button and select Data Source Name (DSN).

4. In the Data Source Name dialog box (Figure 46-4) type the DSN name or choose from DSNs listed in the menu below the name field. Dreamweaver lists all system DSNs on your local computer or network (Figure 46-4). If you are online and using a remote testing server, any DSN setup on that system lists in this menu.

   
   Figure 46-4: System DSNs are available to all users on the local computer or network.

5. Add a user name and password if required and select whether the DSN is for a local or remote testing server.

Adding a custom connection string in Dreamweaver

Your connection string will vary based on the type and location of your database and Web server. In the following steps, an Access 2000 database and a local testing server are assumed:

1. Create a blank or open an existing asp page in your site.

2. Open the Applications panel group and click the Databases tab.

3. Click the Add (+) button and choose the Custom Connection String option.

4. Name your connection.

   Tip 

   Typically, developers start connection string names with conn, as in connHabitats or connSitename. That way, when you see connSitename anywhere in your code, you may be sure that it refers to the connection string.

5. Add the proper driver and database path to the Connection String field, as shown in Figure 46-5. For this example you would add (all on one line):

   
   Figure 46-5: The Connection String dialog box

   Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Inetpub\wwwroot\  siteroot\database\dbname.mdb

6. Use Driver on this Machine because this example assumes a local testing server.

   A remote testing server setup could use a similar string, except that the path could be quite different. For example, a virtual remote server path might look something like:

   Driver={Microsoft Access Driver  (*.mdb)};Dbq=d:\n2dreamweaver.com\wwwroot\siteroot\database\ dbname.mdb

7. Test the connection by clicking the Test button. A local testing server does not require being online but a remote one would. If you receive an error message, copy or write down the message before you close the pop-up window. Check your string carefully because the syntax used is critical. If that still doesn't help, consult the Dreamweaver Application Search Help feature (Help ® Using Dreamweaver) or the Dreamweaver support section of the Macromedia Web site, using the error number as a keyword in the search.

8. If the test is successful, click OK.

Dreamweaver builds a Connections folder at the root level and stores the string information in an ASP page within, using the Connection name as the filename. In each page where you need access to your database, you will query the database via the connection, using a recordset to "bind" data to your page. Figure 46-6 shows the newly created Connections folder at the root level in the Site panel and displays the now available subscribers table from the  admin.mdb database that is accessed via the new connection.

Figure 46-6: With your connection in place, you can view fields for the tables available in your database.

On the CD-ROM 

See the exercise add_connection.pdf in the chapter46_exercise files on the CD to create a custom connection string in the Administrative Intranet project site. A complete list of connection strings used for the most popular configurations of databases and drivers may be seen in connection_strings.pdf.

---