Rick Dobson
Beginning SQL Server
2005 Express Database
Applications
with Visual Basic Express and
Visual Web Developer Express
From Novice to Professional
5238CH00_FM 11/18/05 4:29 PM Page i
Beginning SQL Server 2005 Express Database Applications
with Visual Basic Express and Visual Web Developer Express From Novice to Professional
Copyright © 2006 by Rick Dobson
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN (pbk): 1-59059-523-8
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editors: Tony Davis and Matthew Moodie
Technical Reviewer: Cristian Lefter
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore,
Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser
Project Manager: Beth Christmas
Copy Edit Manager: Nicole LeClerc
Copy Editors: Damon Larson and Freelance Editorial Services
Assistant Production Director: Kari Brooks-Copony
Production Editor: Kelly Winquist
Compositors: Dina Quan and Diana Van Winkle, Van Winkle Design Group
Proofreader: April Eddy

■
CHAPTER 2 Graphical and Command-Line Query Tools
. . . . . . . . . . . . . . . . . . . . . . . . . . . 33
■
CHAPTER 3 Exploring, Creating, and Recovering Databases
. . . . . . . . . . . . . . . . . . . . . . 73
■
CHAPTER 4 Data Types, Tables, and Constraints
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
■
CHAPTER 5 Creating Queries from a Single Database Object
. . . . . . . . . . . . . . . . . . . . 155
■
CHAPTER 6 Querying Multiple Database Objects and Manipulating Result Sets
. . . 191
■
CHAPTER 7 Leveraging Database Objects That Encapsulate T-SQL
. . . . . . . . . . . . . . . 247
■
CHAPTER 8 Managing SQL Server Express Security
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
PART 2
■ ■ ■
Working with Visual Basic Express
and Visual Web Developer Express
■
CHAPTER 9 Introduction to Visual Basic Express and Windows Forms
. . . . . . . . . . . . 371
■
CHAPTER 10 Introduction to Visual Web Developer Express,

Getting Started with SQL Server Express
. . . . . . . . . . . . . . . . . . . . . . 3
What Is SQL Server Express?
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Who Is SQL Server Express For?
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Hobbyists and Other Nonprofessional Developers
. . . . . . . . . . . . . . . . . . . . . 6
Business Analysts
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Database Administrators and Operations Specialists
. . . . . . . . . . . . . . . . . . 7
Professional Developers in Need of a Free, Modern Database
. . . . . . . . . . . 8
SQL Server Express vs. SQL Server 2005
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Similarities
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Differences
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
SQL Server Express vs. MSDE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Installing SQL Server Express
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Performing a System Check for SQL Server Express
. . . . . . . . . . . . . . . . . . 12
Registration, Feature, and Instance Name Screens
. . . . . . . . . . . . . . . . . . . 13
Service Account and Authentication Mode Screens
. . . . . . . . . . . . . . . . . . . 14

CONTENTSvi
■
CHAPTER 2
Graphical and Command-Line Query Tools
. . . . . . . . . . . . . . . . . . 33
Using the SSMS-Based Query Tool
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Connecting to SQL Server Instances
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Using Database, Table, and View Designers
. . . . . . . . . . . . . . . . . . . . . . . . . 41
Designing, Running, Saving, and Rerunning SQL Scripts
. . . . . . . . . . . . . . 51
Using the sqlcmd Utility
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Connecting to SQL Server Instances
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Running Statements from a File
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Saving Output to a File
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Dynamically Running and Batching sqlcmd Statements
. . . . . . . . . . . . . . . 65
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
■
CHAPTER 3
Exploring, Creating, and Recovering Databases
. . . . . . . . . . . . . 73
Overview of Databases

. . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Choosing and Modifying a Database’s Recovery Model
. . . . . . . . . . . . . . 101
Performing a Full Data Backup and Restore
. . . . . . . . . . . . . . . . . . . . . . . . 102
Performing Differential Backups and Restores
. . . . . . . . . . . . . . . . . . . . . . 103
Cleaning Up the Backup and Restore Scripts
. . . . . . . . . . . . . . . . . . . . . . . 106
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
■
CHAPTER 4
Data Types, Tables, and Constraints
. . . . . . . . . . . . . . . . . . . . . . . . . 107
Learning About Data Types
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Numbers and Dates
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Character and Binary Byte Streams
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Miscellaneous
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Creating Tables and Using Data Types
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Creating a Table with Columns
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
5238CH00_FM 11/18/05 4:29 PM Page vi
■
CONTENTS vii

