Visual Basic 6 Database Programming Bible (Bible (Wiley))

Wayne S. Freeze, Wiley Publishing

ISBN:0764547283, Edition: Pap/Cdr, 2000-09

Price: $39.99

Contents

Preface ~ ix
Acknowledgments ~ xv

Part I: Database Programming Fundamentals ~ 1

Chapter 1: Basic Concepts ~ 3
Why Use a Database? ~ 3
Storing information ~ 3
Concurrency ~ 5
Securing your data ~ 6
Performing backups and using transaction logs ~ 6
A database is the answer ~ 7
Database Architecture ~ 8
Servers and clients ~ 8
Database servers and database clients ~ 9
Types of Databases ~ 11
Hierarchical databases ~ 11
Networked databases ~ 11
Indexed databases ~ 13
Relational databases ~ 13
Common Databases ~ 14
SQL Server 7.0 ~ 14
Microsoft Jet 3.5/4.0 ~ 15
Oracle 8i ~ 15

Chapter 2: The Relational Database Model ~ 19
Introducing the Structured Query Language ~ 19
Relational history ~ 19
Business benefits of a relational database ~ 23
Parts of a Relational Database ~ 24
Tables and rows of data ~ 24
Columns and data types ~ 24
Indexes and keys ~ 25
Views ~ 27
Normalization ~ 27

Chapter 3: Designing a Relational Database ~ 31
Overview of the Design Process ~ 31
Stating the Problem ~ 32
Brainstorming ~ 33
Brainstorming Toy Collector ~ 33
Reviewing the results ~ 34
Mapping the results to data types ~ 36
Modeling Entities and Relationships ~ 37
Entity/relationship modeling ~ 37
Identifying entities and attributes ~ 38
Identifying Relationships ~ 44
Drawing the E/R model ~ 45
Building the Database ~ 46

Chapter 4: SQL Statement Primer ~ 49
Using SQL Statements ~ 49
SQL statements ~ 49
SQL data types ~ 50
Testing SQL statements ~ 53
The Select statement ~ 53
Simple Select statements ~ 54
Selecting a subset of a table ~ 56
Sorting results ~ 59
Using multiple tables ~ 60
Nested queries ~ 62
Using functions ~ 63
Inserting Rows into a Table ~ 64
A simple Insert statement ~ 65
Deleting Rows from a Table ~ 67
A Sample Delete Statement ~ 67
Updating Rows in a Table ~ 68
A Sample Update Statement ~ 69
The Create Table Statement ~ 70
The Create Index Statement ~ 71
A Sample Create Index Statement ~ 72
The Create View Statement ~ 72

Chapter 5: Introducing Visual Basic ~ 77
Why Use Visual Basic? ~ 77
Basic history ~ 77
Database integration ~ 80
Visual Basic Editions ~ 82
Learning Edition ~ 83
Professional Edition ~ 83
Enterprise Edition ~ 84
Other Variations ~ 84
Types of Visual Basic Programs ~ 85
Standard EXEs ~ 85
ActiveX DLLs/Controls/EXEs ~ 86
IIS Applications ~ 87
DHTML Applications ~ 87

Chapter 6: Accessing Databases from Visual Basic ~ 89
Microsoft Database Programming APIs ~ 89
ODBC ~ 90
ODBC architecture ~ 90
Drawbacks to ODBC ~ 91
Database Access Objects (DAO) ~ 91
Remote Database Objects (RDO) ~ 92
OLE DB ~ 92
Data providers ~ 92
Data consumers ~ 93
ActiveX Data Objects (ADO) ~ 93
OLE DB providers ~ 93
Custom OLE DB providers ~ 94
Visual Basic Database Tools ~ 94
Data Environment Designer ~ 95
Data View Window ~ 96
Database Designer ~ 97
SQL Editor ~ 98
T-SQL Debugger ~ 98
Query Designer ~ 99
Data Reporter Designer ~ 100
UserConnection Designer ~ 100

Part II: Beginning Database Programming ~ 103

Chapter 7: Codeless Database Programming ~ 105
Data Binding ~ 105
What is data binding? ~ 105
How does data binding work? ~ 106
Connecting to the database ~ 106
Intrinsic bound controls ~ 107
ActiveX bound controls ~ 107
Building the Codeless Program ~ 108
Preparing your project ~ 108
Configuring the ADO Data Control ~ 109
Adding bound controls ~ 113
Testing your program ~ 114
Finishing your program ~ 114

