ADO tables

This will be Question 2.2 in the finals as from Finals 2018.

NOTE in the Exam Guidelines of 2018: "Pre-coded methods such as filter, sort and locate will NOT be allowed as coding in this regard must be done from first principles." We have all taught the grade 12 learners of 2018 these methods so make sure to tell them they may not use these methods.

The database will be linked to the program in the exam with or without a data module. We have better luck keeping the link if we close Delphi completely and then opening the file using the .dpr from Windows Explorer.

Here are some pointers:

1. The learners usually have to display in a RichEdit and search for records using an If-Statement in a While loop. Remember that the .next should not be inside of the if-Statement or you will be stuck in an infinite loop. The only time that we place the .next in the else of an if-Statement is when we are looping to delete.
2. Using a while loop to search for items or add the values of a field to a ComboBox.
   • Ensure that the tbl.next is outside the if-statement but inside the while loop.
   • Remember pos can be used to search for partial string. This was handy in Exemplar 2018 Q 2.2.1
   • Remember that ADO tables are case sensitive, unlike SQL, and you will need uppercase in your condition when searching for strings.
3. Reading from both tables: Use the outer loop to search for the condition and the inner loop to search for the fields that are linked (PK - FK).
4. Edit - the Exemplar of 2018, made changes to the current active record. Active means that you do not need to search for a specific record but you will change whichever record is currently active. Remember the .post after the change has been made.
5. Delete:
   • When deleting multiple tables ensure that you place the tbl.next in the else of the if where you are deleting. Failing to do this will mean that, when you have two records directly after each other, that needs to be deleted, you will skip over the second one. This occurs because the active record is changing to the next record when you are deleting and adding a tbl.next then goes to the second record after the one that was deleted.
   • The Exam Guidelines of 2018 indicate that learners will only delete from the "child" table containing the foreign key.
6. Insert.
   • My guess is that learners will use the RecordCount to add the Primary key as in the Exemplar 2018 - "Records in a database table are indexed starting at 1 up to the number of records in the table." I cannot see another way, except maybe hard code to, to add a unique value. To find the last value you need to sort and we may not use sort.
   • Note that it is not needed to use tbl.Last; before you insert.
   • The insert in the Exemplar of 2018 was using the current active record in the parent table to insert the foreign key.
   • If the insert asked to use hard code to insert the primary key and you click on the insert method twice, you will get an error since you are creating a duplicate primary key.
   • Remember the .post after adding the values to the fields.
7. If the question refers to the current active record - it means any record that is currently active in the table it refers to. My learners found this difficult to understand, as we never did examples like these in grade 11. They wanted to search using a locate which is not allowed.