Specifying Queries from Another Server Instance
. . . . . . . . . . . . . . . . . . . 168
Filtering, Grouping, and Aggregating
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Filtering for Exact and Approximate Character Matches
. . . . . . . . . . . . . . 175
Filtering for Values in a Range
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Grouping and Aggregating
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
■
CHAPTER 6
Querying Multiple Database Objects
and Manipulating Result Sets
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Joining Data Sources
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Inner Joins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Outer Joins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Cross Joins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Self-Joins
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Joins for More Than Two Data Sources
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Using Subqueries

Performing Data Modification with a View
. . . . . . . . . . . . . . . . . . . . . . . . . 252
Processing Meta Data with System Views
. . . . . . . . . . . . . . . . . . . . . . . . . 256
Creating and Using User-Defined Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Creating and Using FN User-Defined Functions
. . . . . . . . . . . . . . . . . . . . . 260
Creating and Using IF User-Defined Functions
. . . . . . . . . . . . . . . . . . . . . . 263
Creating and Using Stored Procedures
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Returning Result Sets Without Parameters
. . . . . . . . . . . . . . . . . . . . . . . . . 268
Returning Result Sets and Input Parameters
. . . . . . . . . . . . . . . . . . . . . . . 271
Returning Scalar Values with Output Parameters
. . . . . . . . . . . . . . . . . . . . 278
Processing Return Status Values
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Performing Data Manipulation
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Creating and Using Triggers
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Learning to Reference Inserted and Deleted Tables
. . . . . . . . . . . . . . . . . . 293
Rolling Back in an AFTER Trigger
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Archiving Changes to a Table with Triggers
. . . . . . . . . . . . . . . . . . . . . . . . 297

Creating Objects in Owned Schemas
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Creating Objects in Any Schema
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356
Using Encryption Keys and Certificates
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
Encrypting Data
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Signing Data
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
■
CONTENTSviii
5238CH00_FM 11/18/05 4:29 PM Page viii
■
CONTENTS ix
PART 2
■ ■ ■
Working with Visual Basic Express
and Visual Web Developer Express
■
CHAPTER 9
Introduction to Visual Basic Express
and Windows Forms
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Starting, Saving, and Exploring a Solution
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
Starting and Saving a New Solution’s Project
. . . . . . . . . . . . . . . . . . . . . . . 371

Dynamically Adapting to Runtime Errors
. . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Using the File System
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Design and Formatted Views of an Application Form
. . . . . . . . . . . . . . . . . 397
Exploring Folders and Viewing Files
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Reading Fixed-Width Reports
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
■
CHAPTER 10
Introduction to Visual Web Developer Express,
Web Pages, and Web Forms
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
ASP.NET Development Concepts
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Starting and Exploring ASP.NET Projects
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Starting an ASP.NET Project
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Exploring a New ASP.NET Project
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Creating Pages for an ASP.NET Project
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Changing a Page by Clicking a Button
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Returning a Formatted Date from a Calendar Control

Getting Help with Constructing a Connection String
. . . . . . . . . . . . . . . . . 450
Connecting to SQL Server Express and Access Database Files
. . . . . . . . 451
Programming Command Objects
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
Returning a Single Value with a Command Object
. . . . . . . . . . . . . . . . . . . 455
Returning Forward-Only, Read-Only Rowsets
. . . . . . . . . . . . . . . . . . . . . . . 462
Performing Data Definition and Database Maintenance
. . . . . . . . . . . . . . 471
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
■
CHAPTER 12
Programming DataAdapter and DataSet Objects
. . . . . . . . . . . 485
Creating, Populating, and Using DataSet Objects
. . . . . . . . . . . . . . . . . . . . 486
Sorting and Filtering DataTables with DataViews
. . . . . . . . . . . . . . . . . . . . 493
Inserting, Updating, and Deleting Rows
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Bulk Loading Data from Excel
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514
■
CHAPTER 13

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
Viewing Data and Modifying Databases
. . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Displaying Data on a Web Form Page with a GridView Control
. . . . . . . . 560
Performing Lookups on a Web Form Page
. . . . . . . . . . . . . . . . . . . . . . . . . 564
Summary
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 574
■
INDEX
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575
■
CONTENTS xi
5238CH00_FM 11/18/05 4:29 PM Page xi
5238CH00_FM 11/18/05 4:29 PM Page xii
xiii
About the Author
■
RICK DOBSON has written six books on database development with Visual
Basic .NET, SQL Server, and Microsoft Access. In the past few years, he
authored three DVDs on Visual Basic .NET, ADO.NET, and T-SQL. In addi-
tion, Rick has written countless articles for computer periodicals, including
SQL Server Magazine, Visual Studio .NET Developer, Visual Basic Developer,
SQL Server Professional, SQL Server Solutions, Inside Microsoft Visual Basic,
and Smart Access.
Rick is also the webmaster for several websites. His most popular site,
http://ProgrammingMSAccess.com, focuses on the needs of VB .NET and SQL
Server developers, as well as Access developers. This site gets over 1 million
page views annually, and thousands of site visitors register to receive a

