Creating an ASP ADO data source

Microsoft ActiveX Data Objects (ADO) defines a class of database providers that include Microsoft Access, Oracle, and other ODBC databases. The ADO interface is preferred to the ODBC interface because of the availability of additional functions GoLive can use to access database schema information. In ASP, we use the term ADO Database and ADO Database Query to cover access to all databases of this type.

The data source object created is a Microsoft Data Link file, which has the suffix .udl.

The data source object tells your site how to connect to your database. For most uses, there are only four fields that are required: the driver or provider name, that database name, and, if a user identity is needed for database access rights, a user name and password.

To set up an ADO data source in your ASP site:

Note: You need to have your site set up as a dynamic site. (See Making a Web site dynamic.)

1. With the site window active, select Site Settings > Dynamic Content pane.
2. In the Data Sources area, click New.
3. Select ADO Database, and click Add Data Source.
4. Now fill in the fields.
• Data Source Name: Pick any name you want. Usually, the name of the database is a good choice. The name must be a legal Windows filename.
• Provider: Fill in the Windows data source name. This must be the name that appears in the System DSN tab of the ODBC datasources dialog (see Creating a Windows ASP Microsoft Access data source). This will be a name that you or your system administrator defined. You may need to log onto the Web server or get information from the system administrator to know this name. Do not use User or File DSN names, as these are generally not available to the Web server.
• Data Source: Enter the name of the data source. This may be the name on the Web server file system of the actual database file for a file-based source (such as Microsoft Access or Excel), or the simple name of the database for a database system that manages its own information (such as Oracle or MySQL).
• User Id: If a user identity is required to establish access rights for the database, enter the user name here.
• Use Password and Password: If a password is required, check the Use Password checkbox, and enter the password in the Password field.

Note: The password is stored in clear text in the UDL file. Be sure that HTTP Read access is not allowed to the config/datasources folder to restrict visibility of this file. (See Relocating config/datasources folder.)

• Other: Note that there are many other parameters that can be specified in a UDL file. You can enter and edit them in the Other field. If more sophisticated control of the access is required, it is recommended that you use the Microsoft UDL properties editor to set up the UDL file. This requires you to log on to the Web server machine, select the UDL file in your site, and click Properties in Windows Explorer.
5. Press the Test button to test the data source. Test will make a call to the Web server and try to connect to the database. If successful, it will return and display an XML representation of the list of tables that were discovered in the database. If an error occurs, some error information will be displayed if possible. Unfortunately, depending on the database system and driver, the content of the error messages may be very obscure.