Beginning SQL Server 7.0 with Visual Basic

by Thearon Willis

• ISBN13: 9781861003065
• ISBN10: 1861003064
• Format: Trade Paper
• Copyright: 2000-01-01
• Publisher: SPRINGER-VERLAG

Summary

Willis provides beginners with one source of information to help them build a complete application. Detailed examples demonstrate how-to, along with easy-to-understand explanations.

Table of Contents

Introduction	p. 1
What Is This Book About	p. 1
How Is This Book Organized?	p. 2
Who Is This Book For	p. 5
What You Need To Use This Book	p. 6
Where You Can Find The Sample Code	p. 6
Conventions	p. 6
Tell Us What You Think	p. 8
Customer Support	p. 8
Introduction to SQL Server	p. 11
SQL Server History	p. 12
SQL Server Databases	p. 12
Database Files	p. 13
Tables	p. 15
Relational Database Design	p. 19
Stored Procedures	p. 23
Triggers	p. 24
Views	p. 24
Visual Basic and SQL Server	p. 25
OLE DB, ODBC, and ADO	p. 26
Summary	p. 28
Installing the Desktop Edition of SQL Server	p. 31
SQL Server Editions	p. 32
Target Platform	p. 32
SQL Server Prerequisites	p. 33
Installation Overview	p. 34
SQL Server Installation	p. 34
Tour of SQL Server	p. 43
Enterprise Manager	p. 44
SQL Server Query Analyzer	p. 51
SQL Server Profiler	p. 52
Wizards	p. 53
Full-Text Search	p. 54
English Query	p. 55
Online Analytical Processing (OLAP)	p. 55
Summary	p. 56
Creating the Development Database	p. 59
Suggested Naming Conventions	p. 60
Identifying Objects	p. 60
The Development Database	p. 63
Database Design	p. 64
Tables, Keys, and Data Types	p. 65
Wrox Auto Works Database View	p. 70
Creating the Tables	p. 71
Summary	p. 81
SQL Server Security	p. 83
General Security Overview	p. 83
Authentication	p. 84
Access to Database Objects	p. 86
Adding New Users to SQL Server	p. 86
Database Security	p. 90
Roles	p. 90
Adding New Users to a Database	p. 95
Adding Other Users	p. 97
Object Security	p. 101
Table Security	p. 101
View Security	p. 101
Stored Procedure Security	p. 101
Statement Level Security	p. 102
Granting Access Rights by Assigning Role Permissions	p. 102
Summary	p. 105
SQL Server Query Analyzer	p. 107
Introducing the Query Analyzer	p. 107
Quick Tour of the Query Analyzer	p. 108
Creating, Executing, and Saving Queries	p. 115
Debugging Queries	p. 118
Insert, Update, and Delete Statements	p. 121
Insert Statement	p. 121
Update Statement	p. 123
Delete Statement	p. 125
Summary	p. 127
Database Connections	p. 129
Overview of the ADO 2.5 Object Model	p. 129
ADO Objects and Collections	p. 130
Connection Object	p. 132
Setting Up a Data Source Name	p. 132
DSN Connections	p. 139
DSN-Less Connections	p. 145
Data Environment Designer Connections	p. 148
Summary	p. 153
Introduction to Stored Procedures	p. 155
Stored Procedure vs Transact-SQL Performance	p. 155
Permissions on Stored Procedures	p. 156
Parameters and Return Values	p. 159
Select Stored Procedures	p. 166
Insert Stored Procedures	p. 176
Update Stored Procedures	p. 186
Delete Stored Procedures	p. 191
Summary	p. 197
Stored Procedures vs T-SQL Statements	p. 199
Comparison - Stored Procedures vs In-line SQL	p. 199
INSERT Stored Procedure	p. 200
INSERT SQL Statement	p. 206
UPDATE Stored Procedure	p. 209
UPDATE SQL Statements	p. 213
Delete Stored Procedure	p. 214
Delete SQL Statement	p. 217
Comparison Summary	p. 218
Optimizing SQL Statements	p. 219
Using Command Object Parameters	p. 223
Multiple Recordsets	p. 230
Using Stored Procedures to Return Multiple Results Sets	p. 230
Using a Recordset Object to Return Multiple Results Sets	p. 233
Summary	p. 237
Selecting Data	p. 241
Preparing Our Tables	p. 241
Simple Select Stored Procedure	p. 244
Joins and Column Aliases	p. 252
Left Outer Joins and Correlation Names	p. 272
Summary	p. 287
Inserting Data	p. 289
Stored Procedures that Use Logic	p. 289
Controlling the Flow	p. 290
Evaluating Data	p. 290
Stored Procedures that Use Transactions	p. 301
Checking for Errors	p. 302
Summary	p. 339
Updating Data	p. 341
Stored Procedure that Uses Transactions and Logic	p. 341
Stored Procedure that Builds and Executes a Dynamic SQL String	p. 362
Building and Executing a Dynamic SQL String in VB	p. 381
Summary	p. 389
Deleting Data	p. 391
Referential Integrity	p. 391
Triggers	p. 392
Testing the Trigger	p. 396
Managing Triggers	p. 396
Delete Stored Procedure	p. 397
Summary	p. 407
Creating a Front-End Program	p. 409
Stored Procedures	p. 410
Wrox Auto Works Customer Application	p. 411
Visual Elements	p. 411
Data Presentation	p. 414
Classes	p. 421
AbSplash Form Code	p. 424
Employment Form Code	p. 435
Main Form Code	p. 449
Testing	p. 479
Summary	p. 481
Creating a Business Server Component	p. 483
What is a Business Server Component	p. 483
MTS versus SQL Server Transactions	p. 484
Creating A Business Server Component	p. 486
Help File	p. 492
Modifying the UI	p. 511
Summary	p. 519
Working With Text Data	p. 521
Introduction to Text Data	p. 521
How SQL Server Manages The Data	p. 522
Customer_Comment_T Table	p. 523
Inserting Text Data	p. 524
Selecting Text Data	p. 530
Text Pointers	p. 545
Reading Text Data	p. 547
Writing Text Data	p. 554
Updating Text Data	p. 560
Deleting Text Data	p. 567
Summary	p. 573
Preparing for the Test and Production Environments	p. 575
Index Tuning	p. 576
Backing Up Your Database	p. 583
Generating Scripts	p. 588
Preparing Your Programs	p. 594
Summary	p. 595
Wrox Auto Works Customer Application	p. 599
Wrox Auto Works Customer Application - Part 1	p. 600
Modified Database Design	p. 600
Creating and Altering Tables	p. 602
Stored Procedures	p. 609
Financial Institute Stored Procedures	p. 615
Customer Purchase History Stored Procedures	p. 620
Summary	p. 632
Wrox Auto Works Customer Application - Part 2	p. 633
Finance Class	p. 633
Vehicle Class	p. 637
Address Class	p. 643
Employer Class	p. 644
Completing the AutoWorks Component	p. 647
Summary	p. 648
Wrox Auto Works Customer Application - Part 3	p. 649
Controls Class	p. 649
Financial Form	p. 654
History Form	p. 666
Existing Forms	p. 690
Testing	p. 696
Summary	p. 697
Wrox Auto Works Sales Staff Application	p. 701
Wrox Auto Works Sales Staff Application - Part 1	p. 702
Database Design	p. 703
Database Script	p. 704
Stored Procedures	p. 710
Existing Stored Procedures	p. 710
New Stored Procedures	p. 714
AutoWorks Business Component	p. 720
SalesStaff Class	p. 722
Wrox Auto Works Sales Staff Application - Part 2	p. 730
Modifying the UI	p. 730
Sales Staff Form	p. 731
Wrox Auto Works Sales Staff Application - Part 3	p. 747
Temporary Tables	p. 747
Aggregate Functions	p. 750
Top Sales Persons Stored Procedure	p. 751
Data Environment Designer	p. 754
Data Report Designer	p. 758
Summary	p. 768
Customer English Query Application	p. 771
English Query Installation	p. 772
English Query Overview	p. 775
Semantic Objects	p. 776
The Customer English Query Application	p. 777
Deploying Your English Query Application	p. 804
Summary	p. 807
T-SQL and VB Data Types	p. 809
ADO 2.5 Object Model and Constants	p. 813
Object Model	p. 813
Objects	p. 813
Command Object	p. 814
Connection Object	p. 815
Error Object	p. 817
Errors Collection	p. 818
Field Object	p. 818
Fields Collection	p. 819
Property Object	p. 820
Properties Collection	p. 820
Parameter	p. 821
Parameters Collection	p. 821
Record	p. 822
Recordset	p. 823
Stream	p. 827
Constants	p. 829
SQL Server Functions	p. 853
String Functions	p. 853
System Functions	p. 863
Date and Time Functions	p. 878
Aggregate Functions	p. 881
Support and Errata	p. 889
Index	p. 895
Table of Contents provided by Syndetics. All Rights Reserved.