Chapter 5: Updating Data with Views

To extract a set of records from a table, make changes to those records, and send the updates to the source tables, you can use a view. You can create views from local tables, from other views, from tables stored on a server, or from remote data sources, such as Microsoft® SQL Server, via ODBC. You can even have Visual FoxPro send updates to the source table when you update or change the set of records in the view.

This chapter covers creating simple, single-table local and remote views. For information about creating complex views and combining local and remote data in a single view, see Chapter 6, Querying and Updating Multiple Tables.

This chapter discusses:

Creating Views

Creating queries and views are similar processes. The main difference is that views can be updatable, while queries are not. Use queries when you want to display a set of records that are read-only. Queries are a SQL - SELECT statement saved as text in a .qpr file; use views when you want to extract an updatable set of data from local or remote tables.

Queries, views, and remote views

For example, you can display, but not update, the records retrieved by a query. But when you edit the records in a view, you can have the changes sent to the source table, updating it.

Views are a feature of Visual FoxPro databases. You can only use views when their parent database is open.

Using a View Wizard

You can create local and remote views with a view wizard. To set up an updatable view using tables on an ODBC data source, use the Remote View Wizard.

To create a view with a wizard

  1. In the Project Manager, select a database.

  2. Select Local Views or Remote Views, and choose New.

  3. Choose View Wizard.

  4. Follow the instructions on the wizard screens.

Starting the View Designer

To create a view of a local table, use the View Designer. Local tables can include native Visual FoxPro tables, any tables in a .dbf format, and tables stored on local servers.

To use the View Designer, first create or open a database. When you expand the plus sign next to the name of a database in the Project Manager, the Data tab shows you the components of the database.

Local views are stored in the database.

To create a local view

  1. From the Project Manager, select a database.

  2. Click the plus sign next to the Databases symbol.

  3. Under Databases, select Local Views and choose New.

  4. Choose New View.

  5. In the Add Table or View dialog box, select the table or view you want to use, then choose Add.

  6. After choosing the tables you want in your view, choose Close.

    The View Designer appears, showing the table or view you selected.

  7. In the Fields tab, select the fields you want to see in your view results.

  8. To view the results of your view, choose the Run button.

You can also start a view by choosing New from the File menu and choosing View.

View Designer for a local view

Using the View Designer is essentially the same as using the Query Designer. However, the View Designer has an extra tab — the Update Criteria tab — where you can control how updates are made. For more information about using the Update Criteria tab, see Updating Data later in this chapter. For more information about using the Query Designer, see Chapter 4, Retrieving Data.

Connecting to Remote Data

With remote views, you can extract a subset of the data on a remote ODBC server without having to download all the records to your local computer. You can work on the selected records locally, then send the changes or additions you make back to the remote data source.

You can connect to a remote data source in one of two ways. You can directly access any ODBC data sources registered on your computer or you can design custom connection using the Connection Designer.

ODBC is installed on your system when you choose the Complete or Custom installation option of Visual FoxPro. For more information about registering ODBC data sources, see Chapter 1, Installing Visual FoxPro, in the Installation Guide.

Creating a Connection

If you want to create a customized connection for a server, use the Connection Designer to create or customize a connection. The connections you create are saved as part of the database and contain information about how to access a specific data source.

Connection Designer

You can set connection options to save and name the connection for future use. You might have to consult your system administrator or server documentation to find the right settings for connecting to your particular server. Connection Designer settings are described in more detail in Chapter 8, Creating Views, in the Programmer’s Guide.

Note   Before you can create a connection, you must have a database open.

To create a new connection

  1. In the Project Manager, select a database.

  2. Select Connections and choose New.

  3. In the Connection Designer, enter the options that correspond to your server requirements.

  4. From the File menu, choose Save.

  5. In the Save dialog box, enter a name for the connection in the Connection Name box.

  6. Choose OK.

You can also create a connection by choosing New from the File menu and selecting the Connection option.

Creating a New Remote View

To gain access to your remote data in a view, you can use an existing connection or create a connection to use with your new view.

To create a new remote view

  1. In the Project Manager, select Remote Views and choose New.

  2. Choose New View.

  3. In the Select Connection or Data Source dialog box, select the Available data sources option.

    -or-

    If you have previously defined and saved a connection, select the Connections option.

  4. Select a data source or connection and choose OK.

  5. If required, in the Microsoft SQL Server or other login dialog box, enter your login ID and password.

You can also choose New from the File menu and select the Remote View option.

Once the connection is established, the Open dialog box appears so you can select a table on the remote server.

Tables on a remote server

After you select a table, the View Designer appears.

View Designer for a remote view

To continue creating your remote view, you can select output fields and set filters just as you would a local view.

Updating Data

You can use either local or remote views to update your tables.

Sending Updates to Tables

In the View Designer, the Update Criteria tab allows you to control how the changes you make to remote data (updates, deletes, and inserts) are sent back to the remote data source. You can also enable and disable updating for specific fields in the tables and set the SQL update method appropriate to your server.

Making a Table Updatable

If you want to have the changes you make to the local version of a table sent back to the source table, you need to set the Send SQL Updates option. You must set at least one key field to use this option. If the table you select has a primary key field defined and you have selected it in the Fields tab, the View Designer automatically uses the primary key field for the table as a key field for the view.

