Visual Basic Concepts

Creating a DataGrid Linked to a DataList Control

A common use of the DataGrid is to show "details" supplied by one table in a database. For example, the Northwind (Nwind.mdb) database includes two tables, one named "Suppliers," and the other named "Products." In this example, we'll use the DataList control to show the company names of suppliers from the "Suppliers" table. When the user clicks on any company name, the DataList control will furnish the SupplierID for the company. Using that ID, a query can be constructed to retrieve all records in the "Products" table which have a matching SupplierID. In other words, when the user clicks on a company (in the DataList control), all of the products produced by that company will appear in the DataGrid control.

To fill a DataGrid Control with products from a particular supplier

1. Ensure that an OLE DB data source for the Northwind database is present on the machine; if such a datas source has not been created, follow the steps in "Creating the Northwind OLE DB Data Link."

2. Create a new standard EXE project in Visual Basic.

   If the DataGrid and DataList and ADO Data controls are not present in the Toolbox, right-click the Toolbox, and click Components.****In the Components dialog box double-click Microsoft DataGrid Control, Microsoft DataList Controls and Microsoft ADO Control.

3. Place an instance of the DataGrid and DataList controls on the blank form.

   Place the DataList control in the top left corner of the form, and place the DataGrid control somewhat below it.

4. Place two instances of the ADO Data control on the form.

   Select the first ADO Data control and press F4 to display its Properties page. Set the Name property of the control to adoSuppliers. Select the second ADO Data control and set its Name property to adoProducts. Place the first control directly underneath the DataList Control, and the second directly below the DataGrid control.

5. Set the ConnectionString property of the two ADO Data controls to the Northwind OLE DB data source.

   Select the control named adoSuppliers and set the ConnectionString property to the Northwind OLE DB data source (Northwind.udl). Select the control named adoProducts and repeat the operation.

6. Set the RecordSource property of the two ADO Data controls.

   Select adoSuppliers and click RecordSource on the Properties page. Type Select * From Suppliers. This query instructs the ADO Data control to return all records in the Suppliers table. Select adoProducts,****click RecordSource, and type Select * From Products. This query returns all the records from the Products table.

7. Set the RowSource property of the DataList Control to adoSuppliers.

   The RowSource property determines which data source supplies the data for the ListField property.

8. Set the ListField property of the DataList control to CompanyName.

   The ListField property is set to the name of a field in the table named Suppliers. At run-time, the DataList control displays the value of the field specified in this property. In this example, the property will display the name of a company found in the Suppliers table.

9. Set the BoundColumn property of the DataList control to SupplierID.

   The BoundColumn property is set to a second field in the Suppliers table. In this case, the property is set to the SupplierID field. When the DataList control is clicked, the BoundText property returns the value of the SupplierID field associated with the company displayed in the DataList control. This value will be used in a query of the Products table, which provides data for the DataGrid control.

10. Set the DataSource property of the DataGrid control to adoProducts.

    The DataSource property specifies the data source for the control. In this case, the property is set to the ADO Data control named adoProducts, which returns all of the records in the Products table.

11. In the code module for the form, add the following:

    Private Sub Datalist1_Click()
       ' Declare a string variable that will contain a new query. The
       ' new query uses the BoundText property of the DataList control
       ' to supply a SupplierID value. The new query simply asks for
       ' all products with the same SupplierID. This query is assigned
       ' to the RecordSource property of the ADO Data control named
       ' adoProducts. After refreshing the control, the DataGrid is
       ' updated with the new recordset of all products that are
       ' supplied by the same company.
    
       Dim strQuery As String
       strQuery = "Select * FROM Products WHERE SupplierID = " & _
       Datalist1.BoundText
    
       With adoProducts
          .RecordSource = strQuery
          .Refresh
       End With
    
       With DataGrid1
          .ClearFields
          .ReBind
       End With
    End Sub
    

12. Run the project.

    Click any company name in the DataList control, and the DataGrid control is automatically updated with all products supplied by the company.