6-8-99
======

database
entity
attribute
table
record
field
key (primary, secondary, foreign)
index
relationship
query
SQL
data rendering



Data - a Collection of key records which meet unique needs.
A database has records and fields.
Records are instances of some entity, which is a class of object (people, services, products)

Each column might become an attribute, such as:

| first	| last	| addr |  phone

what belongs and doesn't belong in the customer database?


see table (example) on page 621.

Eventually, if you just "throw data" into a table, you will have data redundancy.

You generally have several such tables storing information.

Figure "24.5" shows how to read several databases.

Primary Key is a unique identifier.

If an index number is based on the primary key, it could be called the primary
index.

Query - a formailzed instruction to a database that will return a set of records
or perform a set of records as specified in the entry.


Can return a collection of records, or manipulate a database.

For database design objectives:

(page 628)

1) eliminate redundant data
2) be able to locate individual records quickly.
3) Make enhancements to the database easy to implement.
4) keep the database easy to maintain.

examine data controls in chapter 26.


6/9/99
======

Today, we're playing with the Visual Data Manager in Visual Basic.

(page 723 - ?? of book...)

Go to "Addins"...
Microsoft Data Manager...
open a possible database file....it will read data from it.

Record sets more or less must be one of three types:

	Table - directly represents basically everything.
	Dynaset - 
	Snapshot

recordset  record     speed   updates  navigation      searching?
type       source             allowed?       
=========  ========   =====   =======  =============   ==========

table      table      medium  yes      has move        seek method
           results                     methods.        theory index
           only.                                       (you can find jones
                                                       only if you have a
                                                       pre-defined index)

                   ==directly accesses the table==

recordset  record     speed   updates  navigation      searching?
type       source             allowed?       
=========  ========   =====   =======  =============   ==========

dynaset   could be    slower  yes      also has move   find methods
          table or                     methods.        (queries)
          query

               ==might use pointer info to access table==


recordset  record     speed   updates  navigation      searching?
type       source             allowed?       
=========  ========   =====   =======  =============   ==========

snapshot   could be   faster  no       also has move   find methods
           table or           (read    methods.        (queries)
           query               only)


          ==read only copies of data that are stored in memory==



Data control opens a database and creates a record set.



snapshot

6/10/99
=======

IF you wanted to create a recordset using a query, which datatype would you use?
Dynaset or Snapshot

If you wanted to make changes?
Only Dynaset.


We need to learn the names of tables, at the very least, in order to 
create a dataset.

Properties:

Database Name: Path and file name of the database
RecordSource: Table name or query text
RecordsetType: Table, Dynaset, or Snapshot
Readonly: T/F
Editmode: state of editing the current record
Visible / Enabled
may be set at run time

Refresh Method:

Closes and rebuilds underlying recordset
side effect: wipes out index property.

If you invoke the refresh method, it will basically close the database,
and reopen it.

Validate event

-occurs before the current record changes
-enables input validation before save

Private Sub Data1_Validate (action as integer, save as integer)
end sub

- Action: Integer code for operation that caused validate to occur
- Save: (T/F) for whether bound data has changed.

Reposition:
occurs AFTER the current record changes

Bind - to attach, or connect, a data-aware control to a data control
and thus, to a database.

Some controls that might be bound:
text box, label, list box, combo box, check box, picture box, image control,
masked edit box

bindable controls have two data properties:

datasource: name of data control
datafield: field name in data control's recordset the bound
control will display.

data controls convention - dat

6/11/99
=======
Usually when you're trying to load something or set something,
do it in the form_activate event - not the load event (as the
form itself binds the database, and the database is really not
accessable at that point).

How do you order a dynaset or snapshot? By coding SQL into the
command set. 

So...linking:

Set MyDB = OpenDatabase ("C:\data\employee.mdb")
Set MyRS = MyDB.OpenRecordset (X, dbOpenDynaset)

(if you don't know the name, that's fine...)

Myrs becomes the recordset object. (which is equal to datplayers.recordset.movefirst)

or myrs.movefirst

in data control - when do you use .recordset or not?

example:

