- •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
946 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET
Run the ViewEditCustomers application, browse all the customers in the DataSet, and edit a few. You can commit the changes to the database at any time by clicking the Update Table button. Or you can discard the edits and reload the data into the DataSet by clicking the Load Table button.
In Chapter 22, you’ll learn how to validate the data and how to commit the changes to the database in a more robust manner. A sure way to crash this application is to load the data, remove one of the customers from the underlying table (use the Enterprise Manager to delete a row of the Customers table in the Northwind database), and then attempt to update the database. The operation will fail, because the Update method will attempt to update a row that no longer exists in the database.
There are two more problems with this application. First, we’re downloading the entire Customers table to the client. Northwind is a small database, but an actual table with customer information might be quite large. It’s recommended that you move only as many rows as you need from the database (customers from a specific country, or customers that placed an order in the last few weeks). You can even move a single row. If the user wants to change a customer’s phone number, your application should give the user a chance to specify the desired row and then retrieve only that row. You should always keep in mind the fact that the DataSet resides on the client, and you shouldn’t move too much information too frequently from the database to the client.
The other problem of this application is the user interface. If the number of customers were in the thousands, moving to the next or previous row would clearly be out of the question. We’re going to look at a more functional interface in the following section, where we’ll also discuss the process of data-binding the ListBox and ComboBox controls.
Binding Complex Controls
The process of binding the ListBox and ComboBox controls is different than binding simple controls, or even the DataGrid control. These two controls are commonly used as lookup and navigational devices. They can display one field and keep track of another field. For example, you can display the customers’ names and keep track of the customer ID. In effect, the user sees names, but your program sees IDs. The application we’ll use in the following section does exactly that: it displays the company names in a ComboBox control and lets the user select a customer by clicking a name. The selected customer’s fields will appear in the data-bound controls, and the user can either edit a row, or move to a different row by clicking another item on the ListBox control. The new interface is far more functional that the previous one, and you will use it in many situations, especially if you want to present many rows to the user.
An even more common use of the complex data-bound controls is as lookup devices. Each row in the Products table has a CategoryID field and a SupplierID field, which link each product to a category and a supplier. The actual names of the categories and suppliers are stored in separate tables, related to the Products table through a pair of primary/foreign keys. You’ll see how you can create a form with data-bound controls for the Products table and use two ComboBox controls populated with the names of the categories and suppliers. As you move through the products, the proper items will be selected in the two ComboBox controls.
VB.NET at Work: The LookupCustomers Project
The LookupCustomers project is very similar to the ViewEditCustomers project—the only difference is in the navigational model. Copy the ViewEditCustomers project’s folder to a new location
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
CREATING A DATASET 947
and rename it to LookupCustomers. Then open the new project and rename it to LookupCustomers. (Or use the project provided on the companion CD.) The main form of the new project is shown in Figure 21.13.
Figure 21.13
Using a data-bound ComboBox control as navigational tool
Delete all the navigational controls at the bottom of the form, and place a ComboBox control to the left side of the form (you will have to move the other controls to make room for the ComboBox control). The ComboBox control must be populated with the names of all customers. Set the control’s DataSource property to DSCustomers1.Customers and its DisplayMember property to CompanyName. The ComboBox control will get its data from the Customers table and will display the CompanyName field of each row.
Bind the TextBox controls on the form to the appropriate fields in the Customers table. The last step is to populate the DataSet by calling the Fill method of the associated DataAdapter. Enter the following statements in the Load button’s Click event handler:
Private Sub LoadData(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles bttnLoad.Click
DSCustomers1.Clear()
DACustomers.Fill(DSCustomers1, “Customers”)
End Sub
If you run the application now, you’ll be able to populate the DataSet, but nothing will happen as you click its items. You must add a few lines of code to set the current row in the DataSet every time the user selects another customer in the ComboBox control. Since the order of the items on the ComboBox control is the same as the order of rows in the DataSet, you can set the Position property to the index of the selected item. This is done with a single statement:
Me.BindingContext(DsCustomers1, “Customers”).Position = ComboBox1.SelectedIndex
If you allow users to edit the current row, however, and that row contains errors, you may not be able to move to another row. One such error would be to clear the current row’s CompanyName field. This field can’t be empty (this column’s AllowNull property is False), and any attempt to set it to Null will cause an exception when you attempt to leave the current row. You must insert an exception handler to reject the changes if the current row contains errors. Listing 21.5 shows the event handler of the ComboBox control’s SelectedIndexChanged event handler.
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
948 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET
Listing 21.5: Moving to a Row in the DataSet
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Try
Me.BindingContext(DsCustomers1, “Customers”).EndCurrentEdit() Me.BindingContext(DsCustomers1, “Customers”).Position = _
ComboBox1.SelectedIndex Catch updateException As Exception
MsgBox(updateException.Message)
Me.BindingContext(DsCustomers1, “Customers”).CancelCurrentEdit() Me.BindingContext(DsCustomers1, “Customers”).Position = _
ComboBox1.SelectedIndex End Try
End Sub
The code in the Catch clause of the exception handler displays the error message that prevented the DataSet from moving to another row. Then it cancels the current edit action (the fields are restored to their initial values) and, finally, moves to the new row. This isn’t the most elegant method of handling update errors, but we’ll discuss more robust techniques in the following chapter.
VB.NET at Work: The Products Project
In this section, you’ll learn how to use a data-bound ComboBox as a lookup mechanism in your applications. The main form of the application, shown in Figure 21.14 and available in this chapter’s folder on the CD, allows you to navigate through the rows of the Products table with the help of a ListBox control, a technique you’re already familiar with. Most of the fields of each row are displayed on TextBox controls. If you bind the CategoryID and SupplierID fields to two TextBox controls, you will see two numbers, which are the foreign keys to the other two tables. By using data-bound ComboBox controls, users see the actual names of the related fields.
Figure 21.14
Browsing and editing the Products table
Start a new project and design a form like the one shown in Figure 21.14. Then you must create a DataSet with three tables that store product-related information: the Products, Categories, and Suppliers tables. Drop the three tables on the form, configure each DataAdapter, and create the Products
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
CREATING A DATASET 949
DataSet. You can select a few fields from each table, as you aren’t going to display many fields. The SELECT statements for the three DataAdapters are as follows:
DAProducts:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock
FROM dbo.Products
DACategories:
SELECT CategoryID, CategoryName FROM dbo.Categories
DASuppliers:
SELECT SupplierID, CompanyName FROM dbo.SuppliersASuppliers
Then create the DSProducts DataSet and place all three tables in it. Since we want the current row of the Categories and Suppliers tables to change each time another row of the Products table is selected, we must establish the appropriate relationships between the tables. Double-click the DSProducts.xsd file in the Solution Explorer window to open the DataSet in design view. Then establish a relationship between the Products and Categories table based on the CategoryID field and another relationship between the Products and Suppliers tables based on the SupplierID field. Preview the DataSet to see that all tables and relations are in place. If you view one of the Categories or Suppliers tables, you will see that each row leads to a set of related rows.
Now you must bind all the controls on the form. Bind the Text property of each of the TextBox controls to the appropriate field of the Products table of the DataSet. The top TextBox is bound to the expression
DsProducts1 - Products.ProductName
and it will display the ProductName field of the Products table. Do the same for the controls that display the Price and Stock fields.
Select the ListBox control and bind it as follows:
Property |
Value |
DataSource |
DSProducts1.Products |
DataMember |
ProductName |
These two properties will cause the ListBox control to be populated with the ProductName field of the Products DataTable. Then set the control’s ValueMember property to the ProductID field. This property connects the selected item on the control to one of the rows of the Products table in the DSProducts1 DataSet.
The last step is to bind the two ComboBox controls to the related tables. Select the first ComboBox control on the form and bind it to the DataSets as follows:
Property |
Value |
DataSource |
DSProducts1.Categories |
DataMember |
CategoryName |
ValueMember |
CategoryID |
SelectedValue |
DSProducts1 – Products.CategoryID |
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |
950 Chapter 21 BUILDING DATABASE APPLICATIONS WITH ADO.NET
The ComboBox control will be populated with the names of the categories and will be connected to the Products table through the CategoryID field. In effect, the current selection on the control will be the name of the category of the row whose CategoryID field matches the CategoryID field of the Products DataTable.
Binding the second ComboBox control to the Suppliers table is quite similar, so I will only list the data-binding properties and their settings:
Property |
Value |
DataSource DSProducts1.Suppliers
DataMember CompanyName
ValueMember SupplierID
SelectedValue |
DSProducts1 – Products.SupplierID |
You should be very familiar with the code that populates the DataSet and updates the database by now. The code behind the two buttons on the form is shown in Listings 21.6 and 21.7.
Listing 21.6: Populating the DSCustomers DataSet
Private Sub Button1_Click(ByVal sender As System.Object,_
ByVal e As System.EventArgs) Handles Button1.Click
DACategories.Fill(DsProducts1, “Categories”)
DASuppliers.Fill(DsProducts1, “Suppliers”)
DAProducts.Fill(DsProducts1)
End Sub
Listing 21.7: Updating the Database
Private Sub Button2_Click(ByVal sender As System.Object,_
ByVal e As System.EventArgs) Handles Button2.Click
DAProducts.Update(DsProducts1)
End Sub
The code for updating the underlying table is quite primitive. You will learn how to properly update the underlying table in the following chapter.
The last step is to add some code to the ListBox control to turn it into a navigational tool. We want to move to the row of the product selected on the control. We can take advantage of the fact that the items on the ListBox appear in the same order as in the DataSet: we’ll use the control’s SelectedIndex property to move to the appropriate row in the Products DataTable. Listing 21.8 shows how to move to the correct row of Products when the user selects another item on the ListBox.
Copyright ©2002 SYBEX, Inc., Alameda, CA |
www.sybex.com |