Chapter 12

1. Chapter 12 Advanced Data Handling - Grids, Validation, Selection, and Sorting

2. DataGrids - Microsoft DataGrid Control 6.0 (dbg prefix) • Presents the table data in rows and columns like a spreadsheet or datasheet view in Access • In order to use the datagrid with ADO, you must first add the Microsoft ADO Data Control 6.0 (adodc) to the toolbox • Project, Components, Microsoft ADO DataControl 6.0 • Then add the datagrid itself to the toolbox • Project, Components, Microsoft DataGridControl 6.0

3. DataGrids - Microsoft DataGrid Control 6.0 (dbg prefix)cont. • Draw the ADODC on your form • Set the properties of the ADODC to point to the database • Name, ConnectionString, CommandType, RecordSource • Draw the DataGrid on your form • Link the DataGrid to the ADODC using the DataSource property • Right Click the DataGrid, Retrieve Fields • To modify, Right Click, Edit, then select columns for deletion or resize columns

4. Accessing DataGrid Properties • Some properties are available in the Properties window • More properties are available from the Property Pages • Properties Window, Custom • OR, Right Click the DataGrid and select Properties

5. DataGrid - Property Pages Properties • AllowAddNew • Allow user to add new database records from the grid • AllowDelete • Allow user to delete database records from the grid • Row height • Column width

6. Record Count and Record Number • RecordCount property of the recordset holds the number of records in the recordset • AbsolutePosition property of the recordset hold the position of the current record in the recordset • AbsolutePosition=0 at BOF and EOF intRecordCount=adoBooks.Recordset.RecordCount intCurrentRecord=adoBooks.Recordset.AbsolutePosition

7. Displaying Record Info • Some programmers display the record info in the Caption of the ADODC • To display the record number as the user moves from record to record use the ADODC's MoveComplete event which occurs each time a new record becomes current

8. Displaying Record Info -SetRecordNumber Subprocedure • Private Sub SetRecordNumber( ) • Dim intCurrentRecord as Integer • Dim intRecordCount as Integer • With adoBooks.Recordset • intCurrentRecord = .AbsolutePosition intRecordCount = .RecordCount • End With • If adoBooks.Recordset.EOF Then • adoBooks.Caption = "EOF" • Else • adoBooks.Caption="Record " & intCurrentRecord & _ • " of " & intRecordCount • End IF • End Sub

9. MoveComplete Event -Calling SetRecordNumber Private Sub adoBooks_MoveComplete(ByVal adReason As _ADODB.EventReasonEnum, _ByVal pError as ADODB.Error, _adStatus as ADODB.EventStatusEnum, _ ByVal pRecordset as ADODB.Recordset) SetRecordNumber End Sub

10. Preventing User Errors • Set TextBox's MaxLength equal to database field length • Use TextBox's Lock property when you don't want the user to change data, like primary key when not adding a record • Validate user data BEFORE updating the database

11. Validating Data-Validate Event • Use Validate event to perform data validation • CausesValidation property=True will invoke the Validate event just before the control loses focus (default is True!) • Always set CausesValidation to False for Cancel command button to allow the user "a way out"

12. Validate Event Example Private Sub txtEmpID_Validate(Cancel as Boolean) If Not IsNumeric(txtEmpID) Then MsgBox "Employee Id must be numeric", vbOKOnly,"Invalid Emp ID" With txtEmpID .SelStart = 0 .SelLength = Len(.Text) End With Cancel =True 'resets focus to txtEmpID End If End Sub

13. Trapping Errors with On Error • Include error handling for every subprocedure that accesses the database • Open • Add • Delete • Update • Move • Trap errors and inform user

14. Handling Errors on Moves • Code On Error Resume Next to ignore the error and continue execution Private Sub cmdFirst_Click( ) On Error Resume Next adoBooks.Recordset.MoveFirst End Sub

15. Handling Errors On Delete • Notify user using MsgBox • Code On Error GoTo 0 to turn off error trapping and return control to the user

16. Handling Errors On Delete Example Private Sub cmdDelete_Click( ) On Error GoTo ErrorHandler With adoBooks.Recordset .Delete .MoveNext . . . see P496 for complete code ErrorHandler: Dim strMsg as String strMsg="Cannot delete record." & vbCrLf & Err.Description MsgBox strMsg, vbExclamation, "Database Error" On Error GoTo 0 End Sub

