|Best ADO Cloning & Filtering Practices||
|NT, 9x, 2000
Situation: user wants to make selections in a data list view that sets and populates a data grid based on criteria. (i.e. an "orderline" list view that populates a "booked spots detail" grid for the orderline)Mission: development standards requires that applications remain "stateless" allowing for ease of portability to-and-from either a client-server, component based, or web based version of the application; basically keeping continuous and number of connections to the database to an absolute minimum.
' create your base ADO recordset Private Sub PopulateMyBaseRecordset() ' use your company standard to create your base recordset Set MyDataAccessObject = CreateObject("ADO_Component") Sql = "SELECT OrderID, OrderLineID, PlayStartDate, PlayEndDate FROM OrderLines WHERE (1=1)" ' note - the sql select should include all of the fields that you would need ' for all of the data you would want to get for x number of clones you wish to make ' with the least amount of filtering. ' Here is your base data recordset Set MyBaseOrderlineRS = MyDataAccessObject.SQLRetrieve(MyConnectionString, Sql) ' Here is your first (control) data list view Set MyOrderlineListView.DataSource = MyBaseOrderlineRS Set MyDataAccessObject = Nothing End Sub ' now create your detail recordset Private Sub PopulateMyDetailRecordset() ' use your company standard to create your base recordset Set MyDataAccessObject = CreateObject("ADO_Component") Sql = "SELECT * FROM ScheduleLog WHERE (1=1) AND PlayStartDate >= '01/01/2001'" ' Here is your detail data recordset Set MyOrderlineDetailRS = MyDataAccessObject.SQLRetrieve(MyConnectionString, Sql) Set MyDataAccessObject = Nothing End Sub ' lastly create your clone and filter it on your base data recordset's list view Private Sub PopulateMyCloneRecordset() ' validate your base data recordset If MyBaseOrderlineRS Is Nothing Then Exit Sub 'now create your clone and bind it to it's base data list view and recordset Set MyOrderlineDetailCloneRS = Nothing Set MyBookedSpotsGridView.Datasource = Nothing Set MyOrderlineDetailCloneRS = MyOrderlineDetailRS.Clone MyOrderlineDetailClone.Filter = "OrderID = " & MyOrderlineListView.Fields(0).Value & _ " AND OrderLineID = " & MyOrderlineListView.Fields(2).Value & _ " AND PlayStartDate >= " & MyOrderlineListView.Fields(3).Value ' you now have a recordset cloned to one recordset but filtered on another ' Here is your second (control) data grid Set MyOrderlineDetailGrid.DataSource = MyOrderlineDetailClone ' Format your detail data grid based on what you ' need/don't need out of the MyOrderlineDetailRS ' Call your nested clones and population procedures... End Sub