SELECT statement to be used to create the data control's
RecordSet. Read/write at design time and run time.
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:
The SQL statement must refer to a simple column list or to the entire column list (*).
The SQL statement must not set the read-only flag of the options argument.
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
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
If this property is
NULL or empty, then an
OraDynaset object is not created, but
OraDatabase objects are created for the data control. This behavior enables access to these objects prior to creation of a dynaset. For example, a
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.
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