SQL Property Example

This example demonstrates the use of parameters, the Refresh method and the SQL property to restrict selected records. Copy and paste this code into the definition section of a form. 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 a parameter with an initial value.

OraDatabase.Parameters.Add "job", "MANAGER", 1

'Create the OraDynaset Object.

Set OraDynaset = OraDatabase.CreateDynaset("select * from emp where job=:job", 0&)

'Notice that the SQL statement is NOT modified.

MsgBox OraDynaset.SQL

'Currently, OraDynaset only contains employees whose

'job is MANAGER.

'Change the value of the job parameter.

OraDatabase.Parameters("job").Value = "SALESMAN"

'Refresh the dynaset.

OraDynaset.Refresh

'Currently, OraDynaset only contains employees whose

'job is SALESMAN.

'Notice that the SQL statement is NOT modified.

MsgBox OraDynaset.SQL

'Remove the parameter.

OraDatabase.Parameters.Remove ("job")

End Sub