AutoBinding Property 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, 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 creating

'the 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