Chapter 8: More About Bound Controls ~ 119
Bound Controls Revisited ~ 119
Key properties ~ 119
Key methods ~ 119
Key events ~ 120
Data validation ~ 121
Formatting data ~ 125
Using the Picture and Image Controls ~ 127
Using the Masked Edit Control ~ 127
Key properties ~ 128
Creating an input mask ~ 129
Prompting the user ~ 130
Database considerations ~ 131
Using the DateTimePicker Control ~ 131
Key properties ~ 131
Choosing a user interface ~ 134
Using the DataCombo Control ~ 135
Key properties ~ 135
Key methods ~ 136
Configuring the control ~ 137
Selecting from a list ~ 137
Translating a value ~ 137

Chapter 9: Programming with Data Environments ~ 141
The Data Environment Designer ~ 141
Enabling the Data Environment Designer ~ 142
Exploring the Data Environment Designer ~ 142
Data Environment building blocks ~ 144
Connecting to Your Database ~ 145
Setting Connection properties ~ 146
Selecting an OLE DB provider ~ 146
Entering connection information ~ 146
Creating Commands with the Designer ~ 147
Adding a command ~ 148
Setting general command properties ~ 148
Setting parameters ~ 149
Setting advanced properties ~ 151
Saving the Command ~ 153
Adding a Child Command ~ 154
Inserting a stored procedure ~ 157
Building Programs with the Designer ~ 158
Drawing controls ~ 158
Setting options ~ 160
Data Environment RunTime Object Model ~ 161
DataEnvironment properties ~ 161
DataEnvironment methods ~ 162
Data Environment events ~ 163
Viewing Databases with the Data View Window ~ 163
Configuring the Data View Window ~ 164
Working with database diagrams ~ 164
Working with tables ~ 165
Working with views ~ 166
Working with stored procedures ~ 167

Chapter 10: Building Reports with the Microsoft Data Report Designer ~ 169
Introducing the Microsoft Data Report ~ 169
Using the Data Report Designer ~ 170
Getting your data ~ 170
Building a data report’s structure ~ 170
Placing controls on your report ~ 172
Programming Your Report ~ 176
Previewing a report ~ 176
Printing a Report ~ 177
Exporting reports ~ 178
Tracking asynchronous activity ~ 179
The Data Report object model ~ 179
DataReport properties ~ 179
DataReport methods ~ 181
Key DataReport events ~ 182
Controls collection properties ~ 184
ExportFormat object properties ~ 184
ExportFormats collection properties ~ 184
ExportFormats collection methods ~ 185
RptError object properties ~ 187
RptFunction control properties ~ 187
RptImage control properties ~ 189
RptLabel control properties ~ 190
RptLine control properties ~ 192
RptShape control properties ~ 193
RptTextBox control properties ~ 194
Section object properties ~ 195
Sections collection properties ~ 195

Part III: Hardcore ADO ~ 199

Chapter 11: The ADO Object Model ~ 201
Introducing ActiveX Data Objects 2.5 ~ 201
The ADO object model ~ 201
New in ADO 2.5 ~ 203
Introducing ActiveX Data Objects Extensions ~ 204
Basic ADO Programming ~ 207
Connecting to the data source ~ 207
Executing a command ~ 207
Playing with Recordsets ~ 208

Chapter 12: Connecting to a database ~ 211
The Connection Object ~ 211
Connection object properties ~ 211
Connection object methods ~ 214
Connection object events ~ 217
The Error Object ~ 223
Error object properties ~ 223
The Errors Collection ~ 223
Errors collection properties ~ 224
Errors collection methods ~ 224
Connecting To Database Server ~ 225
Connection strings ~ 225
Opening a connection ~ 227
Closing a connection ~ 231
Analyzing Errors ~ 231
Watching connection activity ~ 232

