prepared by James T. Perry University of San Diego

1. prepared by James T. Perry University of San Diego

2. 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

3. 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

4. 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

5. 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

6. 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 2-adCmdTable 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”

7. 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

8. 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

9. 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)

10. 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

11. 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

12. 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

13. 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

14. 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 = “”

15. Working with Database Fields • Refer to DB field this way: • datControl.Recordset!LastName • datControl.Recordset(“LastName”) • Refer to DB field this way: datControl.Recordset(“Pub Date”) Or datControl.Recordset![Pub Date]

16. 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

17. Hands on Programming Example