CP2030 Visual Basic For C++ Programmers

1. CP2030Visual Basic For C++ Programmers • Week 8 - Databases continued: • Example using Further Features • Find Method • Reposition and Validate Event • Multiple Data Controls • Queries • Other Topics to Consider • Last Words on Databases

2. Example of Other Features • Below is an example (dental3.mak) which is used to demonstrate further data control functionality • Modifying properties • Refresh method • Referencing fields • Unbound controls • EOF property • IsNull • The example shows the addition of a list box

3. Example - Code • Sub Form_Load () • Data1.DatabaseName = "c:\files\uow\vb_sc_da\dental.mdb" • Data1.RecordSource = "Patients" • Data1.Refresh 'Open database & recordset • Do While Not Data1.Recordset.EOF • If Not IsNull(Data1.Recordset(“Surname”).Value) Then 'If not empty, then • List1.AddItem Data1.Recordset(“Surname”).Value 'add to the list • End If • Data1.Recordset.MoveNext 'Move to next record • Loop • Data1.Refresh 'Rebuild the recordset • End Sub

4. Property Setting • The DatabaseName, RecordSource and Datafield (the field within a database to which a data aware control links) properties can all be set at design and run time • The DataSource property (the data control to which a data aware control links e.g. Data1) can only be set at design time • e.g. in above code Data1.DatabaseName = "c:\files\uow\vb_sc_da\dental.mdb" Data1.RecordSource = "Patients"

5. Refresh Method • Used to open the database and build or reconstruct the dynaset in the controls recordset property at runtime • Has side affect of making the first record current in the recordset e.g. Data1.Refresh 'Open database & recordset • In the code example above it is used twice i. To initially open the database and recordset ii. To refresh the recordset, making the first record current, after the Do ... Loop has completed

6. Referencing Fields • Can move through records using MoveNext etc • To select individual fields within a record, need to reference it within the recordsets current record, can use any of the following e.g. • Data1.Recordset(“First Name”).Value Data1.Recordset.Fields(“First Name”) Data1.Recordset.Fields(0).Value Data1.Recordset.Fields(0) Data1.Recordset(“First Name”) • All equivalent since ‘Fields’ is a default collection the recordset and ‘Value’ is default property for a ‘Field’

7. Unbound Controls • To display data in controls other than the 5 data aware controls requires coding to manipulate the data in and out of the control • In the above code the required field is referenced and then added to a list boxes list e.g. List1.AddItem Data1.Recordset(“Surname”).Value 'add to the list • Here we’re adding the second field (Surname) of each record into the list box

8. EOF / BOF / IsNull • EOF is used to check if the end of the recordset has been reached, when EOF becomes true • Do While Not Data1.Recordset.EOF • In the example the code loops while the end of the file has not been reached • BOF is True when at beginning of recordset • IsNull is a Visual Basic command which checks if a variable is a null, here it is used to check the value of the referenced field of the current record • If Not IsNull(Data1.Recordset(1)) Then ‘If not empty, then

9. Example - Code Revisited • Sub Form_Load () • Data1.DatabaseName = "c:\files\uow\vb_sc_da\dental.mdb" • Data1.RecordSource = "Patients" • Data1.Refresh 'Open database & recordset • Do While Not Data1.Recordset.EOF • If Not IsNull(Data1.Recordset(1)) Then 'If not empty, then • List1.AddItem Data1.Recordset(1) 'add to the list • End If • Data1.Recordset.MoveNext 'Move to next record • Loop • Data1.Refresh 'Rebuild the recordset • End Sub

10. Alternative version (using For loop and RecordCount property) Data1.Recordset.MoveLast ‘sets value of RecordCount Data1.Recordset.MoveFirst For I = 1 to Data1.Recordset.RecordCount If Not IsNull(Data1.Recordset(1)) Then 'If not empty, then List1.AddItem Data1.Recordset(1) 'add to the list End If Data1.Recordset.MoveNext 'Move to next record Next I