Chapter 13: Using Commands and Stored Procedures ~ 235
Introducing the ADO Command Object ~ 235
The Command Object ~ 236
Command object properties ~ 236
Command bject methods ~ 238
The Parameter Object ~ 240
Parameter object properties ~ 240
Parameter object methods ~ 245
The Parameters Collection ~ 245
Parameters collection properties ~ 245
Parameters collection methods ~ 245
Running SQL Statements ~ 246
Running a simple command ~ 246
Returning a Recordset ~ 248
Running with parameters ~ 250
Stored Procedures ~ 251
Advantages of stored procedures ~ 251
Stored procedures and the Data View Window ~ 253
Creating a Data Link ~ 253
Creating a stored procedure ~ 254
Debugging stored procedures ~ 256
Calling a stored procedure ~ 261

Chapter 14: Working with Recordsets—Part I ~ 265
The Recordset Object ~ 265
Recordset object properties ~ 266
Recordset object methods, ~ 272
Recordset object events ~ 283
Before Opening a Recordset ~ 289
Locking considerations ~ 289
Choosing a cursor type ~ 292
Picking a cursor location ~ 293
Opening a Recordset ~ 294
Using Source strings ~ 294
Using Command objects ~ 295

Chapter 15: Working with Recordsets—Part II ~ 299
More About Recordsets ~ 299
The Field Object ~ 299
Field object properties ~ 300
Field object methods ~ 304
The Fields Collection ~ 305
Fields collection properties ~ 305
Fields collection methods ~ 306
Moving Around a Recordset ~ 307
The Recordset Movement Demo program ~ 307
Moving sequentially ~ 309
Moving randomly ~ 312
Searching, Sorting, and Filtering ~ 314
Finding a row ~ 315
Sorting rows ~ 316
Filtering rows ~ 317
Collecting recordset information ~ 318
Getting Information From Fields ~ 320
Binding a field to a control ~ 320
Accessing field values ~ 320
Working with large values ~ 321

Chapter 16: Working with Recordsets—Part III ~ 327
Updating Recordsets ~ 327
Updating an existing record ~ 329
Adding a new record ~ 331
Deleting an existing record ~ 332
Performing batch updates ~ 333
Making Transactions ~ 335
Why do I need transactions? ~ 335
ADO and transactions ~ 336
Working with Disconnected Recordsets ~ 338
Making a recordset local ~ 339
Working with Other Recordset Functions ~ 342
Cloning a recordset ~ 342
Resyncing and requerying a recordset ~ 342
Returning multiple recordsets ~ 342
Alternate ways to get data ~ 343
Setting the cache size ~ 344

Part IV: COM+ Transactions and Message Queues ~ 347

Chapter 17: Building Your Own Bound Controls ~ 349
Introducing Data Sources and Consumers ~ 349
Data sources ~ 350
Data consumers ~ 350
A Brief Introduction to COM Components ~ 351
What is a COM component? ~ 351
Using class modules ~ 351
Persistable objects ~ 352
Class module properties ~ 353
Class module property routines ~ 354
Class module events ~ 354
The PropertyBag object ~ 355
Building a Data Source ~ 356
Module-level declarations ~ 357
Binding data ~ 357
Moving through the recordset ~ 359
Exporting recordset information ~ 359
Using the DataSpinner control ~ 360
Building a Data Consumer ~ 360
Exposing properties ~ 361
Setting property attributes ~ 362
Persisting properties ~ 364
Pulling It All Together ~ 366

Chapter 18: Using COM+ Transactions ~ 369
A Brief Overview of COM+ ~ 369
Multi-tier applications ~ 369
Transaction Servers ~ 371
COM+ applications ~ 372
The COM+ transaction server ~ 373
The object context ~ 373
The Component Services utility ~ 373
Introducing COM+ Transactions ~ 374
The ACID test ~ 375
Class module properties for transactions ~ 376
The ObjectContext object ~ 377
ObjectContext object properties ~ 378
ObjectContext object methods ~ 378
Constructing a COM+ Transaction ~ 380
Holding type information ~ 380
Accessing the database with transactions ~ 384
Building a simple test program ~ 392

