15th chapter-Application and programming of data access Parts (II.) (3)

15.4.2.2 find records in a database table

If you want to find records in a database table, you must specify the field values for some fields that find records, and then retrieve them in the table to retrieve records that match the lookup values. If we look for a record in a table in the Paradox or dBASE database, the field that corresponds to the lookup value must be a key or secondary index field in the table. If you look for a table in the SQL database server, the lookup value must be the field specified in the table's Indexfieldnames property.

Delphi provides two ways to look up records in a database table: Goto mode and Find method. The two approaches are very similar, and the main difference is that it is not the same way to find the specified lookup value.

There are Setkey methods, Gotokey methods and Gotonearest methods for data lookup using goto mode. The practical steps are as follows:

① Make sure that the field you are looking for is a key or secondary index field.

② calls the Setkey method to place the ttable part corresponding to the table as the lookup state.

③ assigns the lookup value to the corresponding field.

④ calls the Gotokey method and tests its return value to verify that the lookup was successful.

Suppose that the first field in the Table1 table is a key field, Edit1 is an edit box in the application form, and the user can enter the lookup value by Edit1. The following code will be searched by Goto.

Table1.setkey; {Table1 to lookup State}

Table1.field[0]. Asstring: = Edit1.text; {Specify lookup value}

Table1.gotokey; {To find}

The last line of code performs a lookup in a table based on the lookup value specified by the user. There are two ways to find the results, perhaps success may fail, as determined by the Boolean value returned after the call to the Gotokey method, if True, the lookup succeeds, and the record pointer points to a record that matches the lookup value, and if Fale is returned, the lookup fails and the position of the record pointer does not change. The following code can test the return value after calling the Gotokey method to tell the user whether the lookup was successful or not.

Table1.setkey;

Table1.field[0]. asstring:= ' Smith ';

If not Table1.gotokey Then

ShowMessage (' record not found ')

In this section of code, if a record with the first field value of Smith is not found in the table, the application pops up a dialog box informing the user that "record is not found."

If you have more than one key field in a table or a secondary index that contains more than one field, and you want to specify a lookup value for the first field when you are looking, you must set the Keyfieldcount property value of the ttable part to 1. If you want to specify a lookup value for more than one field, you can specify lookup values only for adjacent fields, such as three fields in a secondary index, so we can only specify lookup values for the first field, first and second fields, first and second, and third fields, but not for the first and third fields.

The use of the Gotonearest method is exactly the same as the Gotokey method, only it is used for imprecise lookups, it does not require that the lookup result exactly match the lookup value, and when the table has a record that exactly matches the lookup value, it moves the record pointer to the record, when there are no records in the table that exactly match the lookup value, It finds the record closest to the lookup value and moves the record pointer to the record.

The following is a piece of code that applies the Gotonearest method:

Table1.setkey;

Table1.fields[0]. asstring:= ' Sm ';

Table1.gotonearest;

After executing the above code, if there is a record in the table with the first field value equal to SM, the record pointer moves to the record where the first field value equals SM, and the record with the first field value equals Smith, then the record pointer moves to the record.

If we are not looking for a key field in a database table, we can also find data for the field in the Indexfieldname property of the ttable part, or for the fields in the IndexName property. For example, suppose that there is a secondary index in the Customer table called Cityindex, we first set the IndexName property of the ttable part to Cityindex, and then find it, when we specify lookup values for the fields in Cityindex. Here is the specific program code:

Table1.indexname: = ' cityindex ';

Table1.open;

Table1.setkey;

table1.fieldbyname{' city '). Asstring: = Edit1.text;

Table1.gotokey;

Use the Find method: The method of finding the data in the database is: Findnearest method and FindKey method.

The FindKey method and the Findnearest method provide a simple way to find data, which combines setkey, specifying lookup values, performing a lookup three steps in one step, and when specifying a lookup value, is to pass the lookup value of each field into an array to FindKey or findnearest. Here is an example of making the FindKey method.

Suppose the first field in the corresponding table for Tabel1 is a key field.

Table.findkey ([Edit1.text]);

If you do this with the Gotokey method, you need to write the following code:

Table1.setkey;

Table1.fields[0]. Asstrine: = Edit.text;

Table1.gotokey;

The difference between the FindKey method and the Findnearest method is the same as that of the Gotokey and Gotonearest methods.

15.4.2.3 Create the main--Detail database application

The Mastersource and Masterfields properties in the ttable part are one-to-many relationships that define two database tables. The Mastersource property specifies the Tdatasource part that corresponds to the primary table, the Masterfields property specifies the field that establishes the connection between the primary table and the BOM, and when a one-to-many relationship is established between the primary table and the BOM, it may not be based on just one field, possibly multiple fields. If you have more than one field, use a semicolon to separate multiple fields when describing the Masterfields property. such as table1.masterfields: = ' OrderNo; Custno '. At design time you can use the Field Connection designer (the field link Designer) to create a one-to-many relationship for the two tables, and you can double-click the masterfields of the ttable part in object Inspector to open the field link Designer To create a one-to-many relationship. If you are creating a one-to-many relationship between a customer.db table and a order.db table, use the field Link Designer as shown in Figure 15.5.

Figure 15.5 Using the field Link Designer to create a one-to-many relationship

The Field Link Designer provides a visual way to create a one-to-many relationship between the main and the BOM. The Available Indexes combo box in the figure holds the key fields and index fields in the schedule, and you can select the index fields to connect to. Select a key field for the connection in the primary table, and then connect it to the corresponding key field in the schedule, click the Add button, and the connection field for the BOM will appear in the Joined Fields list box, such as:

Custno->custno

15.5 Tdatasource parts and their applications

The Tdatasource part is a very important part of developing a database application, and it is a bridge connecting a dataset part Ttable or Tquery and data browsing part. The Tdatasource widget itself is so simple that it has fewer properties, events, and methods, and does not have to do much to work with it, primarily for the data browsing part, if the data browsing part is not used in the application, We also do not need to set the Tdatasource part for the application.