Visual Basic Programming II Lecture 10

1. Visual Basic Programming IILecture 10 MIS233 Instructor – Larry Langellier

2. This Week – Databases • Understand Relational Database Technology • Using the Data Control • Understand the Jet engine and working with DAO Collections • Open Databases and Recordsets • Work with the Recordset object • Learn to create, retrieve, and use QueryDef objects

3. What is a Relational Database? • A database is a place to store information to persist it for future use • A database is a collection of tables, in which each rows of each table represent records, and the columns of each record refer to attributes of that record • Information in a database can be retrieved and updated dynamically, much more readily than in a sequential file • Relationships can be formed between fields in different table to tie them together in meaningful representations • Let’s look at a database – design1.mdb

4. Relational Database Terminology • Table – a logical structure used to group sets of related information • Record – a grouping of a set of attributes describing each person, place or item in a database • Field – an attribute of a record • Primary Key – a unique ID (number) used for retrieving records that insures each record is different • Foreign Key – fields in common between tables that can be used to link the tables together • Indexes – fields used to retrieve records from a table • Queries – questions asked of a database • Relationships – table connections based on keys

5. Using the Data Control • Visual Basic utilizes the Jet database engine – the same as Access • The Jet engine can be used to manipulate other databases like Paradox, dBase and FoxPro • The easiest way to connect to Visual Basic’s built-in database capabilities is to use the Data Control • you can use it to connect to a database with writing a single line of code • Demo - Adding a DataControl to a form

6. Data Control Properties • There are four properties you need to set for a Data Control prior to using it (at Design Time or in code): • DatabaseName: The location (including full path) of the database aDC.DatabaseName = “C:\data\design1.mdb” • Connect: The type of database you’re connecting to (Access is the default) • RecordSource: Where the records will come from aDC.RecordSource = {tablename, sqlstatement, queryname} • RecordSetType: What type of recordset • Table-type – part of the underlying database and it can be updated • Dynaset – returned from a query and can be updated • Snapshot – can be from a table or query but is read-only

7. Bound Controls • Controls we’ve already seen (TextBox, CheckBox, ImageBox, Label, PictureBox, etc.) can be used to view or edit a record’s field • You “bind” these controls by setting properties • DataSource – the source of the records, typically a DataControl you’ve added and opened • DataField – the specific field in a record bound to the control • Example control.DataSource = aDataControl control.DataField = "Customer Name" • Example: 23.1 – A Beginning Database • Notice how the Data Control is used to move from record to record – changing the Bound Control contents

8. The Refresh Method • The Data Control’s Refresh method is used to open or reopen a database • When executed, the underlying query is re-executed, and the Data Control automatically points to the first record and makes it current • Syntax [form].datacontrol.Refresh

9. Do It Together! • Connect to the “Players” table in the baseball.mdb database (located in today’s example download) using a DataControl • Using BoundControls display: • Name Walks • Position Doubles • At Bats Triples • Hits Home Runs • Test It!

10. Sample Interface

11. Working with Recordsets • A Recordset is a collection of several records (rows) • It doesn’t matter whether they’re from a table or a query • It maintains a pointer to a current record • You can access the recordset using the Data Control’s Recordset property aDC.Recordset • Recordset Methods • AddNew - create a new record • Delete - delete the current record • Edit - open the current record for editting • Update - save the contents of the buffer to a table (or dynaset) • MoveFirst, MoveLast, MoveNext, MovePrevious - move to a record

12. Working with Recordsets (cont.) • Recordset Methods (cont.) • FindFirst, FindLast, FindNext, FindPrevious: locate a record based on search criteria • Used only with dynaset or snapshot recordset types • Syntax anRS.FindFirstcriteria Where criteria is like the WHERE clause of an SQL statement ("State = 'IL'") • Index: Used with a table-type recordset to select one of the database’s indices, not necessary if you’re using the primary key • Seek: move to an indexed record based on method arguments • Used with table-type recordsets to search the table using the current Index • Syntax anRS.Seekcomparison, key1, key2, ... • Examples • 23.2: Adding and Deleting a Record in a Database • 23.3: Adding Editing to a Design

13. Working with Recordsets (cont.) • Assigning a value to a field aDC.Recordset("Order Number") = CInt(txtOrdNum.Text) You must position the DataControl to the desired record before you edit fields!!! • BOF and EOF • Boolean properties that indicate whether the current location in a Recordset is at the beginning or end of the file • Examples aDC.Recordset.EOF aDC.Recordset.BOF 'both return True or False • The DataControl will automatically prevent you from going past the beginning or end

14. Recordset Errors • Three types of errors to avoid when modifying databases: • Data Validation: assign the wrong type of value or an out of range value to a field • Entity Integrity: attempt to add a 2nd record with the same primary key as another already in the database • Referential Integrity: attempt to change data in one table that violates data in another, like deleting a sales rep when customers still point to them

15. Do It Together! • Connect to the baseball.mdb using a DataControl • Then, using the DataControl’s Recordset, programmatically: • Iterate through each record of the Player Table using Recordset methods • Print each player’s name and batting average to the form • To compute the batting average you will need to retrieve the hits and atBats • Batting Average = Hits / At Bats

16. Interface Example

17. Sample Solution Private Sub cmdDisplay_Click() With dcPlayers .RecordsetType = 0 .RecordSource = "Players" .Refresh End With dcPlayers.Recordset.MoveFirst While Not .EOF Print dcPlayers.Recordset("Name") dcPlayers.Recordset.MoveNext Wend End Sub Private Sub Form_Load() ChDrive App.Path dcPlayers.DatabaseName = App.Path & "\baseball.mdb" End Sub

18. Using SQL from Visual Basic • Visual Basic supports SQL (Structured Query Language) – a specialized language for interacting with databases • SQL allows a programmer to: • Create Tables • Define the relationships among tables • Manipulate the data held in tables • Snapshot and Dynaset Recordsets result from an SQL query that extracts data from a database into a result set • Syntax: SELECTthe following fields FROMthis table [WHERE(match this or these criteria)] • Examples: • 23.4 Use a Query

19. Building a Database Using Visual Data Manager (VisData) • Add-Ins -> Visual Data • Create a Database • File -> New • Add Tables to the Database • Right-mouse click • Add Fields to the Tables • Click the “Add Field” button • Add Indices – Primary and Secondary • Click the “Add Index” button • Enter Data – Example 23.6 • Build the Table and double click the icon for the table • Building Queries – Example 23.7

20. Details… • Next Class • Read Chapter 21: Modular Design • Read Chapter 24: Using Data Access Objects • Homework #11 will be due at the beginning of class

21. Semester Project –Preliminary Design • This Design should be comprised of three elements: • A Prototype of your User-Interface – similar in spirit to exercise 21.2 (page 499) that we looked at in detail last week • An Object Model definition • What Classes are you going to have? • What Properties will each class have? • What Behaviors (Methods) will each class have? • A Database schema • What Tables will your database contain? • What Fields will each table have? • What will the Primary Keys be for each table? • What Foreign Keys will you have between tables?