Chapter 19: Using Message Queues ~ 399
How Message Queuing Works ~ 399
Synchronous processing ~ 399
Asynchronous processing ~ 400
Benefits of message queuing ~ 401
Microsoft Message Queuing ~ 404
Requests and responses ~ 404
Types of queues ~ 404
Public and private queues ~ 405
Message queuing and COM+ transactions ~ 406
Message Queuing Object Model ~ 406
The MSMQQueueInfo Object ~ 408
MSMQQueueInfo object properties ~ 408
MSMQQueueInfo object methods ~ 409
The MSMQQueue Object ~ 410
MSMQQueue object properties ~ 411
MSMQQueue object methods ~ 411
The MSMQMessage Object ~ 414
MSMQMessage object properties ~ 415
MSMQMessage object methods ~ 418
MSMQEvent object events ~ 418
Accessing Message Queues ~ 419
Building the client program ~ 420
Building the server program ~ 424
Viewing Message Queue Information ~ 427

Part V: The Impact of XML ~ 431

Chapter 20: Introducing XML ~ 433
Documenting Information ~ 433
Tagging information ~ 433
A Simple XML document ~ 435
XML attributes ~ 438
Writing XML Documents ~ 439
Creating an XML document ~ 439
Identifying XML elements ~ 439
Creating XSL Style Sheets ~ 440
Other XML tools ~ 446
Working with XML and ADO ~ 446
Creating an XML File with ADO ~ 447
Looking at the XML file ~ 447
Understanding the Benefits of Using XML ~ 449
Data interchange ~ 449
Separating content from formatting ~ 451
Vendor independence ~ 452
Industry acceptance ~ 452

Chapter 21: The Document Object Model ~ 455
The Document Object Model ~ 455
Document hierarchy ~ 456
Other objects ~ 458
The XMLDOMNode Object ~ 458
XMLDOMNode object properties ~ 458
XMLDOMNode object methods ~ 460
The DOMDocument Object ~ 464
DOMDocument object properties ~ 464
DOMDocument object methods ~ 466
DOMDocument object events ~ 468
The XMLDOMAttribute object ~ 469
XMLDOMAttribute object properties ~ 469
XMLDOMAttribute object methods ~ 469
The XMLDOMCDATASection Object ~ 469
XMLDOMCDATASection object properties ~ 470
XMLDOMCDATASection object methods ~ 470
The XMLDOMComment Object ~ 470
XMLDOMComment object properties ~ 471
XMLDOMComment object methods ~ 471
The XMLDOMDocumentType Object ~ 471
XMLDOMDocumentType object properties ~ 471
XMLDOMDocumentType object methods ~ 472
The XMLDOMElement Object ~ 472
XMLDOMElement object properties ~ 472
XMLDOMElement object methods ~ 472
The XMLDOMEntity Object ~ 473
XMLDOMEntity object properties ~ 473
XMLDOMEntity object methods ~ 474
The XMLDOMEntityReference Object ~ 474
The XMLDOMNotation Object ~ 474
XMLDOMNotation object properties ~ 475
XMLDOMNotation object methods ~ 475
The XMLDOMProcessingInstruction Object ~ 475
XMLDOMProcessingInstruction object properties ~ 475
XMLDOMProcessingInstruction object methods ~ 475
The XMLDOMText Object ~ 476
XMLDOMText object properties ~ 476
XMLDOMText object methods ~ 476
The XMLDOMParseError Object ~ 478
XMLDOMParseError object properties ~ 478
XMLDOMParseError bject methods ~ 478
The XMLHttpRequest Object ~ 478
XMLHttpRequest object properties ~ 479
XMLHttpRequest object methods ~ 479

Chapter 22: Integrating XML with Internet Information
Server Applications ~ 483
Requesting Information ~ 483
Getting Customer Information With XML ~ 484
Building the Simple Web Page ~ 484
Requesting Customer Information ~ 486
Defining the XML documents ~ 486
Requesting a customer ~ 487
Getting a customer from the database ~ 492
Updating Customer Information ~ 496
Defining the update XML documents ~ 496
Requesting an update ~ 497
Processing an upda3te ~ 499

Part VI:SQL Server 505

Chapter 23: Overview of SQL Server ~ 507
Overview of SQL Server 7 ~ 507
SQL Server editions ~ 507
SQL Server utilities ~ 509
Database Architecture ~ 512
System databases ~ 512
SQL Server data types ~ 516
Connecting to SQL Server with ADO ~ 518
SQL Server Security ~ 518
Authentication in SQL Server ~ 518
SQL Server authorization ~ 520
SQL Server roles ~ 520

