An important feature of True DBGrid is its ability to automatically sense changes to the database at run time. In this tutorial, you will learn how to use True DBGrid to display the results of ad-hoc SQL queries. Note that no code is necessary to tell the grid what to do—the grid will automatically change its field layout to match the new configuration of the query result. Also note that in order for the grid to automatically respond to field layout changes, you must not have defined any column properties at design time. If a layout is already defined, use the grid's Clear Fields context menu command to remove it. This will cause the grid to configure itself automatically at run time.

1. Start a new project.

2. Place a Data control (Data1), a True DBGrid control (TDBGrid1), a command button (Command1) and a TextBox control (Text1) on the form (Form1) as shown in this figure.

   

3. Set the DatabaseName property of Data1 to TDBGDemo.MDB, and the RecordSource property to Customer.

4. Set the DataSource property of TDBGrid1 to Data1.

5. Set the Caption property of Command1 to "Execute SQL" and the MultiLine control property of Text1 to True.

6. Add the following code to Command1:

   Example Title	Copy Code
   Private Sub Command1_Click() ' Execute the SQL statement in Text1, and trigger an error message if something goes wrong. On Error GoTo SQLErr Data1.RecordSource = Text1.Text Data1.Refresh TDBGrid1.SetFocus Exit Sub SQLErr: MsgBox "Error Executing SQL Statement" End Sub

   Run the program and observe the following:

   As in Tutorial 1, True DBGrid retrieves the database schema information from the Data control and automatically configures itself to display the data for all fields in the database table. Note that the field names are used as the default column headings.

7. In the TextBox control, type the following SQL statement:

   Example Title	Copy Code
   Select * from Customer

   then press the Execute SQL command button. The grid display will not change. The above SQL statement displays all fields from the Customer table and is equivalent to the default display.

8. In the TextBox control, type the following SQL statement:

   Example Title	Copy Code
   Select Company from Customer

   then press the Execute SQL command button. The grid responds by displaying only one column for the Company field.

9. In the TextBox control, type the following SQL statement:

   Example Title	Copy Code
   Select LastName, Company from Customer

   then press the Execute SQL command button. This is similar to the previous SQL statement except that two columns (LastName and Company) are now displayed.

10. In the TextBox control, type the following SQL statement:

    Example Title	Copy Code
    Select Count(*) from Customer

    then press the Execute SQL command button. The above SQL statement uses an aggregate function, Count(*):SQL, to return the total number of records in the Customer table. Even though the SQL result is not a set of records, the grid faithfully responds by displaying the number of records in a single column. By default, Expr1000 is used as the column heading, indicating that the display is the result of an expression.

    To display a more meaningful heading, you can type:

    Example Title	Copy Code
    Select Count(*) As Count from Customer

    The column heading will display Count instead of Expr1000.

11. In the TextBox control, type the following SQL statement:

    Example Title	Copy Code
    Select UCase(LastName) as ULAST, UCase(FirstName) AS UFIRST from Customer

    then press the Execute SQL command button. The above SQL statement produces two calculated columns which display the LastName and FirstName fields in upper case. The grid also displays the (assigned) calculated column names, ULAST and UFIRST, as the column headings.

12. In the TextBox control, type the following SQL statement:

    Example Title	Copy Code
    SELECT * FROM Customer WHERE FirstName = "Jerry"

    then press the Execute SQL command button. The above SQL statement displays only records with FirstName equal to Jerry.

13. In the TextBox control, type the following SQL statement:

    Example Title	Copy Code
    SELECT * FROM Customer ORDER BY LastName

    then press the Execute SQL command button. The above SQL statement displays records in alphabetical order according to the LastName field.

    You can also use an SQL statement to join two database tables, as demonstrated in Tutorial 3.

This concludes Tutorial 2.

Copyright (c) GrapeCity, inc. All rights reserved.