11. Find Instructions • The FindFirst, FindLast, FindNext and FindPrevious instructions are used to locate records in the recordset e.g. Data1.Recordset.FindLast “[First Name] = ‘Jeremy’ ” Location Field to Criteria method search to find • A record which matches the criteria is made the current record, • Criteria can be variables, strings with wildcards Data1.Recordset.FindLast “[Surname] = ‘ “ & NameVar & “ ’ ” Data1.Recordset.FindLast “[First Name] = ‘J*’ ”

12. Reposition Event • A Reposition event occurs after a new record becomes current. • First happens when the Data control loads and makes the first record in the recordset current • Is triggered by ‘Move’ or ‘Find’ method Data Control load New Code (Move / Find) Record Reposition Current Event

13. Validate Event • Allows validation of changes before database updates • A Validate event occurs before a new record becomes current. • It also occurs before an Update, Delete, Unload or Close operation Update/Delete Data Control load Unload/Close Code (Move / Find) Validate New Event Record Current

14. Data Control - Validate event • Private Sub Data1_Validate ( Action as Integer, Save as Integer ) • Action parameter identifies current operation • 1 MoveFirst (includes navigation arrow) • 2 MovePrevious ( “ “ “ ) • 3 MoveNext ( “ “ “ ) • 4 MoveLast ( “ “ “ ) • 5 AddNew • 6 Update • 7 Delete • etc… • Save parameter =True (-1) if any data attached to data control has changed

15. Data Control - Validate event • Validate event can prevent the action from happening • E.g. Private Sub Data1_Validate ( Action as Integer, Save as Integer ) If val(txtAge) < 18 then MsgBox “Too young!” Action = 0 End If End Sub

16. Validate Event • Save - normally True(-1), set False(0) to stop new data being saved • Note. Don’t use methods under the Validate event e.g. MoveNext, as this creates an infinite loop MoveNext Validate Event

17. Multiple Data Controls • Can use two data controls to bring in data from two separate tables • The tables can either be in the same database or in separate databases • Databases could even be different types • But the data under each data control is totally independent of the other data control • The sets of data aware controls update separately; they are dependent on the data control they are attached to

18. Queries • If want to relate the data from two tables together: Tables must be in same database, and have a common field to relate • Use a query to create the relationship • Queries are written using SQL, structured query language • Complex language, but some databases e.g. MSAccess allow you to build queries using blocks, it will then generate the SQL code which can be used by VB to link in a query

19. Query Example • If you had two tables having a common field of Patient category, one table gives patient demographics and the second costing rates and descriptors for each category. • The SQL to use as the RecordSource to relate the patient name to the cost rate would look like Data1.RecordSource = "SELECT Patients.*, [Cost Rate] FROM Patients, Costing, Patients INNER JOIN Costing ON Patients.[Category] = Costing.[Category] Order by [Surname]" • It isn’t simple !!

20. Other Topics To Consider • Transaction Statements - Used to control a series of updates to a database, can be used to allow the ability to undo updates • Error Event - Happens when an error occurs such as an invalid database name being specified • UpdateRecord as for Update except doesn’t trigger a validate event • UpdateControl - reads back values of current record from the recordset into bound controls, means changes to their values can be undone

21. Other Topics To Consider • FieldSize - gives the size of a memo field • GetChunk - gets a 64k chunk of data from a memo field, it has an offset parameter for where to get the chunk • AppendChunk - appends data in 64k chunks to a memo field

22. Last Words on Database • Visual Basic Professional allows greater control over database development with • Ability to create dynaset in code without using a data control • Tools to create new databases and modify existing database structures • Visual Basic uses the MSAccess engine • Visual Basic 3.0 won’t link to MSAccess 2.0 developed database, need to install a fix file COMLAYER.EXE. This gives full compatibility and link ability.