Private Sub cmdQuery_Click()
Dim SQL$

Let SQL$ = "select * from players where HR 
let datplayers.recordsource = SQL$
datPlayers.Refresh
Data1.Recordset.Movefirst

Then there is a close method...


6/14/99
=======

Answers to Quiz:

When using a data control, only one record can be accessed at a time; this record
is called the __CURRENT__ record.

How can a user set this record to some other record?  With A __DATA CONTROL__

When a form with a data control loads and displays at run-time, what two things
are done automatically for you, provided that you have set the appropriate data
control properties?

the database is "linked", and the first record which was initially entered
is called up for you (unless you include an ORDER-BY statement somewhere)

The data control element is quite nice. :)

if you move to the next or previous record ,check to be sure it's 
not .BOF (beginning of file), or .EOF (end of file)

Page 8 is a very good "code" slide.

Lname$ = Myset.Fields("Lname") - takes a record set. Assumes there
is a current record in existance, takes the field "Lname", and assigns
it to Lname$.

on the SQL line - you can put all of the SQL on one line.
(in SQL - when using something like s=s & "Where Position = 'SalesRep';" , the "'"'s are
necessary.

This is to __READ__ data:  (not change it)
This also assumes you have a current record.

When are the SQL statements activated? well - when the jet-engine is run inside vb
on a form load or activate, or on a refresh.

MySet.Fields("LName").Value - what is the value of the field "LName?"

With a data control:

Data1.Recordset.Fields("LName").Value

From Projects, References, find Microsoft DAO 3.5 Object Library.

6/15/99
=======

When running at home, the following might need to be activated under:
	Projects, References
	Microsoft DAO Library
	Databound Grid
	Databound List

Best help - is in RECORDSET OBJECT, RECORDSETS COLLECTION SUMMARY

(recordset, collections...)

Nomatch=true - search failed ; Nomatch = false, it worked.
this is a data control property.

data1.recordset.nomatch

6/16/99
=======

TIMELINE: 

Friday - June 18 - Quiz 2

	write a piece of code to create a recordset without a data control
	write a piece of code to create a recordste with a data control
	navigate
	seek / find

	info on a table - write a piece of code to access that

Wednesday - June 23 - Program 1 due
Thursday - June 24 - Test 1

Nice info on the seek method (with a code example) on pages 736..?

Landry's Side _6_ gives you code for :

recordsetobject.find (first, next, last, previous)

nomatch for the search fails for if a search fails. :)

move first makes first item in the recordset the first item. (more navigation)

find - more of a search and nagivation - find "first"


write some code that checks for beginning of file and end of file
then do the move methods (when coding manually without a data control)

a seek assumes - rs.index = "Name"
(to define an index)
and it must be a table type. (not a dynaset or a snapshot)

so - to search, you might be able to find:

rs.index  =  "name"
rs.seek ">=", "M"  //to find the m's
then - since they're in order by name...
rs.movenext would find the next one.

check for EOF still...
in the process of this, you make sure it's an "M" before you display it.

with the finds, you don't have an index. (biggest difference between
seek and finds, i suppose)


rs.findfirst "City = 'Tallahasseee'"

       (or = , > , < , >= , et cetera)

seek - works with indexes (tables)
recordset.find works without indexes.

example code:

rs findfirst "city ='tallahassee'"
while not rs.nomatch
	'* process record
	picout.print rs.fields('name')
	rs findnext "city='talahassee'"
wend

you must have a find first - before you can have a find next.
(must _NOT_ leave out criteria)

book talks about find on page 733 / 735

another example:

dim findcrit as string

findstr="smith"
findcrit="lastname='" & findstr& "'"
newdyn.findfast findcrit

&'s are concatanation

so...to code this:

rs findfast "city=  '"txtcity" & "'"

debug at runtime!


6/17/99
=======

to count something or other. :)  :

count = 0
dat players.recordset.find first "hr > 20 AND team=8"
do while not datplayers.recordset.nomatch
	count=count+1
	datplayers.recordset.findnext "hr > 20 and team = 8)
loop


As for a SEEK:

dim RS as Recordset
set RS=datplayers.recordset
RS.index="Name"
RS.Seek=>=", "M"
found=not rs.nomatch
if found then
	found=left(rs("name"),1)=m
else
	if reposition current rec
	exit sub
end if

do while found
	'* process current record
	rs.movenext '*we may be at the end of the file or we may get an "n", or another "m"
	found = not rs.eof
	if found then
		found=left (rs"more")=m
	end if
loop


assignment #1:

you may use common dialog control ;)
you may _NOT_ use data form wizard.

changing data in the rs:

	editing
	adding

There are three main methods for this:

.addnew
.edit
.update

.addnew - creates a blank copy buffer

.edit - creates copy buffer which contents of current record

.update - 

discard copy buffer - .cancelupdate

look at help under update - dao. :)

if you have not invoked an .addnew or a .edit, it is illegal to set
an assignment statement to the fields.

(example - lets make all players home runs equal to zero)

rs.movefirst
	do while not RS.EOF
		RS.edit //creates copy buffer
		rs fields("HR")=0
		rs.update //record is overwritten with contents of copy buffer
		rs.movenext
		//copy buffer is discarded ; it's written to the screen

edit - save

(edit could un-grey boxes, and allow you to make changes
save could let you actually let you write the record (.update)

let the user change the form - and have a save button (or a cancel)

--QUIZ TOMORROW--

6/18/99
=======

Saving data to a recordset is a 3-step process:

1) Create a copy buffer  (with edit or addnew method)
2) assign values to a field
3) write copy buffer to a recordsource (w / update method)


Lname$ = CustRS("Last Name") '* anytime there is a current record


allow for an edit button and a save button


6/21/99
=======

Answers to Quiz 1:

Write code to traverse and count all of thbe employees in the employee table. You must
create a recordset with data access (database and recordset) objects, not a data control.
Close your access objects when finished. Just write code, not an event proceedure,. Assume
the name of the database is A:\Company.mdb

Recordset Objects
=================

Dim ComDB as Database
Dim EmpRS as Recordset
Dim Count as Integer
set CompDB=opendatabase ("A:\Company.mdb")
set Emprs=comdb.openrecordset ("Employee", dbopentable)
count=0
emprs.movefirst '*no .recordset
do while not emprs.eof
	count=count+1
	emprs.movenext
loop
emprs.close
compdb.close


don't use a find first to do this - it would give you all the results in a recordset
already.

A user interface has a command button, cmdopen, and a data control, datemp.  Write
an event proceedure to create a recordset of all employees in the employee table
when the user clicks on the command button.  Also assume that the name of the
database is stored in the variable dbname, and that data control has none of its data
properties set at design time.


Data Control
============

sub cmdopen_click()
datemp.databasename=dbname
datemp.recordsource="employee"
datemp.recordsettype=0 'dbopentable?
datemp.refresh  'closes any open recordset and opens a new one as specified
                'errors in those lines will ONLY show up on that line
end sub

Write a piece of code that computes an employees weekly pay, assuming the employee
worked a 40 hour week.  Compute this value for the employee who is the current record
of EmpRS, a recordset object, and store the result in a variable.

dim TotalPay as Currency
TotalPay=40*EmpRS("Pay Rate")


Write code that searches for the first occurence of an employee who works in the
marketing department, informing the user with a message box if such an employee
is not found.  Assume that there is an open DYNASET type recordset referenced by
the recordset object EmpRS.

Dynaset / Snapshot
==================

Dim Crit$
Crit$ = "Dept = 'marketing'"
EmpRS.Findfirst crit$
if EmpRS.Nomatch then
	msgbox "No Marketing Dept. Workers were found."
end if

use data access objects or if a name is given...
(emprs instead of datemployee)

remember a bookmark - to store your current record before / during / everything. :)


6/23/99
=======

Some review items for the test:

review quiz!!

Fundamentals of the database:

	your table needs an index - it orders the recordset by that index, and you search
for it by a key.

	recordsets:
		define them
		talk about types of recordsets
		be able to reconize which type to use when (review table)
	data controls and bound controls
	