Chapter 24: Creating Database Objects with SQL Server ~ 523
Introducing Enterprise Manager ~ 523
Enterprise Manager fundamentals ~ 524
Registering a database server ~ 525
Viewing database servers and their objects ~ 527
Browsing data ~ 530
Databases and Tables ~ 531
Creating a database ~ 532
Creating a table ~ 535
Indexes and Diagrams ~ 539
Creating an index ~ 540
Creating a database diagram ~ 547
Managing Security ~ 550
Creating a login ~ 550
Granting permissions in a database ~ 553

Chapter 25: Creating Stored Procedures with SQL Server ~ 557
Introducing Stored Procedures ~ 557
Why use stored procedures? ~ 557
Introducing Transact-SQL ~ 559
Comments ~ 559
Identifiers ~ 560
Variables ~ 561
Functions ~ 561
Expressions ~ 563
Flow control ~ 563
Cursors ~ 565
Processing transactions ~ 570
Other useful statements ~ 571
Creating and Testing Stored Procedures ~ 573
Creating stored procedures in SQL Server ~ 574
Testing stored procedures in Query Analyzer ~ 575

Part VII: Oracle 8i ~ 579

Chapter 26: Overview of Oracle8i ~ 581
Overview ~ 581
Oracle8i editions ~ 582
Oracle8i utilities ~ 583
Understanding the Database Architecture ~ 586
Key database objects ~ 587
Memory architecture ~ 589
Network architecture ~ 590
Oracle8i data types ~ 590
Connecting to Oracle8i ~ 591
Oracle8i Security ~ 593
Authentication ~ 593
Authorization ~ 593
Roles ~ 596

Chapter 27: Creating Database Objects with Oracle8i ~ 599
Introducing SQL*Plus ~ 599
Command-Line SQL*Plus ~ 599
Connecting to your database with SQL*Plus ~ 600
Entering commands ~ 601
Useful commands ~ 601
Introducing Enterprise Manager ~ 603
The Enterprise Manager console ~ 604
SQL*Plus Worksheet ~ 607
DBA Studio ~ 610
Creating Tablespaces ~ 612
Before you create a tablespace ~ 612
Creating your tablespace ~ 613
Creating Tables and Indexes ~ 614
Creating your table ~ 615
Modifying a table ~ 619
Creating an index ~ 621
Managing Security ~ 621
Creating roles ~ 621
Creating users ~ 622
Granting permissions ~ 624

Chapter 28: Creating Stored Procedures with Oracle8i ~ 627
Introducing PL/SQL ~ 627
Comments ~ 627
Constants ~ 628
Identifiers ~ 628
Variables ~ 628
Functions ~ 629
Block structure ~ 630
Procedures, functions, and packages ~ 633
Expressions ~ 634
Flow control ~ 635
Cursors ~ 637
Transactions ~ 641
Other useful statements ~ 642
Creating Stored Procedures ~ 643
Creating a procedure or function ~ 644
Creating a package ~ 644

Part VIII: Microsoft Jet ~ 647

Chapter 29: Overview of Microsoft Jet ~ 649
Overview of Jet ~ 649
Microsoft Jet versions ~ 650
Jet and DAO ~ 651
Jet utilities ~ 652
Understanding the Database Architecture ~ 652
.MDB files ~ 652
.LDB files ~ 653
.MDW Files ~ 653
Database objects ~ 654
Linked databases ~ 654
Database capacities ~ 655
Jet data types ~ 655
Connecting to Jet with ADO ~ 655
Jet security ~ 658
Share-level security ~ 658
User-level security ~ 659

Chapter 30: Creating Database Objects with Microsoft Jet ~ 663
Introducing the Visual Data Manager ~ 663
Opening an existing database ~ 664
Viewing Database Information ~ 665
Running an SQL query ~ 665
Constructing Databases, Tables, and Indexes ~ 669
Creating a new database ~ 669
Creating a table ~ 670
Creating an index ~ 672
Using the DAO and ADOX alternatives ~ 673
Managing Security ~ 673
The security definition file ~ 673
Managing users and groups ~ 673
Managing permissions and ownership ~ 676

Appendix: CD-ROM Installation Instructions ~ 679
Index ~ 709
End Users License ~ 729
CD-ROM Installation Instructions ~ 733