Microsoft KB Archive/167819

{| The information in this article applies to:
 * width="100%"|
 * Microsoft Visual InterDev, version 1.0

SUMMARY
Microsoft Visual Interdev provides a DataForm Wizard that will generate Active Server Pages that can be used to access a database back-end. These forms provide filtering capability. When these filters are used to filter large recordsets, performance may suffer. An alternative approach is to requery the server for a new recordset each time you apply a filter. This article describes how to change a data form to use this approach.

MORE INFORMATION
To begin, you will need some DataForm Wizard-generated files on which to work. Use the following steps to create these files based on the Adventure Works database file that is installed with Active Server Pages:

 Create a new web project using the Web Project Wizard. After the web project is created, use the mouse to right-click on the GLOBAL.ASA file. Choose "Add Data Connection." Choose "AdvWorks" under the "Machine Data Source" tab. Change the name of the data connection from "DataConn" to "AdvWorks," and then close the "Data Connection Properties" window. Open the "File" menu. Choose "New." Choose the Second tab, "File Wizards."</li> Highlight "Data Form Wizard."</li> Type in "Requery" in the File Name text box.</li> In the list box, "What database connection do you want to use?" select "AdvWorks."</li> Press the Next button twice to move to "Step 3 of 7" in the DataForm Wizard.</li> Add all fields from the "Customer" table by clicking on the ">>" button.</li>  Click the Next button four more times, accepting the defaults of all the remaining wizard steps. </li> Click the finish button.</li></ol>

You will now have the files: RequeryForm.asp RequeryList.asp RequeryAction.asp Make the changes outlined below to the RequeryForm.asp file and the RequeryAction.asp file to change from a filtering model to a requery model. RequeryForm.asp

Change the RequeryForm.asp file to store the Query in a session variable so that you can alter it later. Store the cmdTemp object in a session object so you can modify its CommandText property before requerying. To do all this, follow these steps:

<ol> Open RequeryForm.asp.</li> Press CTRL+F to open the search dialog.</li> Type "Set cmdTemp" in the "Find What" text box.</li> Press the "Find Next" pushbutton.</li> Press the F3 key to find the second occurrence of this string. The string should read "Set cmdTemp.ActiveConnection = AdvWorks."</li> Insert a new line on the line following the line that is now highlighted.</li>  Place the following lines of code on the newly inserted line: '**************     Set Session("cmdTemp") = cmdTemp Session("CommandText") = cmdTemp.CommandText '************** </li> Save this file and close it.</li></ol>

RequeryAction.asp You now need to change the RequeryAction.asp so that a WHERE clause is added to the SQL statement that provides your records. Follow these steps:

<ol> Open the RequeryAction.asp page.</li> <li>Press CTRL+F to open the search dialog.</li> <li>Type "All Records."</li> <li>Press the "Find Next" button.</li> <li> Six lines down you will see the line: Session("rsRequeryCustomers_AbsolutePage") = 1 </li> <li>Insert a new line after this line.</li> <li> Place the following lines of code here: '**************     Session("cmdTemp").CommandText = Session("CommandText") Session("rsRequeryCustomers_Recordset").Requery '************** </li> <li>Press CTRL+F to open the search dialog again.</li> <li>Type in "Filter the recordset," and press the "Find Next" button.</li> <li> Four lines down you will see the line: Session("rsRequeryCustomers_AbsolutePage") = 1 </li> <li>Insert a new line after this line.</li> <li> Place the following lines of code here. '**************     Session("cmdTemp").CommandText = Session("CommandText") & " WHERE " _ & strWhere Session("rsRequeryCustomers_Recordset").Requery '************** </li> <li>Save this file and close it.</li></ol>

You can now browse RequeryForm.asp in your browser. When you press the Filter button, the query will be set to its original state and a requery will be performed. After you enter filter criteria, they will be appended to the SQL query and another query will be performed.