you can connect to several different datasources - which gives you the power
with the visual basic frontend.

overview of the bound control - et cetera...2 things it does:

open database.
opens recordset.

(must know database name and data source)

2 things a data control does:

"current record"
data properties

one text box has information from one field of a current recordset.


navigating in code:
	move methods

searching:
	find / seek method

adding, editing, and deleting:
	
processing all the records of a recordset with a while not eof file loop.

creating a recordset? - at runtime:

	with a data control
	without a data control


lookup how to open the database manually! ;-)

various searches? - using the find and the seek. warning the user, checking the nomatch.

editing, adding, deleting - doing modifications...

bookmark property. :)

(like initializing everything to 0 in the players table)

validate and reposition events? : 

	they're data control events
	they fire when the currnet record changes
	one fires before, one fires after.

if you wanted to format the bound controls, or format information as it comes in,
you might grab information before it changes and displays it on the interface:

reposition occurs AFTER the record changes.  validate occurs BEFORE.

if you wanted to display and show total homeruns, and you're locating
mcquire, you must write the summary code in reposition - not validate!

the update method also causes validate to fire..?

(do lost focus events, too - to make things more efficent)

page 716 - find / seek

setting current index....

recordcount property will also count.  (you must first populate the entire recordset using
a movelast).  You might want to do a movelast and movefirst in the form_activate event.

write an event proceedure, or just a piece of code.



6/28/99
=======

SQL langage - (starts with 'select') - next assignment comes Wednesday or so...?

SELECT fieldnames FROM tables (could be a complete statement)

might need to use players.team, or team.team (if a join is involved,
of if we are utilizing multiple tables)

Where Clause: - matches criteria, it's a filter.

example:

Where City = 'New Orleans' - literal needed, New Orleans is a Text Field.

if literal:

S =  "Select...WHERE City='Jacksonville'; " 

if dynamic:

S = "Select...WHERE City'" & txtCity & "'"

at design time, right click the grid...say retrive fields.
it'll show you current data...

it's suggested to resize the dbgrid like we resized the text
box intially in ite285.

page 773 - the desc puts it in descending order.

Select Team, Max(HR) as total From Player
Group By Team

question one:

? ? 

SQL$ = "SELECT Team, HR, count (HR) as total from player" _ 
       & "GROUP BY Team Having HR=0

6/29/99
=======

Recordcount is ONLY good once ALL the records have been viewed!

