|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Week 2
  - In Review
    - Day 8: Selecting Data with SQL
    - Day 9: Visual Basic and the Microsoft Jet Engine
    - Day 10: Creating Database Programs with Visual Basic Code
    - Day 11: Displaying Your Data with Graphs
    - Day 12: Data-Bound List Boxes, Grids, and Subforms
    - Day 13: Creating Databases with SQL
    - Day 14: Error Handling in Visual Basic 5

## Week 2

## In Review

Week 2 concentrated on topics that are of value to developers in the standalone and workgroup environments. A wide variety of topics were covered in Week 2, including

- How to use Visdata to build and manage databases

- How to use the Structured Query Language (SQL) to extract data from existing databases

- What the Microsoft Jet engine is, and how you can use Visual Basic code to create and maintain data-access objects

- How to create data entry forms with Visual Basic code

- How to use the Microsoft graph control to create graphs and charts of your data

- How to use data-bound list boxes, data-bound combo boxes, and data-bound grids to create advanced data entry forms

- How to make applications more solid with error trapping

Here is a more detailed look at the topics covered in each lesson:

### Day 8: Selecting Data with SQL

You learned in Day 8 how to create basic SQL statements that select data from existing tables. You learned that the most fundamental form of the SQL statement is the `SELECT_FROM` clause. This clause is used to select one or more columns from a table and display the results of in a result set, or view.

You also learned about the optional clauses that you can add to the `SELECT_FROM` clause:

- The `WHERE` clause: Used to limit the rows in the result set using logical comparisons (for example, `WHERE Table.Name = "SMITH"`) and to link two tables in a single, nonupdatable, view (for example, `WHERE Table1.Name = Table2.Name`).

- The `ORDER BY` clause: Used to control the order in which the result set is displayed (for example, `ORDER BY Name ASC`).

- The `GROUP BY` clause: Used to create a subtotal result set based on a break column (for example, `GROUP BY Name`).

- The `HAVING` clause: Used only with the `GROUP BY` clause, the `HAVING` clause acts as a `WHERE` clause for the `GROUP BY` subtotal clause (for example, `GROUP BY Name HAVING SUM(SalesTotal)>1000`).

- The `INNER JOIN` clause: Used to join two tables together into a single, updatable result set. The `INNER JOIN` returns rows that have a corresponding match in both tables.

- The `LEFT JOIN` and `RIGHT JOIN`: Used to join two tables into a single, updatable result set. The `LEFT JOIN` includes all records from the first (left-hand) table and all rows from the second table that have a corresponding match. The `RIGHT JOIN` works in reverse.

- The `UNION` clause: Used to combine two or more complete SQL queries into a single result set (for example, `SELECT * FROM Table1 UNION SELECT * FROM Table2`).

- The `TRANSFORM_PIVOT` clause: Used to create a cross-tab query as a result set (for example, `TRANSFORM SUM(MonthlySales) FROM SalesTable GROUP BY Product PIVOT Month`).

You also learned about additional SQL keywords that you can use to control the contents of the result set:

- `BETWEEN_AND`

- `DISTINCT` and `DISTINCTROW`

- `AS`

- `TOP n` and `TOP n PERCENT`

- `AVG`, `COUNT`, `MAX`, `MIN`, and `SUM`

### Day 9: Visual Basic and the Microsoft Jet Engine

In Day 9's lesson, you learned the features and functions of Visual Basic Microsoft Jet data-access objects and ODBCDirect access objects. These objects are used within Visual Basic code to create and maintain workspaces, databases, tables, fields, indexes, queries, and relations. You learned the properties, methods, and collections of each object. You also learned how to use Visual Basic code to inspect the values in the properties, and how to use the methods to perform basic database operations.

### Day 10: Creating Database Programs with Visual Basic Code

On Day 10, you learned how to write data entry forms using Visual Basic code. These topics were covered: record search routines, the creation of a procedure library to handle all data entry processes, and creating a working data entry form for the CompanyMaster project.

You learned how to perform single-record searches using the three search methods:

- `Move` for browsing the dataset

- `Seek` for indexed table objects

- `Find` for non-table objects (Dynasets and Snapshots)

You created an OLE Server library to handle adding, editing, deleting, reading, writing, and locating records in datasets. These routines were written as a generic DLL that can be inserted into all Visual Basic programs you write in the future.

You used the new library to add a new form to the CompanyMaster database project. This new form reads a dataset and allows the user to update and browse the table. This new data entry form was built using fewer than 30 lines of Visual Basic code.

### Day 11: Displaying Your Data with Graphs

