Transcript Chapter 12

PowerPoint® Presentation
to accompany
prepared by
James T. Perry
University of San Diego
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Ch12: Advanced Data Handling






Write database app. with DB Grid control
Database validation and error-trapping
Locate database records using criteria
Define tables, dynasets, and snapshots
Create database indexes
Create a dynaset using SQL
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Displaying Data in Grids

Using a grid control
– Add data control to a form.
– Project/Components to add M.S. Data Bound
Grid control to toolbox.
– Put Dbgrid on form.
– Retrieve fields into grid: right-click and then
choose Retrieve Fields
– Modify grid's properties: Right-click and select
Properties
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Displaying Data in Grids
(Revised Procdure) (1)

Add new components to the toolbox:
– Select Project, Components
– Locate and check Microsoft ADO Data Control 6.0
(OLEDB) and Microsoft DataGrid Control 6.0
(OLEDB); close dialog box

Add ADO data control to form & name it
adodatbooks

Add DataGrid to form and name it
dbgridBooks
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Displaying Data in Grids
(Revised Procdure) (2)

Set up Data Source:
– Right-click the data control and select ADODC
Properties
– Click General tab, Click Use Connection
String option button
– Click Provider tab & select Microsoft Jet 3.51
OLE DB Provider
– Click Next button
– On the Connection tab, browse to select
RnRBooks.mdb
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Displaying Data in Grids
(Revised Procdure) (3)

Set up the Data Source continued:
– Click the Test Connection button. Dialog box
should appear indicating connection succeeded.
– Close Data Link Properties dialog box
– Click the RecordSource tab; select 2adCmdTable for Command Type
– Select Books from the Table or Stored
Procedure drop down list
– Close the Property Pages dialog box

Continue with textbook: “Set the Properties of the
Grid”
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Displaying Record Number & Count

Display record number and record count
with RecordCount and
AbsolutePosition properties
intCurrentRecord =
datBooks.Recordset.AbsolutePosition + 1

If at BOF or EOF, AbsolutePosition is 0
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Opening the Database




VB automatically opens a database that has
a data control on a form
The open occurs after form load
Use the Refresh method to open a
database manually in the Form_Load event
procedure
Use the data control’s Reposition event to
display the current record number
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Validation and Error Trapping




Lock text boxes by setting their Locked
property to True; unlock with False
Usually, you lock the primary key field of a
database unless you are adding a record
The Validate event is a good place to
check a field’s value
Set the Causes Validation property to
True to perform lost focus validation on a
field (default is True)
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Trapping Program Errors



Errors may be trapped asynchronously
Visual Basic generates an error number
when an error occurs
To handle errors, you must
– Turn on error handling feature: On Error...
– Create error handling code
– Determine what is to be done afterwards
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
The Err Object





The Err object holds info about error that
just occurred
Err.Source holds the source of the error
Err.Number holds the error number
Err.Description contains error description
You can raise an error condition--turn on an
error--with: Err.Raise Number:=xx
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Coding Error-Handling Routines






On Error statement designates error handler
Code to handle errors follows line label
Line label appears on line by itself and ends
with a colon
A Case statement works well to sort out
errors with a "switchboard"
Continue execution with Resume statement
Exit statement leaves procedure early
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Recordsets

VB supports three kinds of recordsets:
– Table recordset—data from a single table
– Dynaset—temporary dataset from a table or query that
can be updated
– Snapshot—dynaset that cannot be updated



Recordset property set at design time or run time
Search for records with FindFirst, FindLast,
FindNext, or FindPrevious methods
Form: datControl.Recordset.Findxxxx criteria
where criteria is the search criteria
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Reordering a Table Recordset




By switching indexes, you can view a
dynaset or table in different orders
One index displays records in PK order,
while another might display records in Title
order
Form: datacontrol.Recordset.Index = “Fieldname”
where Fieldname is one of the fields
Set to data entry order with:
datacontrol.Recordset.Index = “”
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Working with Database Fields

Refer to DB field this way:
1.datControl.Recordset!LastName
2.datControl.Recordset(“LastName”)

Refer to DB field this way:
datControl.Recordset(“Pub Date”)
Or
datControl.Recordset![Pub Date]
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Creating a New Dynaset




Dynaset may be a table or multiple, joined
tables
VB uses Structured Query Language (SQL)
to create dynasets
SQL is world-wide database access
standard
Set the RecordSource property to a SQL
string and use Refresh to execute query
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.
Hands on Programming Example
Irwin/McGraw-Hill
Copyright© 2000 by the McGraw-Hill Companies, Inc.