(in slide # 8)

Slide 9....team total...is a computed field. (it's only temporary)

Where clause determines which records are included in the recordset.
This is used to exclude rows you might not want grouped at all.

The Groupby does the grouping, and it uses "have" to group out THOSE
records.

slide #12 - "txtcity" - you don't want literal quotes there. that
would bug on the refresh. ;-(

6/30/99
=======

More detail on lab we were going over in class.

Example:

S$ = "SELECT Team.Location, Team.Nickname, Sum(Player.HR) As Total " _
       $ "FROM Player,Team " _ 
       $ "WHERE Player.team=team.(team code) " _
       $ "Group by Team " _
       $ "HAVING Sum(HR)>100 " _
       $ "ORDER BY Sum(HR) DESC"
Msgbox $
datPlayer.Recordsource=S$
datPlayer.Refresh

Definitions of statements:

Order By: Sorts records in a recordset
Group By: Combines records that have identical values in group by field
Where: Filters records BEFORE the grouping (have to have "where before grouping")
Having: Filters records before grouping. Works on it after it hsa been grouped?

7/1/99
======

In the reservation table, "guest id" and "site id" are foreign keys.

Don't make the reservation interface look like the campsite interface ID!!

Go to something like a screen asking for a reservation thinggie. click on the
reservation button to make one.

interface should contain combobox.... :)

remeber "don't care", too? (beach, not care  ; et cetera...)

query for this.

query the site table to see what sites can fit what we are looking for.

then see if those sites are available on all of the dates that the camper
wants the dates.

assume the guest already exists in the table.

read up on date type. (comparisons, et cetera)

dblist and dbcombo controls:

(player vs team table)

player		team
======		====
id		team code
name		location
team 8		nickname
hr

store an 8 to point to the team...  ? 

user might know location or nickname...

work around a problem - put up a combobox including all the nicknames
write an additem to write each item into the combobox. (huh?)  <groan>

dblist , dbcombo control - if you want to select a team control to put
in the player table, (look on page 713), choices might come from another
recordset (which include additional properties) - that's what the dbcombo
is.

rowsource, bound column, and list field.

rowsource is the name of the 2nd data control
bound column is the team code.
list field - the one displayed in the list (nickname)

7/2/99
======

Quiz 3 - Answer, Number One:

datEmp.Databasename = "C:\company.mdb"
datEmp.Recordsource = "SELECT * FROM Employee " _
     & "WHERE Dept = 'Accounting'"
dbQuery.Datasource = "datEmp"  <<-- must be done at DESIGN TIME!
dbQuery.Refresh

datPlayer.Databasename = "C:\players.mdb"
datPlayer.Recordsource = "SELECT * Player " _
     $ "WHERE Team = 8"
datPlayer.Refresh

Binding has to be done at DESIGN TIME.
It can't be done during RUN-TIME!


Quiz 3 - Answer, Number Two:

datPlayer.Recourdsource="SELECT Avg([HR]) As AvgHR FROM Player " _
            & Group By Team"

dbcombo - will import the options from the table. (instead of additem!)



7/7/99
======

ITE-370
Where are we going?

We've covered so much - from bound controls to SQL queries. Next,
we're going to be doing reports - 'specially Crystal Reports

Crystal Reports has the ability to create reports based on information
entered.  (Chapter 26 - p 693?)

Networks and Locking Issues - Chapter 31.  We're going there, too. :)

Connecting to Remote Datasources. (connect to an oracle database in
the other building)

obdc - open database compatability.

Other Topics - including using classes ; active x ; dll's ; and the
windows API.

enter special for the date...don't save a reservation with a camper
id that is not in the camper table.

convert incorrect data types.

in making a reservation:

find sites by given criteria (beach, wooded, site type)
resolve conflicts.
handles no-available site situation.

keep old data on repeat query.

report appropriate information - (name, address, et cetera, of camper...)
start and end dates.

do you see the entire site detail...

[where (status="active" AND ...)

use masked edit control.

Check into a site - filter by SSN and Status = IN

for checking out - show approphiate info - cost, et cetera.
[use masked edit!]
if you check out after the pre-approved check out date, notify
what is owed...late check out.

lway has a reservation. but , if he has a record already, you're
simply changing the STATUS of the record. [don't add another record]

do little by little...






conformation of cancel... :)

make current record - do check in. [for bonus, to check them in] :0-)



as for seek - why not do a while not eof ? 

7/8/99
======

Dim PlayerDB as Database
Set PlayerDB=Open Database ("C:\Players.mDb")
PlayerDB.Execute "Select 

Give me all the sites that Elway might want.
Write as available sites in the Reservation table
write like....id #.

what can be done with a querydef object?

to do querys, you need to have physical tables.
temporarily create a talbe containing the info you need,
then use the delete statement to take it back out.


3. Let SQL$="Select ID FROM Vacation " _ 
   & "WHERE # " & BeginDate & "# > FINISH " _
   & " OR #" & EndDate & "# < Start " 

1....3
      4...6
           7...10


7/16/99
=======

In order to "trap" errors as they occur, use:

on error goto errorhandler  (label)

.
.
.

exit sub
Lockhandler:
error-handling code
resume

line labeler:
Errorhandler: (name with colen)

label would have to be within the scope of the proceedure

bad (on error, resume next?)

3 types of locking:

database locking
recordset locking
page / record locking


form activate -->> where you put the lockedits command

to make a optimistic "mode" - rsEmployees.LockEdits = False


error object:

err has several properties. (source, descrip[tion, error #)


test topics:
============

Write code using SQL.  
dbopendynaset...

data source / refresh

where city = 'mobile'

where city = '"city'" or whatever...

selects where 
relational operators

how do you bind a dbgrid to a data control  (only at designtime!)

how does a dbcombo work

why is a dbcombo useful ?  - it represents two table relationships,
primary to secondary key.

requires two data controls.

multi-user issues:

locking schemes - study them, et cetera...

7. - nice essay. :)


crystal report - examine 4-5 pages in book in that. :)

don't know select into

MOST QUESTIONS - might be:

sql and multi user.

7/21/99
=======

Help with program due friday:

page 695-696

Use:

rptmember.selectionformula="(table.field)" = & txtfield

new information:
remote data connectivity:

page 792

7/22/99
=======

Windows API  -   Applications Programming Interface

Visual Basic Proceedures - call the windows API - in order to draw
items on the screen, et cetera.

It's more or less a systems function.

API's usually have a LOT of parameters!

In VB you need to have a declare statement, too, (if it's not already
in your program as a VB function)

Built in tool - API text viewer

Load a help file....and select something

constants....declration of proceedures, and definitions of data types

...whatever comes up in selected file, it comes up with a declaration.
This will go in VB as a code module, or as a general declaration section

goes out and makes a reference to a particlar file at a particlar location.

7/23/99
=======

Machine Language Code - specific to winders - is saved as a *.DLL file.  


Static Linking - you might have two functions in the same place, that might
take up extra space.

Dynamic Linking - vb source code is compiled in executable statements, and
format is called. Instead of copying format over as we do in static , we make
a reference to it in our library.

The VB program needs to know how to access the code which is already there.


API - application program interface. In VB, we deal with a bunch of objects,
such as forms and text boxes. These actually typically make references to 
windows objects, like the form and text boxes.  These are basically "C" 
data types and objects, which are implemented by compiling them into DLL's.

check out: http://skyscraper.fortunecity.com//transmission/45/api/ 



format function in vb references something out in vb.


7/27/99
=======

Object Oriented Programming in Visual Basic

Principals:

Polymorphish - 
Encapsulation  (information hiding) - using pre-written code to accomplish tasks.
Inharitence - using objects which inherit the characteristics of other objects.

There are two types of objects we can create:

control objects (text boxes, labels) and code objects (recordset object)

Objects and CLASSES - 

Object - a basic program element consisting of 3 things:
properties, methods, and events.

class - analygous of a data type in an object instance - similar to
a variable. Defines what is common to that instance of that class.

to declare an object:
=====================

private objectname as classname

example:

private Employee as CEmployee

then, use the set statement to init object:
(inside a proceedure)

set objectname = new classname

example:

set objectname = new CEmployee


To define in visual basic:

1. Project ; Add Class Module
these will eventually get written out as a file, and can be shared by 
multiple people just by referencing it.

2. In Add Class Module, double click class module

3. Look in the properties window, change name of class.


eventually, go to tools, add proceedure...

let - is used to assign a value in code


7/28/99
=======

A class is a TEMPLATE for creating an object.
An object is the varable, the CLASS is the data type.
Conventions: Cname

Events:

Class modules code window has two choices in object drop-down box.  General
and class.  Class has two pre-defined events - initialize and terminate.
(Class_Initialize, and Class_terminate). (similar to form_load, et cetera....)

(these are like the constructor and destructor)

Initialize fires when you create an instance of that class.

(set object name = new class type)
You might assign default values to the properties here, and referencing any
other objects.

Terminate - fires sometime. We're not going to cover this. :)

Other important thing to know about events: WHEN IT'S FIRED. :)

You can define other events besides these.

Steps for creating events:

1. Statement -> Public Event in general declaration of class module.

Example: public event (user defined name) (arguments or parameters)

2. The Satement RAISE event - should be placed in class module code in
place where you want the event to be fixed. (fires the statement event)

Example: Raise event (user defined name) (arguments or parameters)
this might go in the method code:  Raise Event Change

3. When you declare your object, include the with events classes, private
withevents. If you want to be able to have the events with the methods,
use this line:

Private WithEvents object1 as Cclassname

4. Code the event in the form module.  

private sub object_1user_defined_event (parameters)