Skip Headers
Oracle® Objects for OLE Developer's Guide
10g Release 2 (10.2)

Part Number B14309-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

14 Data Control Properties

This chapter describes the Oracle Data Control Properties. For an introduction to Data Control, see "Oracle Data Control".


See Also:

For more information, see the Microsoft Visual Basic help and documentation.

This chapter contains these topics:

The following properties apply to the OraDynaset object and to the Oracle Data Control.


AllowMoveLast Property

Applies To

Oracle Data Control

Description

Determines whether the user can move to the last record using the Data Control's MoveLast button. Read/write at design time and run time.

Usage

oradata1.AllowMoveLast = [True | False]

Remarks

By default, AllowMoveLast is True, in which case the user has no restriction upon record motion, even when moving to the last record may be very time consuming.

When AllowMoveLast is False, the Data Control's MoveLast button is grayed out and disabled. However, once the last record has been encountered (either because the user has navigated to the end of the set, or because code has positioned the record pointer to the last record), the button is enabled. This gives the user visual feedback about whether or not the entire query has been fetched. Setting this property to False does not prevent you from using the MoveLast method.

Changing this property has no effect until a Refresh method is sent to the data control.

Datatype

Integer (Boolean)


AutoBinding Property

Applies To

Oracle Data Control

Description

Determines whether the automatic binding of database object parameters will occur. Read/write at design time and run time.

Usage

