RecordSource Property

Applies To

Oracle Data Control

Description

The SQL SELECT statement to be used to create the data control's RecordSet. Read/write at design time and run time.

Usage

oradata1.RecordSource = [ SQL SELECT Statement ]

Data Type

String

Remarks

The SQL statement must be a SELECT statement; otherwise an error is returned. Features such as views, synonyms, column aliases, schema references, table joins, nested selects, and remote database references can be used freely; object names are not modified in any way.

Whether or not the resultant dynaset can be updated depends on the Oracle SQL rules of updatability, the access you have been granted, and the ReadOnly property. In order to be updatable, three conditions must be met:

1. The SQL statement must refer to a simple column list or to the entire column list (*).

2. The SQL statement must not set the read-only flag of the options argument.

3. Oracle must permit ROWID references to the selected rows of the query.

Any SQL statement that does not meet these criteria is processed, but the results are not updatable and the dynaset's Updatable property returns False.

Changing this property does not take effect until a Refresh method is sent to the data control.

You can use SQL bind variables in conjunction with the OraParameters collection.

If this property is NULL or empty, then an OraDynaset object is not created, but OraSession, OraConnection, and OraDatabase objects are created for the data control. This behavior enables access to these objects prior to creation of a dynaset. For example, a NULL RecordSource might be used to instantiate the database object to add parameters. The RecordSource property can then be set at run time, making use of the automatic binding of database parameters.

Changing this property and calling the Refresh method of the RecordSet property will create a new dynaset object, but the old dynaset continues to be available for use until all references to it are removed.

Example

This example demonstrates the use of SQL bind variables (parameters) in the RecordSource property of the data control. To run this demonstration, copy this code into the definition section of a form containing a data control named oradata1, then, press F5.

Sub Form_Load ()
 
 'Set the username and password.
 oradata1.Connect = "scott/tiger"
 
 'Set the databasename.
 oradata1.DatabaseName = "ExampleDb"
 
 'Refresh the data control without setting the
 ' RecordSource. This has the effect of creating
 
 ' the underlying database object so that parameters  may be added.
 oradata1.Refresh
 
 'Set the RecordSource and use a SQL parameter.
 oradata1.RecordSource = "select * from emp where job = :job"
 
 'Add the job input parameter with initial value MANAGER.
 oradata1.Database.Parameters.Add "job", "MANAGER", 1
 
 'Refresh the data control.
 'Only employees with the job MANAGER will be contained in the dynaset.
 oradata1.Refresh
 
 'Change the value of the job parameter to SALESMAN.
 oradata1.Database.Parameters("job").Value = "SALESMAN"
 
 'Refresh ONLY the recordset.
 'Only employees with the job SALESMAN will be contained in the dynaset.
 oradata1.Recordset.Refresh
 
End Sub