Transcript Table 2.1 Simple Visual Basic Data Types

Database vs. DBMS

Database


A collection of organized, related tables (data
stored in rows and columns)
Database Management System


Also known as DBMS
Software used to create and maintain a database
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Entity-Relationship Diagram

Also known as ERD


Graphical representation showing the
relationships of entities in a database
Entities
 things
of interest to business (products, employees,
suppliers, customers, purchases, sales,…)

Relationships
 real-world
associations between entities
– products purchased by customers
– employees who designed a particular part
– suppliers who provided a particular raw material
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
ERD Cardinality
Related to one
Related to one or zero (optional)
Related to zero or more
Related to one or more
Student
Course
Teacher
Each course belongs to a specific teacher (manager)
A teacher may teach one or more courses
A student can sign up for one or more courses
A course may have no students (not offered)
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Normalized Databases

Normalization



A process of organizing database tables to increase stability
through reduced (controlled) data redundancy.
Higher normal forms further restrict how attributes are
grouped within records
3NF (Third Normal Form) is the generally accepted,
minimum standard for designing a database

Every nonkey field is directly related to only to the entire key
field and nothing else
– {Emp # (key), Emp Name, Dept Code, Dept Name} is not in
3NF since Dept Name can be derived entirely from dept code
(both non-key fields)
– {Prod # (key), Prod Name, Qty, Cost, ValueinInventory} is not
in 3NF since ValueInInventory is a calculated value (from Qty
& Cost)
– {RawMat # (key), Prod #, AmtReqrd} is not in 3NF since
AmtReqrd is derived from both RawMat# and Prod #
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Database Terminology
Northwind DB tables
Second
record or row
of Categories
table
describes
condiments
Key fields
Relationship based on shared data in two tables
Records 3,
6 8 are
condiments
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Databases Accessible by VB

VB Native format

Microsoft Jet database engine
 Microsoft Access

Jet engine can also access


Excel, dBASE, FoxPro, Lotus, Paradox, text files
VB Professional with Open DataBase
Connectivity (ODBC) drivers

Can also access SQL Server, Oracle, and DB2
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Data Control

Provides access to data stored in
databases using any one of three types of
Recordset objects:





data
control

table
snapshot
synaset
Enables data navigation viewed through
bound controls.
Without a Data control, data-bound
controls on a form can't automatically
access data.
Validate event occurs just before new
record becomes current.
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Data Control Properties

Caption


Connect


Defaults to False & must be set to true if user is not allowed to
change contents
RecordSource


Assigns the database filename with path (use dialog)
ReadOnly


Identifies DBMS source of database (Access)
DatabaseName


Text that appears inside the control on the form
Assigns the table or query (view) of the database to be used by the
VB program
BOF and EOF

Flags set to true when current record pointer positioned before first
record or after last record
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Data-Bound Controls

Same controls we’ve used thus far with additional
properties set


DataSource


Labels (user cannot change), Textboxes (user can change),
Checkboxes (T/F or Y/N), etc.
point to database (specify name of data control)
DataField

field name in database table that relates to this
It is possible to navigate data in a database simply by designing a form
with the data control and binding data-aware controls to the data
control -- no coding is needed!
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Programming the Data Control

Recordset Object


Accessing Recordset properties in code



Defined by setting RecordSource property of data
control
datName.Recordset.Property
Since this represents a value, it can only be part of
a statement (e.g., assign it to variable or assign
something to it)
Applying methods to Recordset object


datName.Recordset.Method
Since this represents a process, it is written like a
statement
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Recordset Properties

Checking the database pointer

datName.Recordset. BOF


datName.Recordset. EOF


datName.Recordset.RecordCount
Setting or tracking the current DB record


Flag that is true when record pointer is after last database
record (select next on last record)
Counting the number of DB records


Flag that is true when record pointer is before first database
record (select previous on first record)
datName.Recordset.BookMark
Determining if desired record was found

datName.Recordset.NoMatch
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Recordset Move & Refresh Methods

Navigating DB via Code

Move to next database record
 datName.Recordset.MoveNext

Move to last database record
 datName.Recordset.MoveLast

Move to previous database record
 datName.Recordset.MovePrevious

Move to first database record
 datName.Recordset.MoveFirst

Refresh method

Explicitly opens the database
 datName.Refresh
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Adding a First button
cmdFirst_Click
Move to db record 1
Private Sub cmdFirst_Click()
datEx.Recordset.MoveFirst
lblRecNum.Caption = 1
End Sub
RecNum = 1
End
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Adding a Last button
cmdLast_Click
Move to last db record
Private Sub cmdLast_Click()
datEx.Recordset.MoveLast
lblRecNum.Caption = _
datEx.Recordset.RecordCount
End Sub
RecNum = RecordCount
End
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Adding a Next button
cmdNext_Click
Private Sub cmdNext_Click()
Move to next db record
False
True
EOF?
RecNum=RecNum+1
Display already at
end - go to start
cmdFirst_Click
datEx.Recordset.MoveNext
If datEx.Recordset.EOF Then
Call MsgBox(“msg”,…)
Call cmdFirst_Click
Else
lblRecNum.Caption = _
CInt(lblRecNum.Caption)+1
EndIf
End Sub
End
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Adding a Previous button
cmdPrevious_Click
Private Sub cmdPrevious_Click()
datEx.Recordset.MovePrevious
Move to previous db record
If datEx.Recordset.BOF Then
False
True
Call MsgBox(“msg”,…)
BOF?
Display already at
Call cmdLast_Click
start - go to end
Else
RecNum=RecNum-1
cmdLast_Click
lblRecNum.Caption = _
CInt(lblRecNum.Caption)-1
EndIf
End Sub
End
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Recordset Find Methods


Search for a specific type of record and then move to
it in the specified direction.
Specifying search criteria
<DBFieldName> <relational operator> <expression>

Find methods define starting point & search direction

Start at first record and search forwards


Start at last record and search backwards


datName.Recordset.FindLast (search criteria)
Start at current record and search forwards


datName.Recordset.FindFirst (search criteria)
datName.Recordset.FindNext (search criteria)
Start at current record and search backwards

datName.Recordset.FindPrevious (search criteria)
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Searching Example
1) Save the current record
CurRecord = datName.Recordset.BookMark
3) Assign search criteria
SearchStr = InputBox(“prompt”, “title”, “default”)
Target = “TableName.FieldName = `” & SearchStr & “`”
3) Use search criteria
Call datName.Recordset.FindFirst(Target)
If datName.RecordSet.NoMatch Then
Call MsgBox(“ERROR”)
Else
‘ whatever processing needs to be done with current record
End If
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Find similar name
Private Sub cmdFindName_Click()
Dim CurRecord As Variant
Dim SearchString As String
' Save location of current record
CurRecord = datEmployee.Recordset.Bookmark
SearchString = "EmpName Like '" & _
txtSearchName.Text & "*'"
Call datEmployee.Recordset.FindFirst(SearchString)
If datEmployee.Recordset.NoMatch Then
Call MsgBox("No such employee", _
vbOkOnly + vbInformation)
' Move pointer to previously saved location
datEmployee.Recordset.Bookmark = CurRecord
Else
' fill display controls with found record’s data
Call ShowDBData
End If
End Sub
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Methods to change DB content

