Home

ASP ADVANCED

This section provides a step-by-step guide to using the ADO in your Active Server Pages.  First you learn how to configure your server to use the ADO.  Then the next section presents a basic example of how to use the ADO to store and retrieve data from a database.  Finally, in case you encounter problems, a troubleshooting section is provided.

Configuring Your Server to Use the ActiveX Data Objects

This book assumes that you're using the ADO with Microsoft SQL Server.  Before you continue, Microsoft SQL Server must be installed on either the same machine as your Web server or a machine that's located on the same network as your Web server.  See Chapter "Installing and Using SQL Server," for details on installing and configuring Microsoft SQI, Server.

Before you can use the ADO, you must create a data source.  A data source contains information about how to connect to a data provider.  In this case, you'll use the data source to connect to Microsoft SQL Server.  There are three types of data sources: You can create a user data source, a system data source, or a file data source.  When creating a data source to use with a Web server, you should create a file data source.  The advantage of creating a file data source is that the connection information is stored in an actual file.  More than one user can access this file.  Also, if you need to transfer your Web application from one Web server to another, you can simply transfer this file.

To create a new file data source, follow these steps:

  1. Open the Windows NT Server Control Panel (choose Start I Settings I Control Panel).

  2. Click the icon labeled ODBC.

  3. Click the File DSN tab.

  4. Click the Add button.  The Create New Data Source dialog box appears.

  5. In the dialog box, select the SQL Server driver and click the Next button.  The Create a New Data Source dialog box opens.

  6. Enter a name for your new file data source.  For example, enter MyData.dsn. Click Next and then click Finish.  The Create a New Data Source to SQL Server Wizard will be launched.

  7. In the Description text box, enter a description such as My Data Source.  In the Server text box, enter the name of the server where Microsoft SQL Server is installed. (You can use Local if Microsoft SQL Server is installed on the same machine as your Web server.) Click Next.

  8. A series of dialog boxes asks you to configure different properties of the data source.  You should specify a default database.  Select the name of the database where your tables are located as your default database.  Leave the other options with their default values, and click Next to move through each dialog box.

  9. Finally, you'll be presented with the opportunity to test your new data source.  If the test is successful, click OK to add the new data source.

You have created a new file data source named MyData.dsn, which you can use to connect to Microsoft SQL Server.  The next section explains how to do this.

Using the ActiveX Data Objects to Store and Retrieve Data from a Database

This section provides an example of how to use the ADO to store and retrieve data from Microsoft SQL Server.  This example has a dual purpose: It illustrates the basic methods of accessing Microsoft SQL Server from an Active Server Page, and you can use the example to test your server's configuration.

The Active Server Page in Listing below inserts the text hello world! into a database table. Next, the text Hello World! is retrieved from the table and outputted to the browser.

Listing Hello World!

<HTML>
<HEAD><TITLE> ADO Example </TITLE></HEAD>
<BODY>
<%
Set MyConn=Server.CreateObject("ADODS.Connection")
MyConn.Open "FlLEDSN=d:\Program Files\Common Files\OOBC\Data Sources\MyData.dsn"
MyConn.Execute "INSERT MYTable (MYColumn) VALUES ('Hello World!')"
Set RS=MyConn.Execute("SELECT * FROM MyTable")
Response.Write(RS("MyColumn"))
MyConn.Close
%>
</BODY>
</HTML>

Before you can use this example, you need to Create a table named MyTable.  You can do this by using ISQL/w.  Launch the program, select your default database, and execute the following SQL statement:

Create Table MyTable (MyColumn VARCHAR(255))

The first line in this Active Server Page creates an instance of the connection object.  Next, the Open method of the Connection object is called to open a connection to the database.  The file data source, which you created in the previous section, is used with the open Method to create the connection. (Replace the path of the file data source in this script with the path of this file on your machine.)

Once a connection is Opened, you can execute SQL statements with the connection.  The Execute method in this script is used to execute two SQL statements.  First, a SQL INSERT statement is used to enter the string Hello World! into the database table.  Next, the SQL SELECT statement is used to retrieve this string from the database table.

If your system is configured correctly, the string Hello World! should be displayed in your browser window.  This string was inserted into the database table and retrieved once again.  If all does not go well, see the next section.

Troubleshooting the AcriveX Data Objects

If you're having difficulties accessing Microsoft SQL Server with the ADO, this section should help.  The Active Server Page described in the preceding section could fail for a variety of reasons.  Following is a list of symptoms and possible causes for a number of common problems that you may encounter:

  • Symptom: You get the error unable to create file buffer.
    Cause: The file data source is inaccurate.  You get this error if the file data source has an invalid path or invalid name, or doesn't exist.  Make sure that the path of the file data source on your machine is the same as the path used for the open method in the Active Server Page.

  • Symptom: You get the error Invalid object name 'MyTable'.
    Cause: The table MyTable doesn't exist in your database.  Create this table by using ISQL/w as described in the preceding section.
    Cause: The table MyTable isn't located in your default database.  You need to specify a default database.  Choose Start I Settings I Control Panel.  Click the ODBC icon and then click the File DSN tab.  Select the name of your file data source, click Configure, and then click Options.  You can specify the name of your default database in the resulting dialog box.

  • Symptom: You get the error The server appears to be not available.
    Cause: SQL Server isn't running.  Launch the SQL Service Manager from the Microsoft SQL Server program group.  Select MSSQLSERVER and click the green light.

  • Symptom: You get the error Login failed.
    Cause: You're not using Windows NT authentication, and an incorrect login ID or password was entered when creating the file data source.  Choose Start I Settings I Control Panel.  Click the ODBC icon and then click the File DSN tab.  Select the name of your file data source and then click Configure.  You can select Use Trusted Connection or you can enter a valid login ID and password.

  • Symptom: You get the error INSERT permission denied on object MyTable or the error SELECT permission denied on object MyTable.
    Cause: The login ID you specified when creating the file data source doesn't have adequate permissions to access the table named MyTable.  You need to change the login ID used with the file data source or you need to grant additional permissions to the user or group with this login within SQL Server.

To grant additional permissions on a table, launch the SQL Enterprise Manager from the Microsoft SQL Server program group.  Navigate to the table named MyTable and right-click it. Choose Permissions.  You can now grant permissions on the table for different users or groups.  Remember to click the Set button to finalize any changes in permissions.