1 / 14

BookMark

BookMark. Recordset’s current record may change. Use bookmark to mark a specific record so that you can return to it later. Store the current record’s bookmark in a variant variable. Dim varBookMark as Variant The current location may be changed by methods such as MoveNext, etc.

janus
Download Presentation

BookMark

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BookMark • Recordset’s current record may change. • Use bookmark to mark a specific record so that you can return to it later. • Store the current record’s bookmark in a variant variable. • Dim varBookMark as Variant • The current location may be changed by methods such as MoveNext, etc. • To return to the bookmark you have stored: • adoRecordsetName.BookMark = varBookMark

  2. AddNew • Using bound controls with ADO Data Control or Data Environment Command object: adoRecordsetName.AddNew • Using unbound controls: • A form with unbound textboxes is initialized for input. • After entering data, a Save/Add button is clicked and the event handler uses a pair of AddNew and Update methods to insert record.

  3. Unbound Control Input Example With DataEnvironment1.rsCommand1 .AddNew .Fields("CID") = txtCID.Text !Cname = txtCNAME.Text !City = txtCITY.Text !Rating = txtRATING.Text .Update End With

  4. Find and BookMark • Syntax: adoRecordsetName.Find criteria • Only accept simple comparison criteria. • Example: • Adodc1.recordset.Find “CID = ‘” & txtCID & “’” • Finding employees hired between 2/15/2000 and 5/15/2000: • Adodc1.recordset.Find “Hdate > #2/15/2000#” Then use IF statement in program to filter out those hired after 5/15/2000 If no match is found, the EOF of the recordset will be set to True.

  5. Find Code Example Private Sub txtCID_Validate(Cancel As Boolean) DataEnvironment1.rsCommand1.MoveFirst DataEnvironment1.rsCommand1.Find "CID = '" & txtCID.Text & "'" If Not DataEnvironment1.rsCommand1.EOF Then txtCNAME.Text = DataEnvironment1.rsCommand1.Fields("Cname") txtCITY.Text = DataEnvironment1.rsCommand1.Fields("City") txtRATING.Text = DataEnvironment1.rsCommand1.Fields("Rating") MsgBox "Record already exist, Add operation cancelled" DataEnvironment1.rsCommand1.Cancel Command2.Enabled = False End If Demo

  6. Hierarchical Recordsets • Data Shaping: A new ADO feature that retains a 1:M (Parent-Child) relationship by creating a hierarchical recordset. • Shape command syntax: • Shape {Select …..} As ParentName Append ( {Select ….} As ChildName Relate ParentFieldName TO ChildFieldName) As ChildName

  7. Shape Command Example Shape {Select * From Customers} As Customers Append ({Select * From Orders} As Orders Relate CustomerID TO CustomerID) As Orders Note:As ParentName, As ChildName are optional. The Shape clause constructs a parent recordset. The Append clause creates child recordset using the fields specified in the Relate clause to organize the relationship.

  8. DataGrid and Hierarchical FlexGrid (HFG) • Display recordset records in rows and columns. • DataGrid allows update, HFG is read only. • HFG supports hierarchical recordset. • Use MSDataShape provider to bind data to HFG. • Property Page: Columna & Rows • Property Window: DataSource • Demo

  9. Referencing the Hierarchical Recordset • For applications that display 1:M relatonship in sync. • The parent recordset can be created by Data Environment, ADO Data Control, or recordset’s Open method. • The childname in the Shape command is treated as a field in the parent recordset. To access the child recordset for a particular parent, do: • Set childRecordsetName = parentRecordsetName.Fields(“childname”).Value • Demo

  10. Shape Command Example shape {select CustomerID, CompanyName, City from Customers} As Customers Append ({Select OrderID, CustomerID, EmployeeID, OrderDate From Orders} As Orders Relate CustomerID to CustomerID) As Orders

  11. 1:M Code Example Dim rsParent As ADODB.Recordset Dim rsChild As ADODB.Recordset Private Sub Form_Load() Set rsParent = DataEnvironment1.rsCommand1 rsParent.Open Do Until rsParent.EOF List1.AddItem rsParent.Fields("CustomerID") rsParent.MoveNext Loop End Sub Private Sub List1_Click() rsParent.MoveFirst rsParent.Find "CustomerID = '" & List1.List(List1.ListIndex) & "'" Set rsChild = rsParent.Fields("Orders").Value Set MSHFlexGrid1.DataSource = rsChild End Sub

  12. Using MSDataShape without ODBC DSN • Click Provider and choose MSDataShape • Click ALL • Edit Data Provider: For Access 2000, enter: • Microsoft.Jet.OleDB.4.0 • Edit Data Source: Enter the path to your Access database

  13. Creating 1:M Relationship with Data Environment • Right-click on the command object and select Add Child Command • In the child command, select Relationship tab and check Relate to a Parent Command Object

  14. Creating Grouping and Aggregates in a 1:M Relationship • Add a Child Command. • Set Relation. • Set Grouping • Add Aggregates

More Related