On Day 11, you learned how to use the graph control that ships with Visual Basic 5 to create visual displays of your data tables. You learned how to add the control to your project and how to load the graph control with data points, titles, legends, and labels.

Also, you built a graph ActiveX DLL object library that you can use to display virtually any dataset in a variety of graph formats. This library lets you save the graph to disk, send the graph to the printer, or copy the graph to the Windows Clipboard for placement in other Windows programs through the Paste Special operation.

While building the graph library, you learned how to declare and use enumerated constants to improve the readability of your Visual Basic code.

Finally, you used the new graph library to add three graphs to the CompanyMaster project.

### Day 12: Data-Bound List Boxes, Grids, and Subforms

On Day 12, you learned how to load and use three of the data-bound controls that are shipped with Visual Basic 5:

- The data-bound list box

- The data-bound combo box

- The data-bound grid

You learned how to link these new controls to Recordsets using the Visual Basic 5 data controls and how to use these links to update related tables.

You also learned several of the important Visual Basic 5 events associated with the data grid. These events let you create user-friendly data entry routines using just a data control and the data grid.

Finally, you drew upon your knowledge of data grids, SQL, and form layout to design and implement a data entry subform. This form showed a master table at the top, and a related list table at the bottom of the form in a data-bound grid.

### Day 13: Creating Databases with SQL

On Day 13, you learned how to create, alter, and delete database table structures using DDL (Data Definition Language) SQL keywords. You also learned that using DDL statements to build tables, create indexes, and establish relationships is an excellent way to automatically document table layouts. You learned how to maintain database structures using the following DDL keywords:

- `CREATE TABLE` enables you to create entirely new tables in your existing database.

- `DROP TABLE` enables you to completely remove a table, including any data that is already in the table.

- `ALTER TABLE` enables you to `ADD` a new column or `DROP` an existing column from the table without losing existing data in the other columns.

- `CREATE INDEX` and `DROP INDEX` enable you to create indexes that can enforce data integrity or speed data access.

- The `CONSTRAINT` clause can be added to the `CREATE TABLE` or `ALTER TABLE` statement to define relationships between tables using the `FOREIGN KEY` clause.

### Day 14: Error Handling in Visual Basic 5

The final lesson of the second week covered all the basics of creating your own error-handling routines for Visual Basic applications. You learned that an error handler has three basic parts:

- The `On Error Goto` statement

- The body of the error handler code

- The error handler exit

You learned that an error handler has four possible exits:

- `Resume`: Re-executes the code that caused the error.

- `Resume Next`: Continues processing at the line immediately following the line that caused the error.

- `Resume label`: Continues processing at the location identified by the label.

- `EXIT` or `END`: `EXIT` ends processing for the current routine and `END` exits the program completely.

You learned how to use the `Err.Raise` method to flag errors without resorting to modal dialog boxes.

You learned about the major types of errors that you are likely to encounter in your program:

- General file errors: These include errors such as `File not Found` and `Invalid Path`. Errors of this type can usually be fixed by the user and then the original procedure re-attempted. Use `Resume` as an exit for these types of errors.

- Database errors: These include errors related to data entry mistakes, integrity violations, and multiuser-related errors, such as locked records. Errors of this type are best handled by allowing the user to correct the data and attempt the operation again. If you use the Visual Basic data control, you do not have to write error handlers--the data control handles them for you. For operations that do not use the data control, you need to write your own error-handling routines.

- Physical media errors: These errors relate to device problems, such as unresponsive printers, downed communications ports, and so on. Sometimes users can fix the problems and continue (for example, refilling the paper tray of the printer). Other times, users cannot fix the problem without first exiting the program. It is a good idea to give users the option of exiting the program safely when errors of these types are reported.

- Program code errors: These errors occur because of problems within the Visual Basic code itself. Examples of program code errors include `Object variable not Set` and `For loop not initialized`. Usually the user cannot do anything to fix errors of this type. It is best to encourage the user to report the error to the system administrator and then exit the program safely.

You also learned that you can declare a global error handler or a local error handler. The advantage of the global error handler is that it allows you to create a single module that handles all expected errors. The disadvantage is that, because of the way Visual Basic keeps track of running routines, you are not able to resume processing at the point the error occurs once you arrive at the global error handler. The advantage of the local error handler is that you are always able to use `Resume`, `Resume Next`, or `Resume label` to continue processing at the point the error occurs. The disadvantage of the local error handler is that you need to add error-handling code to every routine in your program.

Finally, you learned how to create an error handler object library that combines local error trapping with global error messages and responses. The error handler object library also contains modules to keep track of the procedures currently running at the time of the error, a process for printing procedure stack dumps to the screen and to a file, and a process that creates an error log on file for later review.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.