Visual FoxPro 9: Made Simple

Cover Page Title Page Copyright Page Dedication Page About the Author Preface Contents at a Glance 1. Introduction to Visual FoxPro 1.1 Database and Visual FoxPro 1.2 Getting Started with Visual FoxPro Visual FoxPro Main Menu Toolbars Command Window 1.3 Visual FoxPro Help System Context-Sensitive Help 1.4 What is a Visual FoxPro Table? 2. Tables 2.1 Creating a Table 2.2 Entering Records in a Table 2.3 Listing Contents of a Table Record Pointer 2.4 Closing a Table 2.5 Using the Table Wizard 2.6 Searching for Specific Records with LIST Using a Filter on a Table 2.7 Defining the Default Drive and Directory 2.8 Visual FoxPro Field Types Character/Varchar Character (Binary)/Varchar (Binary)/Varbinary Numeric/Float/Double Integer/Integer (AutoInc) Currency Date DateTime Logical Memo/Memo (Binary)/Blob General Field Naming Convention 2.9 Performing Calculations with LIST/DISPLAY 2.10 Quiting Visual FoxPro 3. Locating and Editing Data 3.1 Searching for Data with LOCATE Specifying Multiple Conditions Rushmore Query Optimization Searching for Data within a Field 3.2 Editing Data in a Record with EDIT or CHANGE 3.3 Editing Multiple Records with BROWSE Changing Field Captions More Options with BROWSE 3.4 Moving Between Records 3.5 Replacing Field Contents with REPLACE 3.6 Deleting Unwanted Records with DELETE 3.7 Recalling Deleted Records with RECALL 3.8 Removing Records Permanently with PACK Emptying a Table with ZAP 4. Modify Structure, Memo & General Fields and File Utilities 4.1 Modifying Structure of a Table 4.2 Using the Memo Field to Store a Long Text 4.3 Entering Data in a Memo Field Append and Edit Data in a Memo Field 4.4 Using the General Field Inserting Clipboard Contents Inserting OLE Objects APPEND GENERAL and MODIFY GENERAL Commands 4.5 File Utilities in Visual FoxPro Displaying List of Files COPY FILE RENAME ERASE/DELETE FILE Executing External Programs with RUN/! 5. Memory Variables, Date, Time and Mathematical Functions 5.1 What is a Memory Variable? 5.2 Creating and Using Memory Variables Creating Memory Variables with STORE Using = to Create Memory Variables Creating an Empty Character Variable with SPACE() Displaying Memory Variables 5.3 Creating Array Memory Variables Referring to Array Elements The ALEN() Function 5.4 Using Arrays Transferring Data from a Table to Array (SCATTER) Transferring Data from Array to Table (GATHER) Field or Memory Variable? Copying Multiple Records to/from Array 5.5 Saving and Restoring Memory Variables Clearing Memory Variables Restoring Memory Variables 5.6 Displaying/Printing Information with ?/?? Using ??? to Control a Printer 5.7 Time and Date Functions TIME() Displaying System Clock on the Screen SECONDS() and SYS(2) DATE() DAY(), MONTH(), CMONTH() and YEAR() DATETIME(), HOUR(), MINUTE() and SEC() DTOC(), DTOS(), CTOD() and CTOT() CDOW() and DOW() Date Arithmetic 5.8 Converting Numeric Data to Characters with STR() 5.9 Arithmetic Operations 5.10 Character Operators + (Concatenation) - (Concatenation) $ (Comparison) 5.11 Mathematical Functions INT(), FLOOR(), CEILING(), ABS() and SIGN() ROUND() MIN() and MAX() RAND() SQRT(), LOG(), LOG10(), MOD() and EXP() Future Value of Investment — FV() Present Value of Investment — PV() 5.12 Mathematical Commands SUM AVERAGE and COUNT Using the Multipurpose CALCULATE Command 6. Sorting and Indexing Tables 6.1 What is Sorting? 6.2 Sorting Information with SORT Disadvantages of Using SORT 6.3 What is Indexing? Selecting the Controlling Index Tag More Index Functions Creating Index in Descending Order Including Selected Records in the Index Removing Duplicate Records from the Index Candidate and Primary Indexes Binary Index Deleting Tags from a Structural Index File 6.4 Corrupted Index File 6.5 Creating Index Tags while Defining Structure 6.6 Finding Information with SEEK, SEEK() and LOOKUP() LOOKUP() INDEXSEEK() 6.7 Using SQL SELECT to Retrieve the Sorted Data Using Functions in WHERE and Rushmore Optimization Creating Modifiable Cursor Creating a Modifiable Cursor in Visual FoxPro 6 7. Programming with Visual FoxPro 7.1 What is a Program (Command) File? Creating a Program File Running a Program (Command) File 7.2 Using DO WHILE – ENDDO Explanation of the MAIL.prg Program 7.3 Editing a Program File Adding Comments to Executable Commands Continuing a Command in Next Line More Trim Functions Options with SKIP 7.4 Making Decision with IF – ENDIF IIF() — The Compact IF Function Nested IF – ENDIF 7.5 Using SCAN – ENDSCAN 7.6 Using FOR – ENDFOR and FOR EACH – ENDFOR 7.7 Controlling Looping Structures with LOOP & EXIT 7.8 Using Multiple Options with DO CASE – ENDCASE The ICASE() Function 7.9 Concept of the Database Creating a Database 7.10 Opening Multiple Tables What is an Alias? Selecting a Work Area using Alias Is that Alias in Use? Creating Empty Table with COPY STRUCTURE Appending Records from Another File (APPEND FROM) Copying Records to Other Files (COPY TO) 7.11 Linked Tables Linking Tables with SET RELATION Creating a One-To-Many Relation with SET SKIP Using SQL SELECT to Retrieve Linked Information 7.12 Defining Function and Other Keys 7.13 Configuring Environment Settings Using the Direct SET commands Using the Options Dialog Box Using a Configuration File 7.14 Displaying a Message 8. Useful Functions, Commands and Error Handling 8.1 Character Functions ISUPPER(), ISLOWER(), ISALPHA() and ISDIGIT() UPPER(), LOWER() and PROPER() LEFT(), RIGHT(), SUBSTR() and STREXTRACT() PADL(), PADR() and PADC() AT(), ATC(), RAT() and OCCURS() INLIST() and BETWEEN() CHRTRAN(), STRTRAN(), STUFF(), STRTOFILE() and FILETOSTR() TRANSFORM() VARTYPE() and TYPE() 8.2 File Manipulation Functions DBF(), FCOUNT(), FIELD(), FSIZE() and AFIELDS() FILE(), DIRECTORY() and ADIR() Manipulating File Name 8.3 Procedures and User-Defined Functions User Defined Function (UDF). Executing Commands from Other Program Files 8.4 Scope and Visibility of Variables Variables Naming Conventions 8.5 Using Expressions with Commands 8.6 Handling Errors Errors in Programs Syntax Errors Variable Not Found Data Type Mismatch or Operator/Operand Type Mismatch File is in Use File Access is Denied Beginning of File Encountered/End of file Encountered File ‘xyz.prg’ does not Exist Numeric Overflow 8.7 Writing Error-Free Programs Comments, Whitespace and Indentation Declare Variables and Follow Naming Conventions Don’t Modify the Working Environment in Procedure/UDF Avoid Multiple RETURNs Employ DO CASE-ENDCASE in lieu of Nested IF-ELSE-ENDIF Unmatched Loops Use ALIAS with Commands and Functions Proper Parameters Passing Use TRY-CATCH-ENDTRY to Catch Errors 8.8 Errors Debugging Commands and Techniques Display Intermediate Results Visual FoxPro Debugger Settings Conditional Breakpoints Using the Debugger Output Window Using Coverage Profiler Using ON ERROR, ERROR(), MESSAGE() and AERROR() to Analyse Errors 9. Creating Forms 9.1 What is a Form? 9.2 Creating a Form Adding Controls to a Form Properties, Events and Methods (PEM) 9.3 Running a Form Adding Code to Events Using Init() and Valid() Events 9.4 Defining Data for the Form Bound and Unbound Controls 9.5 Formatting Controls on the Form The Layout Toolbar Spacing Controls Tab Order of Controls 9.6 More Features of Forms Modeless and Modal Forms Running Multiple Instances of a Form What is a Private Data Session? Using Builders 9.7 Creating the Worker Form Adding Navigation Buttons 9.8 Adding Custom Properties and Methods to a Form Access and Assign Methods for a Property 10. Using Form Controls and Creating Visual Classes 10.1 Check Box 10.2 Option Group 10.3 Spinner 10.4 List Box Defining Data for the List Box 10.5 Combo Box Combo Box Styles Using Combo Boxes 10.6 Grid Putting a Check Box in a Grid Column Controlling Data in a Grid Using Grid Events and Properties Sorting Grid by Clicking Header 10.7 What is Object-Oriented Programming (OOP)? Class Object Subclass Why Subclass? Subclassing Base Classes 10.8 Creating More Classes 10.9 Experiencing Inheritance Breaking Inheritance Adding to the Inherited Code 11. Creating Reports and Label 11.1 Features of the Visual FoxPro Report 11.2 Creating a Report Format File 11.3 Creating a Quick Report Previewing Report 11.4 Generating a Report Printing a Sorted Report 11.5 Creating a Custom Report Defining the Page Header Defining Report Detail Defining Page Footer 11.6 Grouping of Data in a Report Defining Group Header and Footer Using Subgroups in a Report 11.7 Other Features for Report Title and Summary Bands Suppressing Page Headers on a Multi-Page Report Suppressing Detail Lines Changing Field Widths Conditional Printing in Report Using Pictures in a Report Using Variables in a Report Formatting Fields Defining Page Setup 11.8 Creating Reports Using Linked Tables Using Data Environment 11.9 Using Multiple Detail Bands 11.10 Extending the Visual FoxPro 9 Reporting System 11.11 Designing Mailing Labels Creating Custom Labels 12. Creating Windows and Menus 12.1 User-Defined Windows Defining a Window Defining Additional Features of a Window Controlling Windows Saving and Restoring Windows 12.2 Visual FoxPro Menu Designer Defining Options for the Menu Inserting Visual FoxPro System Menu Bar in a Manu Switching Between the Menu Bar and Submenus Grouping Items in a Submenu Controlling Menu Previewing Menu Defining Setup and Cleanup Code 12.3 Generating and Running a Menu 12.4 Creating a Quick Menu 12.5 Creating Shortcut Menus 13. Validation Rules, Triggers and Referential Integrity 13.1 Format, Input Mask, Caption, Field Comment and Table Comment Caption Input Mask Format Field Comment 13.2 Default Field Value 13.3 Field Validation What are Stored-Procedures? Creating Stored Procedures 13.4 Record Validation 13.5 Defining Primary Index Deleted Records are Included in Primary Index Foreign Key 13.6 Triggers 13.7 Referential Integrity Defining Rules for Referential Integrity Generating Referential Integrity Code 14. Query Designer and SQL-SELECT 14.1 What is a Query? 14.2 Using the Query Designer Joining Tables Filtering Query Result Ordering Results Grouping Results Options on the Miscellaneous Tab Defining the Query Destination Modifying SQL Code in the View Window 14.3 Running the Query 14.4 Using SQL-SELECT to Query Data Querying Data from a Single Table Using Expressions and Constants in Selection Defining Output Destination Filtering Data with WHERE Using Functions in the WHERE Clause Comparing Strings and SET ANSI Using LIKE for Pattern Search Using Multiple Tables Using NVL() to Remove NULL Values Grouping Data with GRUOP BY Using HAVING to Filter GROUP BY Ordering Results with ORDER BY Using DISTINCT to Exclude Duplicate Data Restricting Records with TOP Using UNION to Combine SELECT Results 14.5 Using Subqueries Nested Subqueries 14.6 Creating a Self-Join 14.7 Improving Query Performance Retrieve What You Need Memory Affects Performance Forcing Join Sequence 15. Using Views and Accessing Remote Data 15.1 What is a View? 15.2 Creating Local Views Updating Tables through a View View SQL Query Using a View Creating Parametrised View Using the CREATE VIEW Command to Create a View File 15.3 What is Remote Data? Should I Use Remote or Local Data? How do I Access Remote Data? 15.4 Using ODBC to Access Remote Data 15.5 Creating Remote Views Using a Remote View Using a Connection 15.6 Using SQL Pass-Through Functions 15.7 What is OLEDB? Should I Use ODBC or OLDDB? 15.8 Introduction to the CursorAdapter Class Creating a CursorAdapter Class Using a CursorAdapter Class to Access Data Modifying the Source Data through the CursorAdapter Cursor What’s Next? 16. Organising an Application through Project Manager 16.1 What is Project Manager? Organising Project Files 16.2 Creating a Project 16.3 Managing a Project with the Project Manager Modifying and Renaming Files Running Files Removing/Deleting Files The Main Program File Including and Excluding Files Dragging and Dropping Class Elements The Project Information Dialog Box Closing, Collapsing and Docking the Project Manager 16.4 Building .Exe, .App and .DLL Files 16.5 Distributing Files for an Application 16.6 Distributing an Application using InstallShield Express Defining Application Properties Installation Architecture Adding Files to the Project Defining Application Shortcuts Installation Interview Defining Visual FoxPro Runtime Library Files Building the Release Distributing the Setup Program 17. Data Buffering and Transactions 17.1 Concept of Multi-User Development Opening Tables in Exclusive or Shared Mode 17.2 Locking Data 17.3 What is Buffering? Commands to Define Buffer Mode What Buffer Mode should I Use? 17.4 Using Tables in Buffered Mode Updating Data in a Buffered Table Controlling Data Buffering on a Form 17.5 Handling Update Conflict Resolving Update Conflict 17.6 Using Transactions Requirements for Using Transactions Nested Transactions 18. Using Object-Oriented Programming with Visual FoxPro 18.1 Defining a Class with DEFINE CLASS Using ADD OBJECT with DEFINE CLASS Specifying Multiple Properties with WITH-ENDWITH Finding Ancestor Class Names with ACLASS() Finding Properties, Methods and Events for an Object/Class 18.2 Creating a Custom Class Calling Object Methods 18.3 Protecting and Hiding Properties and Methods Hiding Properties and Methods Adding Property at Run Time 18.4 Managing Classes Managing Class Libraries through Commands 18.5 The Class Browser Renaming a Class Removing a Class Copying a Class to another Class Library File Modifying a Class Viewing Class Code Creating a New Class Filtering Class List Creating an Object by Dropping a Class 18.6 The _SCREEN System Variable 19. Advanced Object-Oriented Programming 19.1 Creating Container Classes Creating the Navigation Controls Container Class 19.2 Creating Combination Controls Modifying the cntCustID Class Creating the GetFileName Control The ReportDestination Control 19.3 Creating and Using a Form Class Creating a Form Class Visually Using a Form Class 19.4 Creating and Using Toolbars Activating a Toolbar 19.5 Wrapper Class 19.6 Business Class 19.7 What is a Framework? 20. Visual FoxPro Development Productivity Tools 20.1 IntelliSense Customising IntelliSense Disabling and Restricting IntelliSense 20.2 Foundation Classes 20.3 Component Gallery Using Component Gallery to Create Components on a Form Adding Your Components to the Component Gallery Creating a New Catalog 20.4 Code References Replacing Text Printing/Exporting Search Results 20.5 Coverage Profiler 20.6 Bookmarks and Task List Shortcuts Setting Bookmarks Setting Task List Shortcuts 20.7 Toolbox 21. Interfacing Visual FoxPro with Other Software Package 21.1 Exporting Data from Visual FoxPro Tables 21.2 Importing Data into Visual FoxPro Tables Using Import Wizard to Import Data 21.3 Interfacing with Word Mail Merge Using Visual FoxPro Mail Merge Wizard Using Visual FoxPro Data without using the Mail Merge Wizard Creating a Data Source Using Visual FoxPro Tables Directly with Mail Merge 21.4 Excel Automation Recording Macros to Get Code for Automation Excel Constants Opening a Workbook and Manipulating Sheets Saving a Workbook Named Parameters What’s Next? 21.5 Word Automation 22. Miscellaneous Commands, Functions and Tips 22.1 Setting Configuration Options Using Configuration File Using a Startup File Defining Settings for an Application 22.2 Key Manipulation Type-Ahead Buffer INKEY() LASTKEY() and CHRSAW() ON ESCAPE ON KEY LABEL 22.3 SET EXACT OFF or ON? SET ANSI OFF or ON? 22.4 Sending Emails Using Outlook to Send Email Using Blat to Send Email Using CDO Mail 22.5 Using the Timer Control 22.6 Low Level File Operations FOPEN() and FCLOSE() FCREATE() FGETS() and FREAD() FPUTS() and FWRITE() 22.7 Miscellaneous and Tips Returning a Value from a Form Finding Duplicate Records in a Table Using CAST() to Convert Data REPLACE and EOF() BLANK and ISBLANK() Clearing Printer Environment from a Report Passing a Long String command to SQLEXEC() Problem Using NVL() in SQL SELECT Storing Pictures INSERT INTO… SELECT FROM Using AUSED() to Find Alias of all Open Tables Using a Progress Bar Is the Date Valid? How to Import Data from all Worksheets of a Workbook? Clearing Command Window 23. Creating Help File 23.1 What is HTML Help? 23.2 Using the HTML Help Workshop Creating a Table of Contents Compiling the Help System Creating an Index Defining Help Options Adding Search and Favorites Tabs to the Help File 23.3 Using a Help File in a Visual FoxPro Application Creating Context-Sensitive Help Using a Map File in a Help Project Calling Context Sensitive Help Topic Implementing What’s This? Help Distributing the Compiled Help System Problem with Opening CHM Help Files from a Network Drive Index