Delete current database record





datName.Recordset.Update
explicitly saves newly entered data
Reset db to previous values

datName.Recordset.Edit
moves current DB record to copy buffer to allow changes to occur
Update database with addition

datName.Recordset.AddNew
clears data bound controls so user can enter new data
DB automatically updated by moving to another record or explicitly
saving record
Edit database record


be sure to add step to go to a valid record
AddNew database record

datName.Recordset.Delete
datName.Recordset.UpdateControls
resets data bound controls to previous values
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Add a new record
Private Sub cmdAddRec_Click()
If cmdAddRec.Caption = "&Add" Then ' 1) Clear input controls
Call DisableNavigation
Call datEmployee.Recordset.MoveLast
txtID.Text = Format(datEmployee.Recordset!empid + 1)
Call datEmployee.Recordset.AddNew
txtEmpName.Text = ""
txtBirthdate.Text = ""
txtPayRate.Text = ""
' Change caption to Save and disable other buttons
cmdAddRec.Caption = "&Save"
Call txtEmpName.SetFocus
Else ' the caption must be &Save
' 2) Write user input to DB
Call WriteDBData
datEmployee.Recordset!empid = txtID.Text
Call datEmployee.Recordset.Update
' Change caption to Add and enable other buttons
cmdAddRec.Caption = "&Add"
Call EnableNavigation
End If
End Sub
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Delete a record
Private Sub cmdDeleteRec_Click()
Dim CurRec As Variant
CurRec = datEmployee.Recordset.Bookmark ' The record to delete
datEmployee.ReadOnly = False
Call datEmployee.Refresh
Call datEmployee.Recordset.MoveLast
datEmployee.Recordset.Bookmark = CurRec
Call datEmployee.Recordset.Delete
datEmployee.ReadOnly = True
Call datEmployee.Refresh
' Move to the last record
Call cmdLast_Click
If datEmployee.Recordset.EOF Then
' Last record was deleted
Call cmdFirst_Click
If datEmployee.Recordset.BOF Then ' No records left
Call MsgBox("There are no records left-the database" & _
" is empty", vbOkOnly + vbInformation)
End If
End If
End Sub
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Example: Change a record
Private Sub cmdModifyRec_Click()
' The current record will be the one to be modified
If cmdModifyRec.Caption = "&Modify" Then ' 1) Allow edit of record
Call DisableNavigation
Call datEmployee.Recordset.Edit
' Change caption to Save and disable other buttons
Call txtEmpName.SetFocus
cmdModifyRec.Caption = "&Save"
cmdDeleteRec.Enabled = False
cmdAddRec.Enabled = False
Else ' the caption must be &Save
' 2) Copy updates to DB
Call WriteDBData
Call datEmployee.Recordset.Update
' Change caption to Modify and enable other buttons
cmdModifyRec.Caption = "&Modify"
cmdDeleteRec.Enabled = True
cmdAddRec.Enabled = True
Call EnableNavigation
End If
End Sub
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Preventing Errors


