Visual Basic Concepts

Setting Layout for the DataGrid Control

See Also

The DataGrid control has several methods and menu commands that affect both the layout of the grid, and how the control is bound to data. These methods and commands include:

• Clear Fields menu command (right-click the control, and click Clear Fields)
  
  
• ClearFields method
  
  
• HoldFields method
  
  
• Rebind method
  
  
• Retrieve Structure menu command (right-click the controls, and click Retrieve Structure)

Setting Layout at Design Time

One feature of the DataGrid is its design-time features. You can configure a data source, such as the ADO Data control, connect it to the DataGrid control, invoke the Retrieve Structure menu command, and customize the layout of the grid.

1. Place a DataGrid control and an ADO Data control on a form.
   
   
2. Configure the ADO Data control to connect to a database.
   
   
3. Set the DataGrid control's DataSource property to the ADO Data Control.
   
   
4. Right-click the DataGrid control and click Retrieve fields.

   The grid is now configured with a default layout: one column per field in the recordset, and each column set to the same width.

5. Right-click the DataGrid control and click Edit. This puts the control in edit mode.
   
   
6. Using the mouse, edit the column layout of the grid. Decrease or expand width, or hide columns altogether.
   
   
7. Alternatively, right-click the control and click Properties. Use the dialog box to customize the layout of the grid.

Preserve or Cancel Layout Using HoldFields and ClearFields

If you have edited the layout as shown above, you can now use the HoldFields and ClearFields methods to either preserve or cancel the layout when the Rebind method is invoked. This only becomes apparent when the recordset itself has been altered. In that case, if the HoldFields method has been invoked, the layout is preserved, but blank columns are substituted for missing fields. On the other hand, if the ClearFields method is invoked, all missing fields are deleted, and only the fields present in the altered recordset are displayed. A sample is shown below.

To show the effects of HoldFields and ClearFields

1. Place an ADO Data control on a form.
   
   
2. Right-click the control and click ADODC Properties.
   
   
3. Set the Data Link file to the Northwind.udl.

   See the topic Creating the Northwind OLE DB Data Link for an example.

4. Click the RecordSource tab.
   
   
5. Set the CommandType to adCmdTable.
   
   
6. Set the table name to Products.
   
   
7. Place a DataGrid control on a form, and set the DataSource property to ADODC1.
   
   
8. Right-click the DataGrid control and click Retrieve fields.
   
   
9. Right-click the DataGrid control and click Edit.
   
   
10. Resize the columns of the grid.
    
    
11. Place one CommandButton and one CheckBox control on the form.
    
    
12. Paste the following code into the code window:

    Option Explicit
    
    Private Sub Form_Load()
        ' Reset the captions for the controls and set the CheckBox
        ' Value to vbChecked.
        Command1.Caption = "Rebind"
        Check1.Caption = "Hold/Clear Fields"
        Check1.Value = vbChecked ' Set to HoldFields.
    End Sub
    
    Private Sub Command1_Click()
        ' The ReDoADODC sub reconfigures the ADO Data Control to deliver an
        ' altered recordset.
        ReDoADODC
        ' Use either HoldFields or the ClearFields method depending on the
        ' value of the checkbox.
        If Check1.Value = vbChecked Then
            With DataGrid1
                .HoldFields
                .ReBind
            End With
        Else ' unchecked means Clearfields.
            With DataGrid1
                .ClearFields
                .ReBind
            End With
        End If
    End Sub
    
    Private Sub ReDoADODC()
        With Adodc1
            .CommandType = adCmdText
            .RecordSource = "SELECT ProductName, UnitPrice FROM Products"
            .Refresh
        End With
    End Sub
    

13. Run the project.

Trying the options

When the project runs, the DataGrid should be filled with the data from the Northwind database, and its layout should be as you designed it. By default, the CheckBox is set to the "Holdfields" setting.

1. Click the Rebind button.

   Notice that several columns are now blank.

2. Clear the " Hold/Clear Fields" check box.
   
   
3. Click the Rebind button.

   Notice that only the layout of the grid is now set according to the number of columns (2) in the recordset.

4. Check the " Hold/Clear Fields" check box again.
   
   
5. Change the width of any column.
   
   
6. Click the Rebind button.

   The grid will revert to the previous layout even though the HoldFields method has been invoked. This is because the control doesn't yet know what the custom layout is.

7. Change the width of any column.
   
   
8. Click the Rebind button.

   The grid will now retain the previous layout.

9. Uncheck the "Hold/Clear Fields" check box.
   
   
10. Click the Rebind button.

    The grid will display only the two columns with default widths.