/---------------------\
| Notes for lecture12 |
| 23 November 1999    |
| CS 125-609          |
| Michael Goldwasser  |
\---------------------/

=========================================================================
     AN INTRODUCTION TO DATABASES
=========================================================================

Preface:   Consider this a "Cliff Notes" version of Chapter 5.3.

=========================================================================

Overview:

 A Database -- collection of (related) Tables
    Each Table -- collection of Records
        Each Record -- a set of fields
           Each Field contains some data
             (stored in whatever datatype is appropriate,
              e.g. Integer, String, Boolean) 


 Example:  Think of your personal addressbook/phonebook.


 To write a program which uses/modifies/creates a database:

 (1) You can certainly write a VB program completely from scratch to
     do exactly what you want (how would you store all of the data?)

 (2) Because using a database is such a common need across many
     different applications, VB includes a lot of extra controls and
     objects that are designed for manipulating databases (thus saving
     you and others the trouble of writing all of the code yourselves).

-------------------------------------------------------------------------

What should a database contain?

  The first step in creating a database is not entering the data, it
  is deciding what the database should contain!   What fields should
  exist in what tables???

  Although VB gives you a way to design a brand new database, it involves
  a lot of work, and we will not cover this in the course.  Instead,
  there are many existing programs out there which let you design
  databases (Access, dBase, Fox Pro, Excel, Lotus, Paradox).
  Unfortunately, each of these programs uses its own specical file
  format for saving the database to disk. (in fact, older versions of
  the same program will often use different file formats!).
  Fortunately, VB includes a translator which makes it able to
  connect to files from most of the existing database programs (on
  Windows, that is).

  Also, the VB software package we use includes a Visual Data Manager
  (under the "Add-Ins" menu) which can be used to design the
  fields/tables in a new database (using the same file format as
  MS Access) 

  So if you are writing a new VB program and you want to use the
  database features of the language, you should start by creating a
  database file with one of these other programs.   Even if the
  database contains zero items, at least you will have defined what
  type of information and fields should be in the database.


-------------------------------------------------------------------------

Reading/Modifying the data stored in a database from VB

  To do this, we will make use of

    * the Data Control, a specific control which allows us to connect
      to an existing database file, and to move through the different
      records in the tables, always refering to a "Current Record"

    * various "data-aware" controls which allow us to associate that
      control with particular information saved in the database,
      displaying the information and often allowing the user to modify
      it. Many of the controls we have already seen are data-aware,
      such as Label, TextBox, PictureBox, CheckBox to name a few. 


To set up these connections (either at design or run time), the
following properties must be set.

For the Data Control:

            property             meaning
            --------             ----------
            DatabaseName         the file location

            RecordSource         which table of the database? 

            Connect              what type of database is this?
                                 (e.g., default is Access)


For a data-aware control:

            property             meaning
            --------             ----------
            DataSource           what Data Control is this bound to?

            DataField            which field of the current record? 


=========================================================================

RUNTIME COMMANDS

You can do everything in runtime code that the user could have done
with the mouse. (in fact, you can do much more) 


methods that involve the choice for the current record
-----------------------------------------------------
data1.Recordset.MoveNext     ' make next record the current one
data1.Recordset.MovePrevious ' make prev record the current one
data1.Recordset.MoveLast     ' make last record the current one
data1.Recordset.MoveFirst    ' make first record the current one
data1.Recordset.EOF          ' true after MoveNext from last record
                               (i.e. End Of File)
data1.Recordset.BOF          ' true after MovePrevious from first record
                               (i.e. Beginning Of File)


to read a field of the current record, use
------------------------------------------
data1.Recordset.Fields("fieldName").Value


To edit field(s) of current record, use this sequence
-------------------------------------------
data1.Recordset.Edit                   ' creates a temp copy for editing
data1.Recordset.Fields("Country").Value = <new value>     'set new value
data1.Recordset.Update                 ' update record in actual database


methods which add/delete/count records in a database
----------------------------------------------------
data1.Recordset.RecordCount        ' the number of records which exist
data1.Recordset.Delete             ' marks current record for deletion
data1.Recordset.AddNew             ' creates new record at end of table
                                     (and points Current Record to it)
                                     NOTE: You must use Update to save
                                           values for the new record
=========================================================================