Microsoft KB Archive/130122

= How to add the SQL SELECT to the data environment of the REPORT FORM in FoxPro 3.0 and later versions =

Article ID: 130122

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q130122



SUMMARY
In FoxPro version 2.6x, many developers used the SQL SELECT statement to create a cursor before running a report. This can be done automatically in Visual FoxPro 3.0 and later versions by adding a SELECT statement to the data environment of the REPORT FORM. This article explains how.



MORE INFORMATION
One way to automatically query a table each time you run a report is to create a view within a database container (.DBC file). Then, add the view to the data environment section of the report.

Step-by-Step Procedure
 If you have an existing .DBC file, use the OPEN DATABASE command to open it. If you do not have an existing .DBC, use the CREATE DATABASE command to create it. You can add a view to the .DBC file interactively by using the view designer, or programatically by using the CREATE SQL VIEW command.

 To add a view interactively:

 If the database designer is not open on the screen, type MODIFY DATABASE in the Command window, and press ENTER. Click the right mouse button, and choose New Local view if you want to have your view based on a FoxPro table. Choose New Remote View if you want to have your view based on an ODBC datasource. Use the View Wizard or the View Designer to select fields and specify selection criteria for your records.</ol> </li>  To add a view programatically issue the CREATE SQL command. For example the following command includes all customers who have placed orders: CREATE SQL VIEW myview AS SELECT * FROM customer WHERE ; customer.cust_id IN (SELECT orders.cust_id from orders) Regardless of which method you use to create the view, you should see the view within the database designer. The view is stored as part of the .DBC file. It is not stored as a separate file on disk. </li></ol> </li> Create a new report. From the View menu, choose Data Environment.</li> Click the Data Environment window to make it active, and click the right mouse button.</li> Choose Add from the menu. On the right side of the Add Table or View dialog, choose Views (instead of tables). Select the desired view from the list of views that appears.</li> Add any needed fields to the report, and run it. The records included in the report will be based on the selection criteria specified in your view.</li></ol>

Keywords: kbcode kbhowto kbprint kbreportwriter KB130122

-

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

© Microsoft Corporation. All rights reserved.