Use of the DataGrid control in VB (i)

Using the DataGrid control

The DataGrid control is a bound control similar to a spreadsheet, and can display a series of rows and columns that represent the records and fields of the Recordset object. You can use the DataGrid to create an application that allows end users to read and write to most databases. The DataGrid control can be configured quickly at design time with little or no code. When the DataSource property of the DataGrid control is set at design time, the control is automatically populated with the recordset of the data source, and the control's column headers are automatically set. You can then edit the columns of the grid, delete, reschedule, add column headers, or adjust the width of any column.
At run time, you can switch DataSource in your program to view different tables, or you can modify the query for the current database to return a different collection of records.
Note that the DataGrid control is code-compatible with DBGrid in Visual Basic 5.0, except for one exception: the DataGrid control does not support the DBGrid "unbind mode" concept. The DBGrid control is included in the Tools directory of Visual Basic.

Possible usage
View and edit data in a remote or local database.
Used in conjunction with another data-bound control, such as a DataList control, using the DataGrid control to display a table record that is linked to the table displayed by the second data-bound control through a common field.

Using the design-time attributes of the DataGrid control
You can create a database application by not writing any code, only by using the design-time attributes of the DataGrid control. The following instructions outline the general steps in implementing a typical application of the DataGrid control. Complete step-by-step instructions, see the topic "DataGrid Scenario 1: Creating a simple database application using the DataGrid control."
To implement a DataGrid control at design time
1. Create a Microsoft data link for the database you want to access (. MDL) file. See the "Create a Northwind OLE DB Data Link" topic for an example.
2. Place an ADO data control on the form and set its ConnectionString property to the OLE DB data source created in step 1th.
3. Enter a entry in the RecordSource property of this Ado Data control to return a
The SQL statement for the recordset. For example, Select * from mytablename Where CustID = 12
4. Place a DataGrid control on the form and set its DataSource property to this 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. Reset the size of the grid, delete or add columns to the grid.
8. Right-click the DataGrid control, and then click Properties.
9. Use the Property Pages dialog box to set the appropriate properties for the control, and configure the grid to the desired appearance and behavior.

Change the displayed data at run time
After you create a grid that uses design-time attributes, you can also change the grid's data source dynamically at run time. The usual ways to implement this functionality are described below.

Change the recordsource of DataSource
The most common way to change the displayed data is to change the DataSource query. For example, if the DataGrid control uses an ADO data control as its DataSource, overriding recordsource and refreshing the ADO Data Control will change the display.
The ' ADO data control is connected to the Northwind database ' Products table. New query finds all
' SupplierID = 12 record.
Dim Strquery as String
strquery = "SELECT * from suppliers WHERE SupplierID = 12"
Adodc1.recordsource = strquery
Adodc1.refresh

Change DataSource
At run time, you can reset the DataSource property to a different data source. For example, you might have several ADO Data controls, each connected to a different database, or set to a different RecordSource property. You can simply reset DataSource from an ADO Data control to another ADO Data control:
' Reset the DataSource to one that is connected to the Pubs database,
' The ADO Data control using the Authors table.
Set DataGrid1.DataSource = adopubsauthors

Re-bind DataSource
When you use the DataGrid control with a remote database, such as SQL Server, you can change the structure of the table. For example, you can add a field to this table. In this case, you can call the Rebind method to recreate the grid based on the new structure. Note that if you have changed the layout of this column at design time, the DataGrid control will attempt to recreate the current layout, including any empty columns. However, you can force the grid to reset all columns by first calling the Clearfields method.

Return value from DataGrid
after the DataGrid is connected to a database, you may want to monitor which cell the user clicked. You can use the Rowcolchange event--not the Click event. As follows:
Private Sub datagrid1_rowcolchange (lastrow as Variant, ByVal lastcol as Integer)
' Displays information about the text, rows, and columns of the cell that 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 is formatted using the NumberFormat property. The NumberFormat property can change the format of any column that contains numbers without changing the actual data format. For example, given a grid that contains a column named ProductID that contains integers. The following code causes the DataGrid to display data in "P-0000" format. In other words, although the actual value contained 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 values contained in the database, use the Cellvalue method, as follows:
Private Sub Datagrid1_rowcolchange ( LastRow as Variant, ByVal lastcol as Integer)
Debug.Print _
Datagrid1.columns ("ProductID"). Cellvalue (Datagrid1.bookmark)
End Sub
Note that the cellvalue used above and the Celltext values used below require that the Bookmark property be used as a parameter to function correctly.
Conversely, if you want to return the formatted value of the field, you should 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 that the Celltext method above is equivalent to using the Text property of the DataGrid control.

Next
To read a step-by-step procedure for creating a simple application using the control, see "Creating a simple database application using the DataGrid control", or "creating a DataGrid that connects DataList controls."
To learn more about the Split object and how to program it, see "manipulating the DataGrid view."