Beyond my wife, I was professionally assisted by Cristian Lefter and Matthew Moodie. As the
technical editor, Cristian Lefter carefully scrutinized all the code samples and step-by-step instruc-
tions. On several occasions, he detected errors that we fixed for you, and in other cases he made
suggestions that resulted in simpler or more powerful solutions. Matthew Moodie served as the lead
editor. In this capacity, he modified every page of the book to make it more succinct and easier to
read. Both Cristian and Matthew asked many probing questions to force clarifications of the content
in the book. If you find a Tip, Note, or Caution that is especially responsive to your needs, chances are
Cristian or Matthew prompted me to insert it. This book is my seventh book project as a sole author,
and I never recalled being so blessed by two such talented editors.
Just as the content of the book was so impeccably handled by the book’s editors, the business
side of the book was equally well handled by two Apress team members. My acquisition editor was
Tony Davis. He was all that an author can hope for in an acquisition editor. He contracted with me
in an expeditious fashion for the book project. Beyond that, he made excellent editorial suggestions
on the first several chapters, which I applied to the remaining book chapters. There is no doubt that
the whole book is easier to read because of his excellent suggestions for the first couple of chapters.
Beth Christmas served as the book’s project manager. She regularly urged me to stay as close as pos-
sible to projected deadlines while always allowing me enough time to prepare the best content that
I knew how to author. In addition, she regularly assisted me in all the ways that a project manager
can to make things move expeditiously and gracefully.
It is my practice to include an acknowledgment of my Lord and Savior, Jesus Christ. He is the
author and finisher of my faith much as I am the author of this book and ultimately responsible for
its content. Jesus is my inspiration for working tirelessly to write the best book that I can for you. It
is my prayer that He blesses you so that you derive the maximum value possible from this book.
5238CH00_FM 11/18/05 4:29 PM Page xvii
5238CH00_FM 11/18/05 4:29 PM Page xviii
xix
Introduction
T
his book especially targets SQL Server Express (SSE), Visual Basic Express (VBE), and Visual Web
Developer Express (VWDE), all of which offer traditional fans of Microsoft technology an inexpen-

book, “Working with Visual Basic Express and Visual Web Developer Express.” Feel free to visit or get
in touch with me at [email protected]. In addition, I have provided an online chapter that looks at
advanced VWDE topics. You can view this chapter at www.apress.com/book/bookDisplay.html?bID=459.
5238CH00_FM 11/18/05 4:29 PM Page xix
Who Is This Book For?
Beginning SQL Server 2005 Express Database Applications with Visual Basic Express and Visual Web
Developer Express From Novice to Professional is for several communities of users. The book’s overall
goal is to get any user started with SSE, including those who are working with SSE on a stand-alone
basis, as well as those who run SSE through either or both VBE and VWDE. Included in this general
summary are the following types of audiences:
•Database administrators in small and mid-sized organizations that will use SSE as their pri-
mary database
• MSDE and Access database developers and users who want to move from legacy Microsoft
database technology so that they can position themselves to take advantage of new features
not available in their legacy software
• Classic Visual Basic database developers who want to try creating database solutions with
SSE and VBE or VWDE
•Business, scientific, or technical professionals who are not IT persons, but who need to cre-
ate database solutions for their personal consumption or use by their organizational unit
•Computer hobbyists and students who want to learn about T-SQL and Visual Basic program-
ming for Microsoft’s latest database engine technology
•Professional developers who want to embed SSE in commercial packages marketed by
their firms
What Does the Book Cover?
The book has two parts. The first part, “Working with SQL Server Express,” focuses on SSE. The pro-
gramming language for this part of the book is T-SQL. The second part of the book, “Working with
Visual Basic Express and Visual Web Developer Express,” highlights the use of the Visual Basic 2005
language in VBE and VWDE. You also gain exposure to powerful graphical development techniques
for creating database solutions.
Selected topics addressed in Part 1 include

5238CH01 11/18/05 4:30 PM Page 2