An important feature of True DBList is its ability to automatically sense changes to the database at run time. In this tutorial, you will learn how to use True DBList to display the results of ad-hoc SQL queries. Note that no code is necessary to tell the list what to do---the list will automatically change its field layout to match the new configuration of the query result. Also note that in order for the list 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 list's Clear Fields context menu command to remove it.This will allow the list to configure itself automatically.

1. Start a new project.

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

   

3. Set the DatabaseName (Data control) property of Data1 to TDBLDemo.mdb, and the RecordSource (Data control) property to Customer.

4. Set the RowSource property of TDBList1 to Data1.

5. Set the Caption property of Command1 to Execute SQL and the MultiLine (TextBox control) property of Text1 to True.  Set the Text (TextBox control) property to Enter SQL statement here.

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 TDBList1.SetFocus Exit Sub SQLErr: MsgBox "Error Executing SQL Statement" Exit Sub End Sub

Run the program and observe the following:

• As in Tutorial 1, the True DBList control 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.

• In the TextBox control, type the following SQL statement:

  Example Title	Copy Code
  Select * from Customer

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

• 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 list responds by displaying only one column for the Company field.

• 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.

• In the TextBox control, type the following SQL statement:

  Example Title	Copy Code
  Select Count(*) from Customer

  then press the Execute SQL command buttonThe above SQL statement uses an aggregate function, Count(*), to return the total number of records in the Customer table.Even though the SQL result is not a set of records, the list 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.

• 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 list also displays the (assigned) calculated column names, ULAST and UFIRST, as the column headings.

• 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.

• 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.

To end the program, press the End button on the Visual Basic toolbar. You have successfully completed Tutorial 5.

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