Visual Basic Concepts

Using the DataGrid Control

See Also

The DataGrid control is a spreadsheet-like bound control that displays a series of rows and columns representing records and fields from a Recordset object. You can use the DataGrid to create an application that allows the end user to read and write to most databases. The DataGrid control can be quickly configured at design time with little or no code. When you set the DataGrid control's DataSource property at design time, the control is automatically filled and its column headers are automatically set from the data source's recordset. You can then edit the grid's columns; delete, rearrange, add column headers to, or adjust any column's width.

At run time, the DataSource can be programmatically switched to view a different table, or you can modify the query of the current database to return a different set of records.

Note   The DataGrid control is code-compatible with the DBGrid control that shipped in Visual Basic 5.0 with one exception: the DataGrid control doesn't support the DBGrid notion of "unbound mode." The DBGrid control is included with Visual Basic in the Tools directory.

Possible Uses

• View and edit data on a remote or local database.
  
  
• Used in conjunction with another data-bound control, such as the DataList control, use the DataGrid control to display records from one table that are linked through a common field to another table displayed by the second data-bound control.

Using the Design-Time Features of the DataGrid Control

You can create a database application with the DataGrid control without writing a line of code by taking advantage of its design-time features. The following instructions outline the general steps needed to implement the DataGrid control in a typical use. For complete step-by-step instructions, see the topic "DataGrid Scenario 1: Create a Simple Database Application with the DataGrid Control."

To implement a DataGrid control at design-time

1. Create a Microsoft Data Link (.UDL) file for the database you wish to access. See the topic "Creating the Northwind OLE DB Data Link" for an example.
   
   
2. Place an ADO Data control on a form, and set the ConnectionString property to the OLE DB data source created in step 1.
   
   
3. In the RecordSource field of the Ado Data control, type a SQL statement that returns a recordset. For example,

   Select * From MyTableName Where CustID = 12
   

4. Place a DataGrid control on a form, and set the DataSource property to the ADO Data control.
   
   
5. Right-click the DataGrid control and then click Retrieve Fields.
   
   
6. Right-click the DataGrid control and then click Edit.
   
   
7. Resize, delete, or add columns to the grid.
   
   
8. Right-click the DataGrid control and then click Properties.
   
   
9. Using the Property Pages dialog box, set the appropriate properties of the control to configure the grid as you wish it to appear and behave.

Changing Displayed Data at Run Time

Once you have created a grid using the design-time features, you may also wish to dynamically change the data source of the grid at run time. The general methods for accomplishing this are discussed below.

Changing the RecordSource of the DataSource

The most common method of changing displayed data is to alter the query of the DataSource. For example, if the DataGrid control uses an ADO Data control as its DataSource, rewriting the RecordSource and refreshing the ADO Data control will change the data displayed.

' The ADO Data control is connected to the Northwind database's
' Products table. The new query asks for all records which have
' the SupplierID = 12.
Dim strQuery As String
strQuery = "SELECT * FROM Suppliers WHERE SupplierID = 12"
Adodc1.RecordSource = strQuery
Adodc1.Refresh

Changing the DataSource

At run-time you can reset the DataSource property to a different data source. For example, you may have several ADO Data controls, each connected to different databases, or set to different RecordSource properties. Simply reset the DataSource from one ADO Data control to another:

' Reset the DataSource to an ADO Data control that is connected to
' the Pubs database, using the Authors table.
Set DataGrid1.DataSource = adoPubsAuthors

Rebind the DataSource

When using the DataGrid control with a remote database such as SQLServer, it's possible that the structure of the table may become altered. For example, a field may be added to the table. In that case, you can invoke the Rebind method to recreate the grid from the new structure. Note that if you have altered the columns' layout of the grid at design-time, the DataGrid control will attempt to recreate the current layout, including any empty columns. You can, however, force the grid to reset all columns by first invoking the ClearFields method.

Returning Values from the DataGrid

Once the DataGrid is connected to a database, you may want to monitor which cell the user has clicked. Use the RowColChange event — not the Click event — as shown below:

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
   ' Print the Text, row, and column of the cell the user clicked.
   Debug.Print DataGrid1.Text; DataGrid1.Row; DataGrid1.Col
End Sub

Using the CellText and CellValue Methods

The CellText and CellValue properties are useful when a column has been formatted using the NumberFormat property. The NumberFormat property changes the format of any column that contains a number without changing the format of the actual data. For example, given a grid with a column named ProductID that contains integers, the code below will cause the DataGrid to display the values in the format "P-0000." In other words, although the actual value held in the ProductID field is "3," the value displayed by the grid will be "P-0003."

Private Sub Form_Load()
   DataGrid1.Columns("ProductID").NumberFormat = "P-0000"
End Sub

To return the actual value contained in the database, use the CellValue method, as shown below:

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
   Debug.Print _
   DataGrid1.Columns("ProductID").CellValue(DataGrid1.Bookmark)
End Sub

Note   Both the CellValue used above, and the CellText value used below, require the bookmark property as an argument to function correctly.

Conversely, if you want to return the formatted value of the field, use the CellText method:

Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
   Debug.Print _
   DataGrid1.Columns("ProductID").CellText(DataGrid1.Bookmark)
End Sub

Note   The CellText method above is equivalent to using the Text property of the DataGrid control.

Where to Go From Here

To read a step-by-step procedure for building a simple application with the control, see "Creating a Simple Database Application with the DataGrid Control," or "Creating a DataGrid Linked to a DataList Control."

To learn more about Split objects and how to program them, see "Manipulating DataGrid Views."