17. Handling Errors on Add • Depending on the thoroughness of your data validation in the Validate event various errors can be generated during Add • Most common error is duplicate, invalid, or missing primary key • Error handling code will vary depending on what you want to do as a programmer • Give the user another chance to correctly enter data • Abort add

18. Handling Errors on Add Example • Review textbook example • Pages 496 - 497 • Error: primary key error, ISBN field • Error Handling: allows the user to try again to enter a valid primary key

19. Searching for Records • Find Method • Returns single record matching specified criteria • Filter Property • Returns a subset a records that match the specified criteria

20. Find Method Syntax adoDatControl.Recordset. Find Criteria[, [RowsToSkip], [SearchDirection] ] • Criteria: String to specify what to search for • RowsToSkip: Optional, specify number of rows/records to skip from current record before starting to search • SearchDirection: Optional • adSearchForward (default) • adSearchBackward

21. Find Method Examples adoMembers.Recordset.Find " Amount > 100" adoMembers.Recordset.Find " Amount > " & txtAmt.Text Dim strCriteria as String strCriteria = "[Last Name]= 'Weldon'" adoPatient.Recordset.Find strCriteria Important Rules: 1. Strings must be enclosed in double quotes, strings inside strings must be enclosed in single quotes 2. Database field names that contain spaces must be enclosed in square brackets

22. Search String Examples • Field name does not contain spaces • "Sex = ' M '" • If field name contains spaces add square brackets • "[First Name] =' Smith '" • If data searched for is numeric single quotes are omitted • "Age < 21"

23. More Search String Examples • If data searched for is in a textbox • "[Patient Number] = ' " & txtPatNum.Text & " ' " • If data searched for is a date use # symbols • "Birthdate > #1/1/1980 #" • Wild Cards • "[Last Name] Like'S* '"

24. Preparing for "No Record Found" • Set a Bookmark before beginning Find so you can return to the record which was current before the find if no record found • If no record is found the recordset will be at EOF • Always test for EOF after Find method

25. Bookmarks • Store in variable of variant data type (prefix vnt) • Store before Find method in the variable by using the Recordset's Bookmark property to save the current record • If no record found, set the Recordset's Bookmark property equal to the previously stored variable

26. Find with Bookmark Example Private Sub cmdFind_Click( ) Dim strCriteria as String Dim vntCurrentRecord as Variant If txtLastName < > "" Then strCriteria="[Last Name] = ' " & txtLastName & " ' " With adoPatient.Recordset vntCurrentRecord=.Bookmark .MoveFirst .FindNext strCriteria If .NoMacth=True Then MsgBox "Record Not Found", vbInformation, "Find" .Bookmark=vntCurrentRecord End If End With Else MsgBox "Enter a name to be found" txtLastName.SetFocus End Sub

27. Filter Property • Creates a new temporary recordset by selecting all records than match the specified criteria • No changes are made to the underlying table

28. Filter Property Syntax Object.Recordset.Filter =Criteria • Criteria: String to specify what to search for • Use the same rules for constructing the criteria string as shown on previous slide for Find method

29. Removing the Filter • Remove the filter to display entire recordset • Set Filter property to adFilterNone • adoBooks.Recordset.Filter=adFilterNone

30. Sort Property • You can sort a recordset by any field using the recordset's Sort property • You can sort by multiple fields by separating them with commas • Create a menu or a listbox for user to select field(s) to sort by • To return recordset's default sort order usually sort by the primary key

31. Sort Property Syntax & Examples adoBooks.Recordset.Sort = "Author" adoBooks.Recordset.Sort = "Author, Title" adoBooks.Recordset.Sort = "ISBN"

32. Working with Database Fields • If your controls ever need to appear empty you can write code to work directly with the database fields • If user needs to pick from a list of values and based on user's selection other controls are automatically populated • Gives you more programmatic control

33. Referring to Database Fields • All these are equivalent: Field Names containing spaces - adoAVB.Recordset![Last Name] adoAVB.Recordset!("Last Name") adoAVB.Recordset!"Last Name" Field Names without spaces - adoAVB.Recordset!Insurance adoAVB.Recordset!("Insurance") The exclamation point is called the BANG operator.

34. Code Examples • Populating a form's textbox control txtLName=adoAVB.Recordset![Last Name] • Writing to the database field from a form's textbox control adoAVB.Recordset![Last Name]=txtLName The exclamation point is called the BANG operator.

35. Populating a Combo or List (p508) • Use Do Until .EOF for the Recordset during Form_Load or Form_Activate Private Sub Form_Load ( ) With adoAVB .Recordset Do Until .EOF If !Lname <> " " Then cboLName.AddItem !LName End If .MoveNext Loop .Close End With End Sub

36. Data Environment Designer • Not available in the Working Model-new in VB 6 • Must be added - Project, Add Data Environment • Visual Interface that allows programmers to set up all the database connections and recordsets in one location • After the connections and recordsets are setup, the programmer can simply drag objects from the DE designer to a form to generate data bound-controls automatically

37. Data Environment (de prefix) • The Data Environment has a hierarchical interface • The Data Environment is at the top of the hierarchy, used instead of the ADO control • The Data Environment is made up of Connections, used instead of the ConnectionString of the ADO control • Connections are made up of Commands, used instead of the RecordSource • Commands are made up of Fields

38. Data Environment - graphically DataEnvironment (like ADODC) DEConnection (like ConnectionString) deAdvancedVision conAVB DECommand (like RecordSource) Patient PatientNumber LastName FirstName Street Fields

39. Steps For Using DE, 1 of 5 • Several possible methods for adding DE, use one • Project Menu, Add Data Environment • Project, Add ActiveX Designers, Data Environment • Project, Components, Designers tab, Data Environment

40. Steps For Using DE, 2 of 5(cont.) • Once the DE has been added, the DE window appears • Rename DE by clicking • Rename Connection by clicking • Set Connection properties by right clicking to access properties window • Data Link Properties • Provider • Connection

41. Steps For Using DE, 3 of 5(cont.) • Add Command object by right clicking Connection • Rename Command by clicking • Set Command properties by right clicking to access properties window • CommandType ==> adCmdTable • CommandText ==> actual table name

42. Steps For Using DE, 4 of 5(cont.) • Add Command object by right clicking Connection • Rename Command by clicking • Set Command properties by right clicking to access properties window • CommandType ==> adCmdTable • CommandText ==> actual table name

43. Steps For Using DE, 5 of 5(cont.) • View list of fields in table by clicking plus symbol beside command object • If necessary, resize/move DE window so that you can see both the DE window and form • Create bound controls on form by dragging fields from DE to form

44. Recordset Object • When using the DE Designer the Command object automatically creates a Recordset object named as follows: • Prefix of "rs" + Command object's name • Ex: Command object name=Patient, Recordset name used in code is rsPatient • Reference in code: • deAVB.rsPatient.property or method • Example: deAVB.rsPatient.MoveNext

45. Simple Code Comparison: ADODC and DE • ADODC: adoPatient.RecordSet.MoveNext • DE: deAdvancedVision.rsPatient.MoveNext

46. Open a Database (DE) • VB automatically handles the opening of a database using the Data Environment Designer • VB uses the hard-coded connection string of the DE's Connection Object • Your VB Project is not portable unless you remove the hard-coded connection string and add code using App.Path to create the connection string, just like ADODC

47. Using App.Path with DE to Open the Database • Use DE's Initialize event (rather than Form_Load event as with ADODC) • Initialize Event fires before any connections are made • Code the Connection object's ConnectionString (before we coded the ADODC's ConnectionString) Private Sub DataEnvironment_Initialize( ) conAVB.ConnectionString="Provider=Microsoft.Jet.OLEDB.3.51;" & _ "Persist Security Info=False;" & _ "Data Source=" & App.Path & "\AVB.mdb" End Sub

48. Close a Database (DE) • Use the Close method of the DE's Connection object to close a database • Place this code in the Data Environment's Terminate event (rather than in a form's Unload event as with ADODC) conAVB.Close

49. Closing the Database Example • Use DE's Terminate event (rather than Form_Unload event as with ADODC) Private Sub DataEnvironment_Terminate( ) conAVB.Close End Sub

50. One-to-Many Relationships, Data Hierarchies (Pages 518-522) • Use the DE Designer to create hierarchies of data • Similar to relationships that join tables • Data hierarchies are useful for displayingOne-to-Many (1-M) relationships • Use MSHFlexGrid control to display the hierarchies created in the DE Designer • Project, Components, Microsoft Hierarchical FlexGrid Control 6.0 (OLE DB)