oradata1.AutoBinding = [ True | False

Remarks

By default, AutoBinding is True, in which case the parameters in the OraParameters collection are bound to the SQL statement of the RecordSource property before data control refresh (before the SQL statement is executed). Technically speaking, the parameters are rebound when the recordset is re-created.

Setting Autobinding to False takes effect only if the SQL statement of the RecordSource property needs to be rebound and reexecuted. This is not the case when you simply change a parameter value and refresh the data control or simply refresh the recordset (the SQL statement only needs to be reexecuted). This is the case if you alter the RecordSource property and change the SQL statement.

Use this property to disable all parameter binding when executing a SQL statement that does not contain any parameters (using CreateDynaset, Refresh, or ExecuteSQL).

Changing this property does not take effect until a Refresh method is sent to the data control (and the appropriate conditions apply). Changing this property has no effect when a recordset.Refresh is executed.

Data Type

Integer (Boolean)

Example

This example demonstrates the use of AutoBinding to show how it affects data control and recordset refresh. Copy this code into the definition section of a new form containing the Oracle Data Control named oradata1, Then, press F5 to run.

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 creatingthe underlying database object so that parameters 
 'can be added.
 oradata1.Refresh
 
 'Set the RecordSource and use a SQL parameter for job.
 oradata1.RecordSource = "select * from emp where job = :job"
 
 'Add the job input parameter with initial value MANAGER.
 oradata1.Database.Parameters.Add "job", "MANAGER", 1
 
 'Add the deptno input parameter with initial value 10.
 oradata1.Database.Parameters.Add "deptno", 10, 1
 
 'Refresh the data control.
 oradata1.Refresh
 
 MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & _
                   oradata1.Recordset.fields("job")
 
 'Only employees with job=MANAGER will be contained in the dynaset.
'Turn off Automatic parameter binding.
 oradata1.AutoBinding = False
 
 'Change the value of the job parameter to SALESMAN.
 oradata1.Database.Parameters("job").Value = "SALESMAN"
 
 'Refresh ONLY the recordset.
 oradata1.Recordset.Refresh
 
 MsgBox "Employee #" & oradata1.Recordset.fields("empno") & ", Job=" & _
               oradata1.Recordset.fields("job")
 
 'The query will still execute even with AutoBinding=False
 'because the dynaset has not been re-created.
 'Set the RecordSource and use a SQL parameter for deptno.
 oradata1.RecordSource = "select * from emp where deptno = :deptno"
 
 On Error GoTo paramerr
 'Attempt to refresh the data control. An error should occur, because 
 ' AutoBind=False, the SQL statement contains a parameter, and the 
 'SQL statement needs to be bound before execution.
 oradata1.Refresh
 
Exit Sub
 
paramerr:
 MsgBox oradata1.Database.Session.LastServerErrText
Exit Sub
 
End Sub

BackColor Property

Applies To

Oracle Data Control

Description

Determines the background color of an object.


See Also:

ForeColor Property


Caption Property

Applies To

Oracle Data Control

Description

Determines the text displayed in or next to a control.


Connect Property

Applies To

Oracle Data Control

Description

The username and password to be used when connecting the data control to an Oracle database. Read/write at design time and run time.

Usage

oradata1.Connect = [ username/password 

Remarks

This string is passed to the OpenDatabase method of the OraSession object when the control is refreshed. Changing this property does not take effect until a Refresh method is sent to the data control.

If the data control is refreshed and the Connect property has not been specified, the refresh will fail.

Examples of valid Connect properties include:

"scott/tiger"
"system/manager"

Data Type

String


Database Property

Applies To

Oracle Data Control

Description

Returns the OraDatabase object associated with the data control. Not available at design time and read-only at run time.

Usage

oradatabase = oradata1.Database

Remarks

If the data control has not been refreshed, any references to this property results in an Object variable not set runtime error.

Changing this property has no effect until a Refresh method is sent to the data control.

Data Type

OLE Object (OraDatabase)


DatabaseName Property

Applies To

Oracle Data Control

Description

The Oracle SQL*Net specifier used when connecting the data control to an Oracle database. Read/write at design time and run time.

Usage

oradata1.DatabaseName = [ DatabaseName ]

Remarks

The Oracle SQL*Net specifier should include the Oracle SQL*Net protocol identifier, Oracle database name, and optional database instance. (SQL*Net aliases can also be used.) This string is passed to the OpenDatabase method of the OraSession object when the control is refreshed. Changing this property does not take effect until a Refresh method is sent to the data control.

If the data control is refreshed and DatabaseName has not been specified, the refresh fails.

Examples of valid DatabaseName properties include:

"t:oracle:PROD"
"p:Oracle10:demo"
"x:orasrv"
"mydbalias"(Where mydbalias represents "t:mfg:prod")

Data Type

String


DirtyWrite Property

Applies To

Oracle Data Control

Description

Determines whether or not Update and Delete will or will not check for read inconsistencies.

Usage

oradata1.DirtyWrite = [ True | False ]

Data Type

Integer (Boolean)

Remarks

By default, DirtyWrite is False, meaning that read consistency will be maintained for Update and Delete operation on underlying recordset/dynaset object. Changing this property has no effect until a Refresh method is sent to the data control.


DragIcon Property

Applies To

Oracle Data Control

Description

Determines the icon to be displayed as the pointer in a drag-and-drop operation.


DragMode Property

Applies To

Oracle Data Control

Description

Determines manual or automatic dragging mode for a drag-and-drop operation.


EditMode Property

Applies To

Oracle Data Control

Description

Returns the current editing state for the current row. Not available at design time and read-only at run time.

Usage

edit_mode = oradata1.EditMode

Remarks

The possible EditMode property values are:

Constant Value Description
ORADATA_EDITNONE 0 No editing in progress
ORADATA_EDITMODE 1 Editing is in progress on an existing row
ORADATA_EDITADD 2 A new record is being added and the copy buffer does not currently represent an actual row in the database.

These values are located in the oraconst.txt file and are intended to match similar constants in the Visual Basic oraconst.txt file.

This property is affected only by the Edit, AddNew, and Update methods.

Data Type

Integer


Enabled Property

Applies To

Oracle Data Control

Description

Determines whether the control can respond to user-generated events.


See Also:

Visible Property


Font Property

Applies To

Oracle Data Control

Description

Determines the font object to be used for text displayed in a data control.

Usage

Oradata1.Font.Bold = True

ForeColor Property

Applies To

Oracle Data Control

Description

Determines the foreground color used to display text and graphics in an object.


See Also:

BackColor Property


Height Property

Applies To

Oracle Data Control

Description

Determines the height dimension of an object.


Index Property

Applies To

Oracle Data Control

Description

Specifies the number that uniquely identifies a control in a control array. Available at design time only if the control is part of a control array; read-only at run time.


See Also:

Tag Property



Left Property

Applies To

Oracle Data Control

Description

Determines the distance between the internal left edge of an object and the left edge of its container.


MousePointer Property

Applies To

Oracle Data Control

Description

Determines the type of mouse pointer displayed when the mouse is over a particular part of a form or control at run time.


Name Property

Applies To

Oracle Data Control

Description

Specifies the name used in code to identify a form, control, or data access object. Not available at run time.


NoRefetch Property

Applies To

Oracle Data Control

Description

By default, NoRefetch is False, this means that default data set by Oracle Database will not be refetched to the local cache. If the ORADB_NO_REFETCH option is True, by default, the underlying recordset or dynaset will inherit this property.

Changing this property has no effect until a Refresh method is sent to the data control.

Usage

oradata1.NoRefetch = [ True | False ]

Data Type

Integer (Boolean)


Options Property

Applies To

Oracle Data Control

Description

Determines one or more characteristics of the database and all dynasets associated with the data control. Read/write at design time and run time.

Usage

oradata1.Options = database_optionsdatabase_options = oradata1.Options

Data Type

Long Integer

Remarks

This property is a bit flag word used to set the optional modes of the database. If options = 0, the default settings will apply. The following modes are available:

Options Property Flag Values

The Options property flag values are:

Constant Value Description
ORADB_DEFAULT &H0& Accepts the default behavior.
ORADB_ORAMODE &H1& Lets Oracle Database set default field (column) values.
ORADB_NOWAIT &H2& Does not wait on row locks when executing a SQL "SELECT ... FOR UPDATE" statement.

These values can be found in the oraconst.txt file. Options may be combined by adding their respective values.

This property is the same as the options passed to the OpenDatabase method. Just as with OpenDatabase, these options affect the OraDatabase object and all associated dynasets created from that database.

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


OracleMode Property

Applies To

Oracle Data Control

Description

Determine whether the changes made to fields (columns) are immediately reflected in the local mirror by retrieving the changed row from the database, thus allowing Oracle to set defaults for the columns and perform required calculations.

Usage

oradata1.OracleMode = [ True | False ]

Data Type

Integer (Boolean)

Remarks

This property value is set to True by default, which means that fields (columns) changes are reflected in the local cache immediately. Changing this property value has no effect until the Refresh method is invoked. If the ORADB_ORAMODE mode is used for the database option, the underlying recordset/dynaset inherits this mode.


ReadOnly Property

Applies To

Oracle Data Control

Description

Determines whether the dynaset will be used for read-only operations. Read/write at design time and run time.

Usage

oradata1.ReadOnly = [ True | False ]

Data Type

Integer (Boolean)

Remarks

By default, ReadOnly is False which means that an attempt will be made to create an updatable dynaset by selecting ROWIDs from the database. If ReadOnly is set to True, a non-updatable dynaset is created (ROWIDs are not selected from the database and cached) and operations will be somewhat faster.

If the SELECT statement contains a LONG or LONG RAW column, ROWIDs are needed whether the dynaset will be updatable or not.

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


Recordset Property

Applies To

Oracle Data Control

Description

Returns a dynaset defined by the data control's Connect, DatabaseName, and RecordSource properties. Not available at design time and read and write at run time.

Usage

Set oradynaset = oradata1.RecordsetSet oradata1.Recordset = Oradynaset

Data Type

OLE Object (OraDynaset)

Remarks

The properties and methods of this dynaset are the same as those of any other dynaset object. The Recordset property of the Oracle Data Control (.OCX) can be set to external dynaset, or the Recordset property of the other data control. After the setting, Oracle Data control Database, session, and options properties now set to the corresponding properties of the external dynaset. Oracle data control shares the advisories of the external dynaset. This is very useful when attaching dynaset returned from the PL/SQL cursor by CreatePlsqlDynaset Method.

Example

This example demonstrates setting Recordset property to external dynaset created by CreatePlsqlDynaset method. This example returns a PL/SQL cursor as a external dynaset for the different values of DEPTNO parameter. Make sure that corresponding stored procedure (found in EMPCUR.SQL) is available in the Oracle Database. Copy this code into the definition section of a form containing the Oracle Data Control named oradata1. Then, press F5.

Sub Form_Load ()
 
 'Declare variables as OLE Objects.
 Dim OraSession As OraSession 
 Dim OraDatabase As OraDatabase
 Dim OraDynaset As OraDynaset 
 
 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")
 
 'Create the OraDatabase Object by opening a connection to Oracle.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
' Create the Deptno parameter 
 OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT
 
 OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER  
 
' Create OraDynaset based on "EmpCursor" created in stored procedure. 
 Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)
 
' Now attach the Oradynaset to Data control's recordset.
  set oradata1.recordset = OraDynaset
 
...
'  Do some operation
...
 
 ' Now set the deptno value to 20
 OraDatabase.Parameters("DEPTNO").Value = 20
 
 'Refresh the sqlstmt
 Oradata1.recordset.Refresh
 
  'Remove the parameter.
 OraDatabase.Parameters.Remove ("DEPTNO")
 
 End Sub


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


Session Property

Applies To

Oracle Data Control

Description

The session object associated with the data control. Not available at design time and read-only at run time.

Usage

orasession = oradata1.Session

Data Type

OLE Object (OraSession)

Remarks

This property is equivalent to referencing oradata1.Database.Session. If the data control has not been refreshed, any references to this property result in an Object variable not set runtime error.


Tag Property

Applies To

Oracle Data Control

Description

Stores any extra data needed by your application.


Top Property

Applies To

Oracle Data Control

Description

Determines the distance between the internal top edge of an object and the top edge of its container.


TrailingBlanks Property

Applies To

Oracle Data Control

Description

Determines whether trailing blanks should be removed from character string data retrieved from the database. Read/write at design time and run time.

Usage

oradata1.TrailingBlanks = [ True | False ]

Data Type

Integer (Boolean)

Remarks

By default, TrailingBlanks is False. This means that trailing blanks will be removed from character string data retrieved from the database.

Changing this property has no effect until a Refresh method is sent to the data control.


Visible Property

Applies To

Oracle Data Control

Description

Determines whether an object is visible or hidden.


See Also:

Enabled Property


Width Property

Applies To

Oracle Data Control

Description

Determines the width dimension of an object.