1 / 21

Visual Basic Programming II Lecture 10

Visual Basic Programming II Lecture 10. MIS233 Instructor – Larry Langellier. 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

Download Presentation

Visual Basic Programming II Lecture 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related