KB190529 - ACC97: ASP Page Based on a Parameter Query May Not Refresh

Article ID: 190529

Article Last Modified on 1/23/2007

APPLIES TO

Microsoft Access 97 Standard Edition

This article was previously published under Q190529

Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

An Active Server Page (ASP) with parameters may not return the expected results after it is run the first time.

CAUSE

You may experience this problem when the following conditions are true:

You create a form based on a parameter query.

-and-
You save a form, from Microsoft Access, as an ASP file.

-and-
You run the query a second time and use different parameters.

-and-
You run the query before the Web server's "session timeout" is over.

RESOLUTION

NOTE: This section contains information about editing ASP files, and assumes that you are familiar with editing ASP files. Microsoft Access Product Support professionals do not support customization of any HTML, HTX, IDC, or ASP files.

You can correct this behavior by clearing the session object through script. Once you know the session name, add the script below to the .html file that prompts you for the parameter.

Determine the Session Name

Use Notepad or another text editor to open the <filename>.asp or <filename>alx.asp file created by Microsoft Access.

Look for the line:

      Set rs = Session("<SessionName>")

The name that appears between the quotation marks is the session name.

Edit the HTML file

Use Notepad or another text editor to open the <filename>.html file created by Microsoft Access.

Type the five lines below after the <BODY> tag.

      <%
      If IsObject(Session("<SessionName>")) Then
         session.abandon
      End if
      %>

Save the file as the same Qry<filename>.asp. Note that the file will need to have a .asp extension instead of a .html extension.

MORE INFORMATION

A session object is created on the ASP page to prevent it from having to requery the data every time it is run. The Web server will clear the session object after a certain period. If this session object is not cleared, the ASP page uses the original SQL string and recordset, even if the parameters have changed. The script created by Microsoft Access checks for this session object. If the session object exists, the script never gets to the point where the parameters are set in the SQL string and the recordset is created.

Steps to Reproduce Behavior

Create the ASP Page
-------------------

On your Web Server, create a System DSN (using the Microsoft Access ODBC driver) based on the Northwind sample database, Northwind.mdb, and name the data source NWind97.
Open the sample database Northwind.mdb.

Create the following new query based on the Customers table:

       Query: qryParamCust
       ----------------------------------
       Type: Select Query

       Field: CustomerID
          Table: Customers
          Criteria: Like [Enter ID] & "*"
       Field: CompanyName
          Table: Customers
       Field: ContactName
          Table: Customers
       Field: Phone
          Table: Customers

Create the following new form:

       Form: frmParamCust
       ---------------------------------
       Caption: Customers
       RecordSource: qryParamCust

       Text box:
          Name: CustomerID
          ControlSource: CustomerID
       Text box:
          Name: CompanyName
          ControlSource: CompanyName
       Text box:
          Name: ContactName
          ControlSource: ContactName
       Text box:
          Name: Phone
          ControlSource: Phone

On the File menu, click Save As HTML. When the "Publish to the Web Wizard" appears, click Next on the opening screen.
On the "What do you want to publish?" screen, click the Forms tab, and then click frmParamCust. Click Next.
On the "What HTML document, if any, do you want to use as a default template?" screen, click Next.
On the "What default format type do you want to create?" screen, click Dynamic ASP, and then click Next.
On the "What are, or will be, the settings for the Internet database?" screen, enter NWind97 in the Data Source Name box, and then click Next.

NOTE: You will have to have an ODBC Data Source Name already created on your web server that points to the Northwind database named NWind97.
On the "Where do you want to publish to?" screen, select a folder on your Web server where you have Execute permission, for example InetPub\Scripts or Webshare\Scripts, and then click Finish. On the Enter Parameter dialog box, click OK. Close the query. The "Publish to the Web" Wizard creates three files, frmParamCust_1.asp, frmParamCust_1alx.asp, and frmParamCust_1.HTML.
Use Notepad or another text editor to open the frmParamCust_1.asp file. You need to change the SQL statement so that it will use the appropriate sequence of parameter and wildcard characters. Change the SQL statement so that it looks as follows:
```
        sql = "SELECT Customers.CustomerID, Customers.CompanyName,
        Customers.ContactName, Customers.Phone FROM Customers WHERE
        (((Customers.CustomerID) Like '" & Request.QueryString
        ("[Enter ID]") & "%'))"
						
```
Save the changes and close the file.

For more information about using wildcards in an ASP page, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: 162977 ACC97: ASP Query Cannot Be Used with the LIKE Predicate

Test the ASP Page
-----------------

Start Microsoft Internet Explorer or another Web browser program. Type the Uniform Resource Locator (URL) in the address box of your Web browser to view frmParamCust_1.HTML. For example, if you saved your IDC files in a folder called Test in the wwwroot folder of your Web Server, type:

http://<servername>/test/frmParamCust_1.HTML

Note that the URL depends upon where your files are located on the Web server.
Type S, and then click Run Query.

Note that only customers whose CustomerID begins with the letter S will show in the form.
Type the same URL you entered in step 1.
Type B, and then click Run Query.

Note that only customers whose CustomerID begins with the letter S will show in the form.

Additional query words: prb

Keywords: kbprb KB190529