Add code to the Validate Event of data control
Add error handlers in all procedures that access DB
Examples:
 The primary key must
be non-blank
 All data entered must
be of correct type and
length
 The database table has
valid records (not an
empty table)
Pseudocode/Algorithm
If a required field is empty
and both BOF and EOF
are False Then
Show error msg
Reset error
condition so
program can run
Don’t save changes
Endif
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Checking for Empty Recordsets
If BOF and EOF Then
Display error message
End If
 By checking for both BOF & EOF, you are
checking for a recordset with no members
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Queries: Joining DB tables
1) Define query to get all unique field names from both tables
Query = “Select * From TableName1, TableName2 Where ” & _
“ TableName1.FieldX = TableName2.FieldY”
Query = “Select TableName1.FieldA, TableName1.FieldB, “ & _
“TableName2.FieldZ Where TableName1.FieldX = “ & _
“TableName2.FieldY”
2) Assign Query to RecordSource property & Open the DB
datName.RecordSource = Query
Call datName.Refresh
3) Start at the first record
Call datName.Recordset.MoveFirst
4) Assign value in current DB field to control on form
lblName.Caption = datName.Recordset(“FieldName”)
lblName.Caption = datName.Recordset!FieldName
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Filling list with DB field choices
1) Get filename from user (common dialog)
2) Assign user’s file selection to DatabaseName property of data
control
3) Define query to get all unique field names
Query = “Select Distinct FieldName From TableName”
datName.RecordSource = Query
4) Use Refresh method to open the database
Call datName.Refresh
5) Clear the list & add any general choices (“Show All”)
6) For each item in the database, add the item to the list and go to
next item until done
Call cboName.AddItem(datName.Recordset(”FieldName"))
Call cboName.AddItem(datName.Recordset!FieldName)
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach
Returning select DB records
1) Define query using user’s selection
a) General “Show All” choice: Query = “Select * From TableName”
b) Otherwise: Query = “Select * From TableName Where “ & _
“FieldName = `“ & cboName.Text & “`”
2) Assign Query to data control
datName.RecordSource = Query
3) Use Refresh method to open the database
Call datName.Refresh
4) If flexgrid is used on the form, it will automatically contain all
fields of the desired records based on the search criteria
© 1999, by Que Education and Training, Chapter 10, pages 511-545 of
Introduction to Computer Programming with Visual Basic 6: A Problem-Solving Approach