1. Introducing Visual Basic and Databases
Preview 1-1
Course Objectives 1-2
Course Requirements 1-3
What is a Database? 1-4
Where Does Visual Basic Fit In? 1-6
Building a Visual Basic Application 1-8
Structure of a Visual Basic Application 1-9
Steps in Developing Application 1-10
Drawing the User Interface and Setting Properties 1-11
Setting Properties of Controls at Design Time 1-17
Setting Properties at Run-Time 1-19
How Names are Used in Object Events 1-20
Writing Code 1-21
Review of Variables 1-22
Visual Basic Data Types 1-23
Variable Declaration 1-24
Example 1-1. Mailing List Application 1-27
Summary 1-36
2. Introduction to Databases
Review and Preview 2-1
Database Structure and Terminology 2-2
Relational Databases 2-4
Using SQL Server Databases 2-6
Sample Relational Database 2-7
Sample Database Structure 2-10
Virtual Database Tables 2-12
Creating a Database 2-15
Summary 2-16
3. Database Connection
Review and Preview 3-1
Data Object Preview 3-2
Connection Object 3-3
Connection Object – Access Database 3-4
Connection Object – SQL Server Database 3-5
Example 3-1. Accessing the Books Database 3-6
Command Object 3-12
Command Object – Access Database 3-13
Command Object – SQL Server Database 3-13
Example 3-1 (Command Object). Books Database 3-14
DataAdapter Object 3-17
DataAdapter Object – Access Database 3-18
DataAdapter Object – SQL Server Database 3-19
DataSet Object 3-20
DataTable Object 3-21
DataRow Object 3-22
Example 3-1 (Data Table). Books Database 3-23
Data Bound Controls 3-25
Example 3-1 (Data Binding). Books Database 3-27
CurrencyManager Object 3-33
Example 3-1 (Final Version). Books Database 3-35
Note to Visual Basic 2008 Users 3-42
Data Wizards 3-43
Example 3-2 (Access Database). Books Database with Wizards 3-44
Example 3-2 (SQL Server Database). Books Database with Wizards 3-55
Summary 3-67
Example 3-3. Northwinds Trader Database 3-68
4. Database Queries with SQL
Review and Preview 4-1
SQL Background 4-2
Basics of SQL 4-3
Where Does SQL Fit In Visual Basic? 4-5
Example 4-1. SQL Tester 4-6
SELECT/FROM SQL Statement 4-14
ORDER BY Clause 4-18
WHERE Clause 4-20
Single Table WHERE Clause 4-21
Multiple Table WHERE Clause 4-27
INNER JOIN Clause 4-32
OUTER JOIN Clause 4-36
Functions with SQL (Access Database) 4-38
Functions with SQL (SQL Server Database) 4-40
SQL Aggregate Functions 4-42
SQL Construction Tools 4-46
SQL Statements with Access 4-47
SQL Statements with the Data Wizard 4-49
Building SQL Commands in Code 4-55
Example 4-2. Searching the Books Database 4-56
Summary 4-66
Example 4-3. Northwind Traders Database 4-67
5. Visual Basic Interface Design
Review and Preview 5-1
Interface Design Philosophy 5-2
Example 5-1. Mailing List Revisited 5-4
Visual Basic Standard Controls 5-5
Form Control 5-6
Button Control 5-8
Label Control 5-9
TextBox Control 5-11
CheckBox Control 5-13
RadioButton Control 5-15
GroupBox Control 5-17
Panel Control 5-18
PictureBox Control 5-19
Example 5-2. Authors Table Input Form 5-20
MessageBox Object 5-28
Example 5-3. Authors Table Input Form (Message Box) 5-33
Application State 5-35
Example 5-4. Authors Table Input Form (Application State) 5-37
Entry Validation 5-42
Key Trapping 5-43
Example 5-5. Authors Table Input Form (Entry Validation) 5-45
Input Validation 5-46
Example 5-6. Authors Table Input Form (Input Validation) 5-48
Error Trapping and Handling 5-52
Example 5-7. Authors Table Input Form (Error Trapping) 5-55
On-Line Help Systems 5-59
Creating a Help File 5-61
Starting the HTML Help Workshop 5-62
Creating Topic Files 5-64
Creating Table of Contents File 5-67
Compiling the Help File 5-72
HelpProvider Control 5-75
Example 5-8. Authors Table Input Form (On-Line Help) 5-77
Application Testing 5-81
Other Controls 5-82
MaskedTextBox Control 5-83
NumericUpDown Control 5-85
TabControl Control 5-87
Toolstrip (Toolbar) Control 5-90
ListBox Control 5-95
ComboBox Control 5-99
DataGridVIew Control 5-100
MonthCalendar Control 5-102
DateTimePicker Control 5-104
OpenFileDialog Control 5-105
SaveFileDialog Control 5-108
Summary 5-111
Example 5-9. Publisher Table Input Form 5-112
Build Interface 5-113
Add Message Box(es) 5-122
Code Application State 5-123
Perform Entry Validation 5-126
Perform Input Validation 5-127
Add Error Trapping and Handling 5-128
Add On-Line Help System 5-129
Application Testing 5-132
6. Database Management
Review and Preview 6-1
Database Management Tasks 6-2
Editing Database Records 6-3
Phone Contact Database 6-5
Example 6-1. Editing Database Records 6-6
Adding Database Records 6-22
Example 6-2. Adding Database Records 6-24
Deleting Database Records 6-30
Example 6-3. Deleting Database Records 6-31
Finding Records in a Database 6-33
Example 6-4. Finding Database Records 6-36
Modifying Records in Code 6-37
Example 6-5. Modifying Records in Code 6-39
Stopping a Database Application 6-41
Example 6-6. Stopping a Database Application 6-43
Example 6-7. Authors Table Input Form 6-47
Additional Navigation Capabilities 6-48
Editing Records 6-50
Adding Records 6-53
Deleting Records 6-55
Stopping the Application 6-57
Example 6-8. Publishers Table Input Form 6-59
Additional Navigation Capabilities 6-60
Editing Records 6-63
Adding Records 6-66
Deleting Records 6-68
Stopping the Application 6-70
Multiple Table Database Management 6-72
Database Keys 6-73
Database Modifications 6-74
Final Application 6-75
Example 6-9. Books Database Management System 6-76
Basic Book Titles Input Form 6-77
Finding Records 6-92
Navigation Information 6-96
Adding Publisher Name 6-97
Adding Publisher Editing 6-107
Modify Publishers Input Form 6-112
Modify Authors Input Form 6-117
Adding Author Names 6-122
Example 6-10. Database Detective – Author Search 6-123
Viewing Author Selections 6-128
Viewing Author Names 6-139
Saving Author Names 6-144
Adding Author Editing 6-148
Input Control Navigation 6-151
Entry and Input Validation 6-154
Titles Form On-Line Help 6-161
Summary 6-165
7. Database Reports
Review and Preview 7-1
PrintDocument Object 7-2
Printing Document Pages 7-5
Pen Object 7-7
Brush Object 7-8
Graphics Methods 7-9
PageSetupDialog Control 7-12
PrintDialog Control 7-15
PrintPreviewDialog Control 7-17
PrintDocument Object with Databases 7-19
Example 7-1. Database Report 7-21
Example 7-2. Titles Listing 7-28
Example 7-3. Book Publishers Listing 7-34
User Interface 7-35
Database Connection and Printing 7-38
Other Approaches to Database Reports 7-47
Summary 7-48
8. Distributing a Database Application
Review and Preview 8-1
Accessing Database Files in Code 8-2
Database File in Application Path 8-3
Example 8-1. Opening Database Files in Application Directory 8-4
Database File Location with OpenFile Dialog Control 8-11
Example 8-2. Opening Database Files with OpenFile Dialog Control 8-13
Distribution of a Visual Basic Database Application 8-20
Applications Icons 8-24
Designing Your Own Icon 8-26
Example 8-3. Visual Basic Setup Wizard 8-29
Step 1. Welcome to the Setup Project Wizard 8-32
Step 2. Choose a project type 8-33
Step 3. Choose project outputs to include 8-34
Step 4. Choose files to include 8-35
Step 5. Create project 8-38
Assigning Icons and Shortcuts 8-42
Debug Versus Release Configurations 8-44
Building the Setup Program 8-45
Installing/Removing a Visual Basic Application 8-47
Summary 8-50
9. Database Design Considerations
Review and Preview 9-1
Database Design 9-2
Database Modeling 9-3
Information Requirements 9-4
Table Requirements 9-6
Field Requirements 9-10
Field Types 9-12
Null Values 9-14
Database Design Implementation 9-15
Building Access Databases with Microsoft Access 9-16
Example 9-1. KWSALES Database with Microsoft Access 9-17
Getting Started 9-18
Customers Table 9-19
Orders Table 9-22
Purchases Table 9-23
Products Table 9-25
Define Relationships 9-26
Building SQL Server Databases with Server Explorer 9-29
Example 9-2. KWSALES Database with Server Explorer 9-30
Getting Started 9-31
Customers Table 9-33
Orders Table 9-37
Purchases Table 9-38
Products Table 9-39
Define Relationships 9-40
Building Access Databases with ADOX Objects 9-44
Example 9-3. KWSALES Access Database with ADOX 9-45
Adding Reference to ADOX Library 9-46
Create a Database 9-47
Create a Table 9-49
Add Fields to Table 9-51
Define Table Primary Key 9-54
Define Table Indexes 9-56
Define Table Relationships 9-61
Example 9-4. KWSALES SQL Server Database with Visual Basic 9-64
Database Testing and Design Refinement 9-68
Summary 9-69
10. Sample Database Projects
Review and Preview 10-1
Overview of Database Projects 10-2
Example 10-1. Sales Order Form Project 10-3
Preliminaries 10-4
Order Information 10-5
Existing Customer Information 10-11
Adding a New Customer 10-21
Product Selection 10-34
Submitting an Order 10-45
Printing an Invoice 10-54
Suggested Improvements 10-57
Example 10-2. Home Inventory Project 10-58
Home Inventory Database 10-59
Preliminaries 10-62
Home Inventory Interface 10-63
Database Connection 10-67
Display Photo 10-72
Database Navigation 10-74
Editing Records 10-77
Load Photo 10-82
Adding Records 10-85
Deleting Records 10-91
Entry Validation 10-93
Input Validation 10-97
Inventory Report 10-99
Stopping the Application 10-104
Suggested Improvements 10-108
Example 10-3. Weather Monitor Project 10-109
Weather Monitor Interface 10-110
Record Weather Data Tab 10-112
Weather Monitor Database (Access) 10-116
Weather Monitor Database (SQL Server) 10-121
Adding Date Values and Editing Features 10-124
Opening Database Files 10-134
Date Display Coordination 10-136
View Temperature Data Tab 10-138
Temperature Summary Statistics 10-145
Temperature Plot 10-151
View Precipitation Data Tab 10-153
Precipitation Summary Statistics 10-157
Precipitation Plot 10-161
Weather Monitor Printed Reports 10-163
Weather Data Report 10-165
Temperature Data Report 10-168
Precipitation Data Report 10-170
Weather Monitor Help System 10-172
Weather Monitor Icon 10-175
Weather Monitor Distribution Package 10-176
Suggested Improvements 10-177
Summary 10-178
11. Other Database Topics
Review and Preview 11-1
Exporting Database Data 11-2
Opening a Sequential File for Output 11-3
Writing Data to a Sequential File 11-4
Saving a Sequential File 11-5
Example 11-1. Exporting Database Data 11-6
Importing Database Data 11-10
Opening a Sequential File for Input 11-11
Reading Data from a Sequential File 11-12
Closing a Sequential File 11-14
Example 11-2. Importing Database Data 11-15
Other Database Types 11-21
ODBC Data Objects 11-22
Oracle Data Objects 11-23
Multi-User Considerations 11-24
Database Web Applications 11-25
Starting a New Web Application 11-26
Web Form Controls 11-29
Building a Web Application 11-36
Example 11-3. Viewing Weather Data 11-39
Summary 11-42
Example 11-4. The Final Application 11-43