To enable updating of source tables

Setting Key Fields

When you first open a table in the View Designer, the Update Criteria tab shows which fields in the table are defined as key fields. Visual FoxPro uses key fields to uniquely identify the update records in remote tables that you have modified locally.

To set a key field

Setting key fields in the Update Criteria tab

If you have changed the key fields and want to restore them to the original setting in the source table, choose Reset Key. Visual FoxPro will check the remote tables and use the key fields from those tables.

Updating Specific Fields

You can specify that only certain fields in any given table are updatable. You must have a key field defined for a table in order to make any fields in that table updatable. If fields are not marked as updatable, users can make changes to the field in a form or Browse window, but these changes are not sent to the remote table.

To make a field updatable

Updating All Fields

If you want to be able to make changes to all the fields in a table, you can set all the fields in a table as updatable.

To make all fields updatable

Controlling How Update Conflicts are Detected

If you are working in a multiuser environment and using data accessible to others on a server, it is possible that others are attempting to make changes to the records on the remote server at the same time you are. To have Visual FoxPro check if data you are working with in a view has been changed by another user before you attempt to update it, use the options in the Update Criteria tab.

The options in the SQL WHERE Clause Includes box in the Update Criteria tab help you manage how records will be updated when multiple users might be accessing the same data. Before an update is allowed, Visual FoxPro checks specified fields in the remote data source table to see if they have changed since the record was retrieved into the view. If the records in the data source have changed, the update is not allowed.

Setting the SQL WHERE clause in the Update Criteria tab

These options determine which fields are included in the WHERE clause in the UPDATE or DELETE statement that Visual FoxPro sends to the remote data source or source table when you make changes to or delete records in the view. The WHERE clause serves to check if the data on the server has changed since you retrieved the record for use in your view.

To… Choose this SQL WHERE option…
Have the update fail if a key field has been changed in the source table Key fields only
Have the update fail if any of the fields marked as updatable have been changed on the remote table Key and updatable fields
Have the update fail if any fields you change locally have been changed on the source table Key and modified fields
Have the update fail if the timestamp of the record on the remote table has changed since you first retrieved it (only available if the remote table has a timestamp column) Key and timestamp

For more information about detecting and resolving update conflicts, see Chapter 17, Programming for Shared Access, in the Programmer’s Guide.

Customizing Views

You can customize views by defining how a field in a view displays, by controlling field display and data entry, by using expressions in views, by setting views up to prompt for values, and by setting advanced options to fine tune how data is exchanged with a server.

Controlling Field Display and Data Entry

Because your view is part of a database, you can take advantage of some of the same properties that a database provides for fields in a table. For example, you can assign captions, enter comments, or set validation rules to control data entry.

To control field display and data entry

  1. Create or modify a view in the View Designer.

  2. In the Fields tab, select a field in the Selected fields list.

  3. Choose Properties.

    View Field Properties dialog box

  4. In the View Field Properties dialog box, enter validation, display, and field type settings.

For more information about field validation, display, and mapping, see Chapter 7, Working with Tables, in the Programmer’s Guide.

Controlling the Update Method

To control how information in key fields is actually updated on the server, use the options in the Update Using option group. These options determine what SQL command is used in the update statement sent to the server or source tables when key fields in records are updated.

Update settings in the Update Criteria tab

You can specify that records be deleted and then replaced with the new values entered in the view (SQL DELETE then INSERT), or that server records be changed via the SQL UPDATE function supported by the server.

Adding Expressions to Remote Views

As with queries, you can include functions and expressions as filters in views.

To add an expression to a view

If you are basing a view on a remote data source, the functions shown in the Expression Builder reflect the functions supported by that server. Check your server documentation for a listing of the functions your server supports. Visual FoxPro does not attempt to parse the expressions you build; instead, it passes them on to the remote server.

Prompting for Parameters

You can set up views that prompt for the entry of a value to complete the query. For example, suppose you want to create a query to find customers located in a specific country. To do this, you need to define a filter on the country field and specify a parameter as the example in the filter. Parameter names can be any combination of alphabetic characters, numbers, and single quotes.

To set up a parameter for a view

  1. In the View Designer, add a new filter or select an existing filter in the Filter tab.

  2. In the Example box, type a question mark (?) and the name of the parameter.

    Parameter value entered as part of a filter for a view

When you use the view, a message box appears prompting for a value to include in the filter.

Setting Timeout Intervals

If you need to adjust how Visual FoxPro interacts with your server, you can set timing options.

To set timeout intervals

  1. In the Project Manager, select a connection and choose Modify.

  2. In the Timeout intervals area, set the options you need.

  3. From the File menu, choose Save.

For more information on setting timing options, see Connection Designer.

Optimizing Performance

You can optimize your views by setting data fetching and performance options. Use the Advanced Options dialog box to fine tune how records are retrieved in a view or how updates are made to the server or source tables.

Advanced Options dialog box

To set advanced options

  1. From the Query menu, choose Advanced Options.

  2. In the Advanced Options dialog box, set the connection information and data fetching options you need.

  3. Choose OK.

For more information about optimizing your views, see Chapter 8, Creating Views, in the Programmer’s Guide.