![](/user_photo/_userpic.png)
- •Using Your Sybex Electronic Book
- •Acknowledgments
- •Contents at a Glance
- •Introduction
- •Who Should Read This Book?
- •How About the Advanced Topics?
- •The Structure of the Book
- •How to Reach the Author
- •The Integrated Development Environment
- •The Start Page
- •Project Types
- •Your First VB Application
- •Making the Application More Robust
- •Making the Application More User-Friendly
- •The IDE Components
- •The IDE Menu
- •The Toolbox Window
- •The Solution Explorer
- •The Properties Window
- •The Output Window
- •The Command Window
- •The Task List Window
- •Environment Options
- •A Few Common Properties
- •A Few Common Events
- •A Few Common Methods
- •Building a Console Application
- •Summary
- •Building a Loan Calculator
- •How the Loan Application Works
- •Designing the User Interface
- •Programming the Loan Application
- •Validating the Data
- •Building a Math Calculator
- •Designing the User Interface
- •Programming the MathCalculator App
- •Adding More Features
- •Exception Handling
- •Taking the LoanCalculator to the Web
- •Working with Multiple Forms
- •Working with Multiple Projects
- •Executable Files
- •Distributing an Application
- •VB.NET at Work: Creating a Windows Installer
- •Finishing the Windows Installer
- •Running the Windows Installer
- •Verifying the Installation
- •Summary
- •Variables
- •Declaring Variables
- •Types of Variables
- •Converting Variable Types
- •User-Defined Data Types
- •Examining Variable Types
- •Why Declare Variables?
- •A Variable’s Scope
- •The Lifetime of a Variable
- •Constants
- •Arrays
- •Declaring Arrays
- •Initializing Arrays
- •Array Limits
- •Multidimensional Arrays
- •Dynamic Arrays
- •Arrays of Arrays
- •Variables as Objects
- •So, What’s an Object?
- •Formatting Numbers
- •Formatting Dates
- •Flow-Control Statements
- •Test Structures
- •Loop Structures
- •Nested Control Structures
- •The Exit Statement
- •Summary
- •Modular Coding
- •Subroutines
- •Functions
- •Arguments
- •Argument-Passing Mechanisms
- •Event-Handler Arguments
- •Passing an Unknown Number of Arguments
- •Named Arguments
- •More Types of Function Return Values
- •Overloading Functions
- •Summary
- •The Appearance of Forms
- •Properties of the Form Control
- •Placing Controls on Forms
- •Setting the TabOrder
- •VB.NET at Work: The Contacts Project
- •Anchoring and Docking
- •Loading and Showing Forms
- •The Startup Form
- •Controlling One Form from within Another
- •Forms vs. Dialog Boxes
- •VB.NET at Work: The MultipleForms Project
- •Designing Menus
- •The Menu Editor
- •Manipulating Menus at Runtime
- •Building Dynamic Forms at Runtime
- •The Form.Controls Collection
- •VB.NET at Work: The DynamicForm Project
- •Creating Event Handlers at Runtime
- •Summary
- •The TextBox Control
- •Basic Properties
- •Text-Manipulation Properties
- •Text-Selection Properties
- •Text-Selection Methods
- •Undoing Edits
- •VB.NET at Work: The TextPad Project
- •Capturing Keystrokes
- •The ListBox, CheckedListBox, and ComboBox Controls
- •Basic Properties
- •The Items Collection
- •VB.NET at Work: The ListDemo Project
- •Searching
- •The ComboBox Control
- •The ScrollBar and TrackBar Controls
- •The ScrollBar Control
- •The TrackBar Control
- •Summary
- •The Common Dialog Controls
- •Using the Common Dialog Controls
- •The Color Dialog Box
- •The Font Dialog Box
- •The Open and Save As Dialog Boxes
- •The Print Dialog Box
- •The RichTextBox Control
- •The RTF Language
- •Methods
- •Advanced Editing Features
- •Cutting and Pasting
- •Searching in a RichTextBox Control
- •Formatting URLs
- •VB.NET at Work: The RTFPad Project
- •Summary
- •What Is a Class?
- •Building the Minimal Class
- •Adding Code to the Minimal Class
- •Property Procedures
- •Customizing Default Members
- •Custom Enumerations
- •Using the SimpleClass in Other Projects
- •Firing Events
- •Shared Properties
- •Parsing a Filename String
- •Reusing the StringTools Class
- •Encapsulation and Abstraction
- •Inheritance
- •Inheriting Existing Classes
- •Polymorphism
- •The Shape Class
- •Object Constructors and Destructors
- •Instance and Shared Methods
- •Who Can Inherit What?
- •Parent Class Keywords
- •Derived Class Keyword
- •Parent Class Member Keywords
- •Derived Class Member Keyword
- •MyBase and MyClass
- •Summary
- •On Designing Windows Controls
- •Enhancing Existing Controls
- •Building the FocusedTextBox Control
- •Building Compound Controls
- •VB.NET at Work: The ColorEdit Control
- •VB.NET at Work: The Label3D Control
- •Raising Events
- •Using the Custom Control in Other Projects
- •VB.NET at Work: The Alarm Control
- •Designing Irregularly Shaped Controls
- •Designing Owner-Drawn Menus
- •Designing Owner-Drawn ListBox Controls
- •Using ActiveX Controls
- •Summary
- •Programming Word
- •Objects That Represent Text
- •The Documents Collection and the Document Object
- •Spell-Checking Documents
- •Programming Excel
- •The Worksheets Collection and the Worksheet Object
- •The Range Object
- •Using Excel as a Math Parser
- •Programming Outlook
- •Retrieving Information
- •Recursive Scanning of the Contacts Folder
- •Summary
- •Advanced Array Topics
- •Sorting Arrays
- •Searching Arrays
- •Other Array Operations
- •Array Limitations
- •The ArrayList Collection
- •Creating an ArrayList
- •Adding and Removing Items
- •The HashTable Collection
- •VB.NET at Work: The WordFrequencies Project
- •The SortedList Class
- •The IEnumerator and IComparer Interfaces
- •Enumerating Collections
- •Custom Sorting
- •Custom Sorting of a SortedList
- •The Serialization Class
- •Serializing Individual Objects
- •Serializing a Collection
- •Deserializing Objects
- •Summary
- •Handling Strings and Characters
- •The Char Class
- •The String Class
- •The StringBuilder Class
- •VB.NET at Work: The StringReversal Project
- •VB.NET at Work: The CountWords Project
- •Handling Dates
- •The DateTime Class
- •The TimeSpan Class
- •VB.NET at Work: Timing Operations
- •Summary
- •Accessing Folders and Files
- •The Directory Class
- •The File Class
- •The DirectoryInfo Class
- •The FileInfo Class
- •The Path Class
- •VB.NET at Work: The CustomExplorer Project
- •Accessing Files
- •The FileStream Object
- •The StreamWriter Object
- •The StreamReader Object
- •Sending Data to a File
- •The BinaryWriter Object
- •The BinaryReader Object
- •VB.NET at Work: The RecordSave Project
- •The FileSystemWatcher Component
- •Properties
- •Events
- •VB.NET at Work: The FileSystemWatcher Project
- •Summary
- •Displaying Images
- •The Image Object
- •Exchanging Images through the Clipboard
- •Drawing with GDI+
- •The Basic Drawing Objects
- •Drawing Shapes
- •Drawing Methods
- •Gradients
- •Coordinate Transformations
- •Specifying Transformations
- •VB.NET at Work: Plotting Functions
- •Bitmaps
- •Specifying Colors
- •Defining Colors
- •Processing Bitmaps
- •Summary
- •The Printing Objects
- •PrintDocument
- •PrintDialog
- •PageSetupDialog
- •PrintPreviewDialog
- •PrintPreviewControl
- •Printer and Page Properties
- •Page Geometry
- •Printing Examples
- •Printing Tabular Data
- •Printing Plain Text
- •Printing Bitmaps
- •Using the PrintPreviewControl
- •Summary
- •Examining the Advanced Controls
- •How Tree Structures Work
- •The ImageList Control
- •The TreeView Control
- •Adding New Items at Design Time
- •Adding New Items at Runtime
- •Assigning Images to Nodes
- •Scanning the TreeView Control
- •The ListView Control
- •The Columns Collection
- •The ListItem Object
- •The Items Collection
- •The SubItems Collection
- •Summary
- •Types of Errors
- •Design-Time Errors
- •Runtime Errors
- •Logic Errors
- •Exceptions and Structured Exception Handling
- •Studying an Exception
- •Getting a Handle on this Exception
- •Finally (!)
- •Customizing Exception Handling
- •Throwing Your Own Exceptions
- •Debugging
- •Breakpoints
- •Stepping Through
- •The Local and Watch Windows
- •Summary
- •Basic Concepts
- •Recursion in Real Life
- •A Simple Example
- •Recursion by Mistake
- •Scanning Folders Recursively
- •Describing a Recursive Procedure
- •Translating the Description to Code
- •The Stack Mechanism
- •Stack Defined
- •Recursive Programming and the Stack
- •Passing Arguments through the Stack
- •Special Issues in Recursive Programming
- •Knowing When to Use Recursive Programming
- •Summary
- •MDI Applications: The Basics
- •Building an MDI Application
- •Built-In Capabilities of MDI Applications
- •Accessing Child Forms
- •Ending an MDI Application
- •A Scrollable PictureBox
- •Summary
- •What Is a Database?
- •Relational Databases
- •Exploring the Northwind Database
- •Exploring the Pubs Database
- •Understanding Relations
- •The Server Explorer
- •Working with Tables
- •Relationships, Indices, and Constraints
- •Structured Query Language
- •Executing SQL Statements
- •Selection Queries
- •Calculated Fields
- •SQL Joins
- •Action Queries
- •The Query Builder
- •The Query Builder Interface
- •SQL at Work: Calculating Sums
- •SQL at Work: Counting Rows
- •Limiting the Selection
- •Parameterized Queries
- •Calculated Columns
- •Specifying Left, Right, and Inner Joins
- •Stored Procedures
- •Summary
- •How About XML?
- •Creating a DataSet
- •The DataGrid Control
- •Data Binding
- •VB.NET at Work: The ViewEditCustomers Project
- •Binding Complex Controls
- •Programming the DataAdapter Object
- •The Command Objects
- •The Command and DataReader Objects
- •VB.NET at Work: The DataReader Project
- •VB.NET at Work: The StoredProcedure Project
- •Summary
- •The Structure of a DataSet
- •Navigating the Tables of a DataSet
- •Updating DataSets
- •The DataForm Wizard
- •Handling Identity Fields
- •Transactions
- •Performing Update Operations
- •Updating Tables Manually
- •Building and Using Custom DataSets
- •Summary
- •An HTML Primer
- •HTML Code Elements
- •Server-Client Interaction
- •The Structure of HTML Documents
- •URLs and Hyperlinks
- •The Basic HTML Tags
- •Inserting Graphics
- •Tables
- •Forms and Controls
- •Processing Requests on the Server
- •Building a Web Application
- •Interacting with a Web Application
- •Maintaining State
- •The Web Controls
- •The ASP.NET Objects
- •The Page Object
- •The Response Object
- •The Request Object
- •The Server Object
- •Using Cookies
- •Handling Multiple Forms in Web Applications
- •Summary
- •The Data-Bound Web Controls
- •Simple Data Binding
- •Binding to DataSets
- •Is It a Grid, or a Table?
- •Getting Orders on the Web
- •The Forms of the ProductSearch Application
- •Paging Large DataSets
- •Customizing the Appearance of the DataGrid Control
- •Programming the Select Button
- •Summary
- •How to Serve the Web
- •Building a Web Service
- •Consuming the Web Service
- •Maintaining State in Web Services
- •A Data-Driven Web Service
- •Consuming the Products Web Service in VB
- •Summary
![](/html/611/317/html_ZdvvECfIFa.rIVN/htmlconvd-U1mIhU911x1.jpg)
906 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS
Listing 20.1: A SQL UPDATE Statement
UPDATE dbo.Customers
SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region,
PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND
(Address = @Original_Address OR @Original_Address1 IS NULL AND Address IS NULL) AND
(City = @Original_City OR @Original_City1 IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND
(ContactName = @Original_ContactName OR @Original_ContactName1 IS NULL AND ContactName IS NULL) AND
(ContactTitle = @Original_ContactTitle OR @Original_ContactTitle1 IS NULL AND ContactTitle IS NULL) AND
(Country = @Original_Country OR @Original_Country1 IS NULL AND Country IS NULL) AND
(Fax = @Original_Fax OR @Original_Fax1 IS NULL AND Fax IS NULL) AND
(Phone = @Original_Phone OR @Original_Phone1 IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode1 IS NULL
AND PostalCode IS NULL) AND
(Region = @Original_Region OR @Original_Region1 IS NULL AND Region IS NULL)
The variables with the original values are set when the row is read from the table. Then you can edit the row’s fields through the appropriate interface. This may take a few seconds, or minutes (or an hour, if you decide to take a lunch break before updating the table). There’s always a chance that another user might edit the same row before you commit your changes to the database. If a single field’s value is different from the value we read, it means that the row has been modified since we read it and the UPDATE operation will fail.
In a banking application, a customer’s balance might be $2,000. When a check is cashed, we must subtract the amount of the check from the current balance. But if another teller has already subtracted an amount from the same account, there may not be enough funds to cover both checks. You will learn more about updating tables with SQL statements (as well as stored procedures) in the following chapter.
I should bring to your attention that some of the complexity of the statements is due to the fact that Null values can’t be compared. If two values are Null, they’re not equal. The very essence of the Null value is to indicate that a field doesn’t have a value, and therefore can’t be compared. To find out whether two fields are both Null, you must use an expression like the following:
@value1 IS NULL AND field1 IS NULL
The Query Builder
The Query Builder is a visual tool for building SQL statements. It’s a highly useful tool that generates SQL statements for you—you just specify the data you want to retrieve with point-and-click
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
![](/html/611/317/html_ZdvvECfIFa.rIVN/htmlconvd-U1mIhU912x1.jpg)
THE QUERY BUILDER 907
operations, instead of typing complicated expressions. A basic understanding of SQL is obviously required, and this is why I’ve described the basic keywords of SQL in the last section, but it is possible to build SQL queries with the Query Builder without knowing anything about SQL. I would suggest you use this tool to quickly build SQL statements, but don’t expect that it will do your work for you. It’s a great tool for beginners, but you can’t get far by ignoring SQL. The Query Builder is also a great tool for learning SQL, as you specify the query with point-and-click operations but the Query Builder builds the appropriate SQL statements. You can also edit the SQL statement manually and execute it.
There are many ways to start the Query Builder. In the following chapter, you’ll see how the Query Builder is activated every time you need to specify a query. You can open the Views items in the Server Explorer, right-click the name of a view, and select Design View from the context menu. Views are based on SQL statements, and you will see the Query Builder with the statement that implements the view you selected.
You can also create new queries by creating a new view. A view is the result of a query: it’s a virtual table that consists of columns from one or more tables selected with a SQL SELECT statement. The Query Builder’s window is shown in Figure 20.15.
Figure 20.15
Using the Query Builder to build a SQL statement with point-and-click operations
The query shown in Figure 20.15 retrieved the names of all the products in the Product table, along with the name of the category they belong to (the category name is stored in the Categories table). To create a new query with the Query Builder, open the Northwind database’s section in the Server Explorer, right-click the Views item, and select New View. You will see the window of Figure 20.15, but it will be empty.
The Query Builder Interface
The Query Builder contains four panes: Diagram, Grid, SQL, and Results.
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
![](/html/611/317/html_ZdvvECfIFa.rIVN/htmlconvd-U1mIhU913x1.jpg)
908 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS
Diagram Pane
This is where you select the tables you want to use in your queries—the tables in which the required data reside. To select a table, right-click anywhere on the Diagram pane and you will see the Add Table dialog box. Select as many tables as you need and then close the Add Table dialog box.
The selected tables will appear on the Diagram pane as small boxes, along with their fields, as shown in Figure 20.15. The tables involved in the query are related to one another (although this is not a requirement, it’s rather unlikely that you’ll retrieve data from unrelated tables). The relations are indicated as lines between the tables. These lines connect the primary and foreign keys of the relation. The line between the Products and Categories tables in Figure 20.15 indicates that the two tables are related through the CategoryID field. The CategoryID field in the Categories table is the primary key, and the same field in the Products table is the foreign key. The symbol of a key at one end of the line shows the primary key of the relationship, and the other end of the arrow is either a key (indicating a one-to-one relationship) or the infinity symbol (indicating a one-to-many relationship).
The little shape in the middle of the line indicates the type of join that must performed on the two tables, and it can take several shapes. To change the type of the relation, you can right-click the shape and select one of the options in the context menu. The diamond-shaped icon indicates an inner join, which requires that only rows with matching primary and foreign keys will be retrieved. By default, the Query Builder treats all joins as inner joins, but you can change the type of the join; you’ll see how this is done in the section “Specifying Left, Right, and Inner Joins,” later in this chapter.
The first step in building a query is the selection of the fields that will be included in the result. Select the fields you want to include in your query by checking the box in front of their names, in the corresponding tables. As you select and deselect fields, their names appear in the Grid pane.
Notice that all fields are prefixed by the name of the table they came from, so that there will be no ambiguities.
Right-click the Diagram pane and select Add Table. In the dialog box that pops up, select the Products and Categories tables, click Add, then click Close to close the dialog box.
Grid Pane
The Grid pane contains the selected fields. Some fields may not be part of the output—you may use them only for selection purposes—but their names will appear on this pane. To exclude them from the output, clear the box in the Output column.
The Alias column contains a name for the field. By default, the column’s name is the alias. This is the heading of each column in the output, and you can change the default name to any string that suits you.
SQL Pane
As you build the statement with point-and-click operations, the Query Builder generates the SQL statement that must be executed against the database to retrieve the specified data. The statement that retrieves product names along with their categories is shown next:
SELECT |
dbo.Products.ProductName, dbo.Categories.CategoryName |
FROM |
dbo.Categories INNER JOIN dbo.Products |
|
ON dbo.Categories.CategoryID = dbo.Products.CategoryID |
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
![](/html/611/317/html_ZdvvECfIFa.rIVN/htmlconvd-U1mIhU914x1.jpg)
THE QUERY BUILDER 909
If you paste this statement in the SQL pane and then execute it, you’ll see a list of product names along with their categories. To execute the query, right-click somewhere on the Query Builder window and select Run Query. The Query Builder will first fill out the remaining panes (if you’ve chosen to enter the SQL statement), and then it will execute the query. It will display the tables involved in the query on the Tables pane, it will insert the appropriate rows in the Grid pane, and then it will execute the query and display the results on the Results pane.
Results Pane
To execute a query, right-click somewhere on the SQL pane and select Run from the context menu. The Query Builder will execute the statement it generated and will display the results in the Results pane at the bottom of the window. The heading of each column is the column’s name, unless you’ve specified an alias for the column.
In the following section, we’re going to build a few fairly complicated queries with the visual tools of Query Builder, and in the process I will discuss additional features of the Query Builder.
SQL at Work: Calculating Sums
In this section we’ll build a query that retrieves all the products, along with the quantities sold. The names of the products will come from the Products table, while the quantities must be retrieved from the Order Details table. Because the same product appears in multiple rows of the tables (each product appears in multiple invoices with different quantities), we must sum the quantities of all rows that refer to the same product.
Create a new view in the Server Explorer to start the Query Builder, right-click the upper pane, and select Add Table. On the Add Table dialog box, select the tables Products and Order Details, then close the dialog box. The two tables will appear on the Diagram pane with a line connecting them. This is their relation.
Now check the fields you want to include in the query: Select the field ProductName in the Products table and the field Quantity in the Order Details table. Expand the options in the Sort Type box in the ProductName row and select Ascending. The Query Builder will generate the following SQL statement:
SELECT |
dbo.Products.ProductName, dbo.[Order Details].Quantity |
FROM |
dbo.Products INNER JOIN dbo.[Order Details] |
|
ON dbo.Products.ProductID = dbo.[Order Details].ProductID |
ORDER BY dbo.Products.ProductName
Execute this statement, and the first few lines in the Results pane will be
Alice Mutton 30
Alice Mutton 15
Alice Mutton 15
Alice Mutton 40
The Query Builder knows how the two tables are related and retrieved the matching rows from the two tables. It has also inserted a line that links the two tables in the Tables pane. This line indicates the relationship between the two tables. However, it didn’t sum the quantities for each product.
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |