Microsoft KB Archive/190529

= 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 .asp or alx.asp file created by Microsoft Access.  Look for the line:

     Set rs = Session("") 

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 .html file created by Microsoft Access.  Type the five lines below after the <BODY> tag.

<pre class="fixed_text">     <% If IsObject(Session("<SessionName>")) Then session.abandon End if     %> </li> Save the file as the same Qry .asp. Note that the file will need to have a .asp extension instead of a .html extension.</li></ol>

<div class="moreinformation_section">

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.</li> Open the sample database Northwind.mdb.</li>  Create the following new query based on the Customers table:

<pre class="fixed_text">      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 </li>  Create the following new form:

<pre class="fixed_text">      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 </li> On the File menu, click Save As HTML. When the "Publish to the Web Wizard" appears, click Next on the opening screen.</li> On the "What do you want to publish?" screen, click the Forms tab, and then click frmParamCust. Click Next.</li> On the "What HTML document, if any, do you want to use as a default template?" screen, click Next.</li> On the "What default format type do you want to create?" screen, click Dynamic ASP, and then click Next.</li> 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.</li> 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.</li>  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:

<pre class="fixed_text">       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. </li></ol>

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://%3Cservername%3E/test/frmParamCust_1.HTML

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

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

Note that only customers whose CustomerID begins with the letter S will show in the form.</li></ol>

Additional query words: prb

Keywords: kbprb KB190529

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.