Adding Records

Article
11/14/2006

When you first create a Visual FoxPro table, it is open but empty. If you try to store data to a table without first creating a record in the table, nothing happens. The first step in adding records to a new table is to add rows to store the new data.

To add records to a table

Use the INSERT – SQL Command.

The INSERT – SQL Command can be used to insert values specified with the command or to insert values from an array or variable. For example, to insert a new record into the TasTrade database customer table, you could issue the following command:

INSERT INTO customer (cust_id, company, contact) ;
   VALUES ("SMI007", "Smith's Delicatessen", "Sarah Smith")

The INSERT – SQL Command is useful with remote data, as it uses ANSI-compliant SQL syntax.

Adding New Blank Records to a Table

To quickly add new records to a table, you can place the Browse or Edit windows into Append mode. In Append mode, a blank set of fields appears at the bottom of the file, so you can fill them in to create a new record.

To add a new blank record

From the View menu, select Append Mode.

Fill in the fields in the new record, tabbing from field to field. A new record is created at the bottom of the file as you complete each record.

You can also use the APPEND BLANK command followed by the REPLACE command to add a blank record to a table and then store data in a field. The APPEND BLANK appends a new, blank record to a table. The REPLACE command replaces the current value of a field, even an empty field, with a new value.

The REPLACE Command requires:

An open table.
An existing record.
The name of the field in which to store the value.
A value for each field that is valid for the field's data type.

The following example uses the APPEND Command to create one record in which you can store data using the REPLACE command:

APPEND BLANK                     && record now available
REPLACE lastname WITH "SMITH"   && store character value to the field

You can use the UPDATE - SQL command instead of the REPLACE command to update records in a table.

Adding Records in Browse Mode

If you want to add a new record while viewing a table in browse mode, you can choose Append Record from the Table menu. Conversely, if you want to prevent users from being able to append a new record while in browse mode, you can use the NOAPPEND clause of the BROWSE Command.

Appending Records from Another Table

Another way to store data in records is to copy them from other tables or files. For example, you can append records from another table or file.

To append records from another file

Use the APPEND FROM Command.

-or-
Use the IMPORT Command.

Records can accept data directly, as in the previous example, where the INSERT command specified the text to be inserted into specific fields in the customer table, as well as from constants, variables, arrays, objects, and other data sources. For more information, see Importing and Exporting Data.

Entering Data in a Table

You can enter data in a table interactively, through a Browse window, or programmatically, with the REPLACE Command or UPDATE - SQL Command. When you use the REPLACE or UPDATE - SQL in a multiuser application, you can turn on record or table buffering, which enables you to edit data without locking the record until you want to commit changes. For more information, see Programming for Shared Access.

Editing Records in a Table

You can display and edit existing records in a table through the interface or programmatically.

To display records for editing

Use the EDIT Command.

-or-
Use the CHANGE Command.

For example, the following code displays the customer table in a Browse window in edit mode:

USE customer
EDIT

If you want to use a form to edit a record, create a text box in your form and set its DataSource property to the name of the table you want to edit. For more information about forms, see Creating Forms.

You can also use the CHANGE COMMAND and EDIT COMMAND to make changes to specific fields in a table.

Adding Graphics to a Table

You can store graphics in a Visual FoxPro table by creating a General Field Type and importing or pasting OLE objects, such as bitmaps or charts, into the field. The APPEND GENERAL COMMAND places an OLE object into a General field. The following example stores a Microsoft Excel chart file from the default Visual FoxPro directory into a General field named Chart:

APPEND GENERAL Chart FROM "CHART1.CLX" CLASS EXCELCHART

For more information about working with OLE objects in Visual FoxPro tables, see Sharing Information and Adding OLE.

Entering Null Values in Fields

You can enter a null value in a field through the language with the NULL token, or through the interface with a key combination if the field accepts null values.

To store a null value in a field

In a Browse Window or form control, press CTRL+0 (zero).

-or-
Use the NULL token.

For example, the following code replaces the existing value in the field automobile with a null value:

REPLACE automobile WITH NULL

Note Use the SET NULLDISPLAY Commandto specify the text displayed for null values.