Use of the DataGrid control in VB (II.)

Create an OLE DB data link for Northwind
An important step in accessing data is to create an OLE DB data source for each database that you want to access. The following steps create an object for the Nwind.mdb (Northwind) database that is provided by Visual Basic. This data source is used for some of the sample procedures provided by the Visual Basic document. Only one OLE DB data source needs to be created on a single computer.

To create an OLE DB data source for Northwind
1. Open Windows Explorer or Windows NT Explorer.
2. Open the directory where you want to create the OLE DB data source. In this example, open ProgramFiles, Microsoft Visual Studio, and VB98.
3. Right-click the right-hand pane of the Explorer, and then click New on the context menu. From the list of file types, click Microsoft Data Link.
4. Rename the new file NORTHWIND.MDL.
5. Right-click the file and click Properties on the context menu to display the Northwind.mdlproperties dialog box.
6. Click the Connection tab.
7. Click the Provider box and select Microsoft Jet 3.51 OLE DB Provider.
8. Enter the path to the Nwind.mdb file in the Data Source box.
9. Click "Test Connection" to detect the connection.
10. If the connection is passed, click OK.
Note You can also create an OLE DB data source by clicking the Data Link icon in Control Panel. In the Manage Data Link Files dialog box, click New to create a new data source.

Using the DataGrid and ADO data controls to create a simple database application
Using only one DataGrid and one ADO data control, you can create a database application that allows end users to read and write to the recordset.

To use the ADO data control to create a simple database application
1. Create an OLE DB data source for the Northwind database. If you have not yet created a data source, follow the steps in the "Create Northwind OLE DB data Link".
2. Create a new standard EXE project in Visual Basic. If the DataGrid control is not in the Toolbox, right-click the Toolbox, and then use the Parts dialog box to add the control. The ADO control is also loaded.
3. Place a single instance of the control on an empty form.
4. Set the ConnectionString property of the ADO control to the data source of Northwind. Click and select the ADO Data control and press the F4 key to appear in the Properties window. Click ConnectionString, and then click OLE DB File. Click the Northwind data source.
5. Set the RecordSource property of the ADO control. In the Properties window, click Record Source and enter an SQL statement to populate the DataGrid
Control. In this example, enter the SELECT * FROM products.
6. Set the DataSource property of the DataGrid control to this ADO Data control. Click and select the DataGrid control. In its Properties window, clicking Data source will appear with a drop-down list of all data controls--in this case, the ADO data control only. Click the control.
7. Press F5 key to run this project.

Create a datagriddata that connects DataList controls
The common use of Grid is to display the "details" provided by a table in a database. For example, the Northwind (Nwind.mdb) database includes two tables, one named "Suppliers" and the other named "Products." In this case, we use the DataList control to display the company name of the vendor in the "Suppliers" table. This DataList control will provide the company's SupplierID when the user clicks any company name. Using this identifier, you can construct a query that retrieves all records with matching SupplierID in the Products table. In other words, when the user clicks a company (in the DataList control), all products that the company produces will appear in the DataGrid control.
To populate a DataGrid control with a product that specifies a vendor
1. Confirm that an OLE DB data source has been established for the Northwind database on the machine, and if you have not yet created such a data source, follow the steps in the "Create Northwind OLE dbdata connection."
2. Create a new standard EXE project in Visual Basic.
If the DataGrid, DataList, and ADO Data controls are not in the Toolbox, right-click the Toolbox, and then click Parts. In the Parts dialog box, double-click Microsoftdatagrid Control, Microsoft DataList Controls, and Microsoft Adocontrol.
3. Place an instance of the DataGrid and DataList control in an empty form. Place the DataList control in the upper-left corner of the form, and then place the DataGrid control somewhere below it.
4. Place two ADO Data control instances on the form. Select the first ADO Data control and press the F4 key to display its property pages. Set the control's Name property to Adosuppliers. Select the second ADO Data control and set its Name property to Adoproducts. Place the first control directly below the DataList control, placing the second control directly underneath the DataGrid control.
5. Set the ConnectionString property of these two ADO data controls to the OLE DB data source for Northwind. Select the control named Adosuppliers, and then set its ConnectionString property to the OLE DB data source (NORTHWIND.MDL) of Northwind. Select the control named Adoproducts and repeat the operation.
6. Set the RecordSource property of these two ADO Data controls. Select Adosuppliers and click Recordsoure on its property pages. Enter select* from suppliers. This query instructs the ADO Data control to return all records in the Suppliers table. Select Adoproducts, click Recordsoure, and enter the Select *from products. This query will return all the records in 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 a table that is famous as suppliers. At run time, the DataList control displays the value of the field specified in this property. In this case, the property will display a company name found in the Suppliers table.
9. Set the BoundColumn property of the DataList control to SupplierID.
The BoundColumn property is set to the second field in the Suppliers table. In this case, this property is set to the SupplierID field. When you click the DataList control, the Boundtext property returns the value of the SupplierID field associated with the company that is displayed in the DataList control. This value will be used for queries against 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 the records in the Products table.
11. In the code module of the form, add the following:
Private Sub Datalist1_click ()
' declares a string variable to contain the new query. of this new
' Query uses the Boundtext property of the DataList control
' To provide a SupplierID value. New query finds all
' Products with the same SupplierID. This query is
' Assign to the ADO Data control named Adoproducts
' The RecordSource property. After the control is refreshed, the DataGrid
' will be used to include all products supplied by the same company with a new
' Recordset to update.

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.
Clicking on any company name in the DataList control automatically updates the DataGrid control with the products that the company supplies.