1 / 36

Chapter 5

Chapter 5. Updating Records in a Table. Updating a table. Updating a table refers to editing (changing) records, adding new records (inserting) or deleting records. Contents in a table can be changed interactively using the BROWSE or EDIT Windows.

saston
Download Presentation

Chapter 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 5 Updating Records in a Table Foxpro Chapter 5

  2. Updating a table • Updating a table refers to editing (changing) records, adding new records (inserting) or deleting records. • Contents in a table can be changed interactively using the BROWSE or EDIT Windows. • Alternatively they can be changed using a FoxPro command. Foxpro Chapter 5

  3. Updating vs. Querying • Updating: • changing the data value within a table or the structure of the table itself. • Querying: • reading data values but not changing them. Foxpro Chapter 5

  4. Ways to update • Editing && fields of a record • Inserting && a record • Deleting && a record • Importing && a file • Exporting && a file • Summing && numeric fields Foxpro Chapter 5

  5. EDITINGRecords can be changed in the Browse/Edit Windows, orthey can be changed using theReplace command Foxpro Chapter 5

  6. Replace Several Fields USE project ... REPLACE date WITH {01/03/2010},; maxTemp WITH 22, minTemp WITH 16 Further Practice 4 Q.1 Foxpro Chapter 5

  7. Replace All Records or Certain Records • USE formPos Further Practice 1A Q.5 ... REPLACE position WITH RECNO() ALL • USE patient Further Practice 5A Q.5 ... REPLACE name WITH ‘Mr. ’+name FOR sex =‘M’ Foxpro Chapter 5

  8. Replace Contents of Memo Let remark be a field of memo type in table student USE job REPLACE remark with; ‘Urgent. Delay more than one week!”; ADDITIVE for … Further Practice 8A Q.2 Foxpro Chapter 5

  9. BLANK USE project … BLANK FIELDS minTemp, maxTemp, ; range, aveTemp ALL Further Practice 4A Q.5 Foxpro Chapter 5

  10. INSERTINGNew records can be added to a table by using the command APPEND Foxpro Chapter 5

  11. APPEND • Put an additional record at the end of the table. • Open an input window for you to key in data. • If an index is in effect, the record will immediately be moved to its appropriate location. • Usually used interactively. Foxpro Chapter 5

  12. APPEND BLANK • Put an additional record to the end of the table. • If an index is in effect, the record will immediately be moved to its appropriate location. • Usually used in programs. Foxpro Chapter 5

  13. DELETING Foxpro Chapter 5

  14. DELETE and PACK Use anyfile go 2 DELETE && The 2nd record is marked for deletion (logically deleted) PACK && The 2nd record is permanently deleted (physically deleted) Foxpro Chapter 5

  15. SET DELETE ON • SET DELETE ON DELETE ALL LIST && no records will be seen • SET DELETE OFF && default DELETE ALL LIST && all records will be shown Foxpro Chapter 5

  16. RECALL DELETE ALL && mark all record for deletion RECALL ALL && unmark all Foxpro Chapter 5

  17. DELETED() USE student Go 2 DELETE ? DELETED() && output .T. RECALL ? DELETED() && output .F. Foxpro Chapter 5

  18. DELETED() To browse only records marked for deletion: USE student BROWSE FOR DELETED() = .T. or, BROWSE FOR DELETED() Foxpro Chapter 5

  19. PACK • Physically remove all records marked for deletion. • Cannot be recovered or recalled. • Can you think of a way to recover the physically deleted records? Foxpro Chapter 5

  20. ZAP Equivalent to DELETE ALL PACK • No record is left • The table is empty but not the .DBF file • The .DBF file still holds information about the structure of the table • The .DBF file is the same as that formed by COPY STRUCTURE Foxpro Chapter 5

  21. COPY STRUCTURE Use f1 COPY STRUCTURE TO f2 USE student COPY STRUCTURE TO namelist FIELDS class, classno, name Foxpro Chapter 5

  22. IMPORTING Foxpro Chapter 5

  23. APPEND FROM a delimited file USE student APPEND FROM newstxt TYPE DELIMITED • A delimited file is an ASCII text file in which records are separated from one another by a carriage return and linefeed. Field contents are by default separated by commas. Foxpro Chapter 5

  24. APPEND FROM a SDF file USE student APPEND FROM newssdf TYPE SDF • SDF stands for System Data Format in FoxPro. An SDF file is an ASCII text file in which records have a fixed length and end with a carriage return and linefeed. Fields are not delimited. Each row of data in the SDF is filled into a record according to the width of each fields Foxpro Chapter 5

  25. APPEND FROM [a dbf file] USE complex APPEND FROM temp Further Practice 6A Q.5 Foxpro Chapter 5

  26. APPEND FROM an excel file USE student APPEND FROM newsxls TYPE XL5 With our machines, Excel files can be imported only if they are of version 5 or earlier. If the source file is in Excel97 or newer version, first save it in Excel95 version. Foxpro Chapter 5

  27. EXPORTING Foxpro Chapter 5

  28. COPY FILE CLOSE ALL COPY FILE anyfile TO newfile • anyfile must not be open. • wildcard characters are not allowed. • The filenames concerned must contain extension if there are any. • Behind the screen: FoxPro call the OS to perform a DOS copy or something similar. Foxpro Chapter 5

  29. COPY TO USE student COPY TO studback USE f2001 COPY TO f2002 FOR PROMOTED=.T. COPY TO repeater FIELDS class, classno, name FOR PROMOTED=.F. Foxpro Chapter 5

  30. Exporting to text files USE student COPY TO stcomma.txt TYPE DELIMITED COPY TO stsdf.txt TYPE SDF Foxpro Chapter 5

  31. Exporting to Excel 5 USE student COPY TO stxls.xls TYPE XL5 Foxpro Chapter 5

  32. SUMMING Foxpro Chapter 5

  33. TOTAL • The total command computes totals for numeric fields in the table currently in use. • The results will be stored in a newly created table with the same structure (by default) as the one in use. Foxpro Chapter 5

  34. TOTAL • Records should first be arranged in groups • In this case class should be grouped USE APStud TOTAL ON class FIELDS award_cnt, ; punish_cnt TO result Further Practice 7A Q.5 Foxpro Chapter 5

  35. TOTAL • For non-numeric fields, values in the first record of each group will be shown (In SQL, that of the last record is shown). • This can be quite misleading in most circumstances. • If only numeric fields and non-numeric fields with consistent value within each group are shown, the result will be more satisfactory. Foxpro Chapter 5

  36. Backing Up • Grandfather-Father-Son Concept • see p.142-143 • Done by you, not the computer • Use 3 tapes A, B and C f1 --> f2 --> f3 --> f4 A --> B --> C --> A grandfa --> father --> son data in f1 are lost as tape A is overwritten with data in f4 Foxpro Chapter 5

More Related