HOWTO: Convert Filtering in DataForm Wizard to Requery Model

• 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:

1. Create a new web project using the Web Project Wizard.

2. After the web project is created, use the mouse to right-click on the GLOBAL.ASA file.

3. Choose "Add Data Connection."

4. Choose "AdvWorks" under the "Machine Data Source" tab.

5. Change the name of the data connection from "DataConn" to "AdvWorks," and then close the "Data Connection Properties" window.

6. Open the "File" menu.

7. Choose "New."

8. Choose the Second tab, "File Wizards."

9. Highlight "Data Form Wizard."

10. Type in "Requery" in the File Name text box.

11. In the list box, "What database connection do you want to use?" select "AdvWorks."

12. Press the Next button twice to move to "Step 3 of 7" in the DataForm Wizard.

13. Add all fields from the "Customer" table by clicking on the ">>" button.

14. Click the Next button four more times, accepting the defaults of all

        the remaining wizard steps.
    

15. Click the finish button.

   RequeryForm.asp
   RequeryList.asp
   RequeryAction.asp

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:

1. Open RequeryForm.asp.

2. Press CTRL+F to open the search dialog.

3. Type "Set cmdTemp" in the "Find What" text box.

4. Press the "Find Next" pushbutton.

5. Press the F3 key to find the second occurrence of this string. The string should read "Set cmdTemp.ActiveConnection = AdvWorks."

6. Insert a new line on the line following the line that is now highlighted.

7. Place the following lines of code on the newly inserted line:

         '**************
         Set Session("cmdTemp") = cmdTemp
         Session("CommandText") = cmdTemp.CommandText
         '**************
   

8. Save this file and close it.

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:

1. Open the RequeryAction.asp page.

2. Press CTRL+F to open the search dialog.

3. Type "All Records."

4. Press the "Find Next" button.

5. Six lines down you will see the line:

   Session("rsRequeryCustomers_AbsolutePage") = 1

6. Insert a new line after this line.

7. Place the following lines of code here:

         '**************
         Session("cmdTemp").CommandText = Session("CommandText")
         Session("rsRequeryCustomers_Recordset").Requery
         '**************
   

8. Press CTRL+F to open the search dialog again.

9. Type in "Filter the recordset," and press the "Find Next" button.

10. Four lines down you will see the line:

           Session("rsRequeryCustomers_AbsolutePage") = 1
    

11. Insert a new line after this line.

12. Place the following lines of code here.

          '**************
          Session("cmdTemp").CommandText = Session("CommandText") & " WHERE " _
              & strWhere
          Session("rsRequeryCustomers_Recordset").Requery
          '**************
    
    

13. Save this file and close it.

REFERENCES

For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:

   http://support.microsoft.com/support/vinterdev/

Keywords          : VIWizards kbtool kbhowto
Technology        : kbInetDev
Version           : 1.0
Platform          : WINDOWS
Issue type        : kbhowto