CP 2030 Visual Basic For C Programmers v

CP 2030 Visual Basic For C++ Programmers v Week 8 - Databases continued: Example using Further Features v Find Method v Reposition and Validate Event v Multiple Data Controls v Queries v Other Topics to Consider v Last Words on Databases v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 1

Example of Other Features v Below is an example (dental 3. mak) which is used to demonstrate further data control functionality – – – v Modifying properties Refresh method Referencing fields Unbound controls EOF property Is. Null The example shows the addition of a list box CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 2

Example - Code v v v Sub Form_Load () Data 1. Database. Name = "c: filesuowvb_sc_dadental. mdb" Data 1. Record. Source = "Patients" Data 1. Refresh 'Open database & recordset Do While Not Data 1. Recordset. EOF If Not Is. Null(Data 1. Recordset(“Surname”). Value) Then 'If not empty, then List 1. Add. Item Data 1. Recordset(“Surname”). Value 'add to the list End If Data 1. Recordset. Move. Next 'Move to next record Loop Data 1. Refresh 'Rebuild the recordset End Sub CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 3

Property Setting The Database. Name, Record. Source and Datafield (the field within a database to which a data aware control links) properties can all be set at design and run time v The Data. Source property (the data control to which a data aware control links e. g. Data 1) can only be set at design time v e. g. in above code v Data 1. Database. Name = "c: filesuowvb_sc_dadental. mdb" Data 1. Record. Source = "Patients" CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 4

Refresh Method Used to open the database and build or reconstruct the dynaset in the controls recordset property at runtime v Has side affect of making the first record current in the recordset e. g. v Data 1. Refresh 'Open database & recordset v 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 CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 5

Referencing Fields Can move through records using Move. Next etc v To select individual fields within a record, need to reference it within the recordsets current record, can use any of the following e. g. v Data 1. Recordset(“First Name”). Value v Data 1. Recordset. Fields(“First Name”) Data 1. Recordset. Fields(0). Value Data 1. Recordset. Fields(0) Data 1. Recordset(“First Name”) v All equivalent since ‘Fields’ is a default collection the recordset and ‘Value’ is default property for a ‘Field’ CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 6

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 v In the above code the required field is referenced and then added to a list boxes list e. g. v List 1. Add. Item Data 1. Recordset(“Surname”). Value 'add to the list v Here we’re adding the second field (Surname) of each record into the list box CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 7

EOF / BOF / Is. Null v EOF is used to check if the end of the recordset has been reached, when EOF becomes true – Do While Not Data 1. Recordset. EOF In the example the code loops while the end of the file has not been reached v BOF is True when at beginning of recordset v Is. Null 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 v If Not Is. Null(Data 1. Recordset(1)) Then ‘If not empty, then v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 8

Example - Code Revisited v v v Sub Form_Load () Data 1. Database. Name = "c: filesuowvb_sc_dadental. mdb" Data 1. Record. Source = "Patients" Data 1. Refresh 'Open database & recordset Do While Not Data 1. Recordset. EOF If Not Is. Null(Data 1. Recordset(1)) Then 'If not empty, then List 1. Add. Item Data 1. Recordset(1) 'add to the list End If Data 1. Recordset. Move. Next 'Move to next record Loop Data 1. Refresh 'Rebuild the recordset End Sub CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 9

Alternative version (using For loop and Record. Count property) Data 1. Recordset. Move. Last ‘sets value of Record. Count Data 1. Recordset. Move. First For I = 1 to Data 1. Recordset. Record. Count If Not Is. Null(Data 1. Recordset(1)) Then 'If not empty, then List 1. Add. Item Data 1. Recordset(1) 'add to the list End If Data 1. Recordset. Move. Next 'Move to next record Next I CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 10

Find Instructions v The Find. First, Find. Last, Find. Next and Find. Previous instructions are used to locate records in the recordset e. g. Data 1. Recordset. Find. Last “[First Name] = ‘Jeremy’ ” Location method Field to search Criteria to find A record which matches the criteria is made the current record, v Criteria can be variables, strings with wildcards v Data 1. Recordset. Find. Last “[Surname] = ‘ “ & Name. Var & “ ’ ” Data 1. Recordset. Find. Last “[First Name] = ‘J*’ ” CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 11

Reposition Event A Reposition event occurs after a new record becomes current. v First happens when the Data control loads and makes the first record in the recordset current v Is triggered by ‘Move’ or ‘Find’ method v Data Control load Code (Move / Find) New Record Current CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Reposition Event Component 8, Slide 12

Validate Event Allows validation of changes before database updates v A Validate event occurs before a new record becomes current. v It also occurs before an Update, Delete, Unload or Close operation v Update/Delete Unload/Close Data Control load Code (Move / Find) Validate Event CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton New Record Current Component 8, Slide 13

Data Control - Validate event v Private Sub Data 1_Validate ( Action as Integer, Save as Integer ) v Action parameter identifies current operation – – – – v 1 Move. First 2 Move. Previous 3 Move. Next 4 Move. Last 5 Add. New 6 Update 7 Delete etc… (includes navigation arrow) ( “ “ “ ) Save parameter =True (-1) if any data attached to data control has changed CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 14

Data Control - Validate event v Validate event can prevent the action from happening v E. g. Private Sub Data 1_Validate ( Action as Integer, Save as Integer ) If val(txt. Age) < 18 then Msg. Box “Too young!” Action = 0 End If End Sub CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 15

Validate Event v Save - normally True(-1), set False(0) to stop new data being saved v Note. Don’t use methods under the Validate event e. g. Move. Next, as this creates an infinite loop Move. Next Validate Event CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 16

Multiple Data Controls Can use two data controls to bring in data from two separate tables v The tables can either be in the same database or in separate databases v Databases could even be different types v But the data under each data control is totally independent of the other data control v The sets of data aware controls update separately; they are dependent on the data control they are attached to v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 17

Queries If want to relate the data from two tables together: Tables must be in same database, and have a common field to relate v Use a query to create the relationship v Queries are written using SQL, structured query language v 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 v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 18

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. v The SQL to use as the Record. Source to relate the patient name to the cost rate would look like v Data 1. Record. Source = "SELECT Patients. *, [Cost Rate] FROM Patients, Costing, Patients INNER JOIN Costing ON Patients. [Category] = Costing. [Category] Order by [Surname]" v It isn’t simple !! CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 19

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 v Error Event - Happens when an error occurs such as an invalid database name being specified v Update. Record as for Update except doesn’t trigger a validate event v Update. Control - reads back values of current record from the recordset into bound controls, means changes to their values can be undone v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 20

Other Topics To Consider Field. Size - gives the size of a memo field v Get. Chunk - gets a 64 k chunk of data from a memo field, it has an offset parameter for where to get the chunk v Append. Chunk - appends data in 64 k chunks to a memo field v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 21

Last Words on Database Visual Basic Professional allows greater control over database development with v Ability to create dynaset in code without using a data control v Tools to create new databases and modify existing database structures v Visual Basic uses the MSAccess engine v 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. v CP 2030 Visual basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton Component 8, Slide 22