Forces an immediate update of the dynaset given the current Connect
, DatabaseName
, and SQL properties.
Forces an immediate update of the dynaset by reexecuting the SQL statement in the SQL statement object.
This method cancels all edit operations (Edit
and AddNew
methods), executes the current contents of the SQL statement buffer, and moves to the first row of the resulting dynaset. Any dynaset objects created before issuing the Refresh
method, including bookmarks, record counts, and field collections, are considered invalid. The OraConnection
and OraSession
objects associated with the previous dynaset remain unchanged.
Performing a refresh operation with this method can be more efficient than refreshing with a data control. This method also lets you execute a modified SQL statement without creating a new dynaset or OraSQLStmt
object.
The preferred refresh methods when changing parameter values are oradynaset.Refresh
or orasqlstmt.Refresh
, because required database operations are minimized (SQL parsing, binding, and so on). This can improve performance when only parameter values have changed.
If you call the Refresh
method after assigning an invalid SQL statement to the SQL property of a dynaset or SQL statement object, these objects remain valid. However, a dynaset in this state does not permit any row or field operations. Bound controls also exhibit unusual behaviors similar to those that occur when the standard Visual Basic data control RecordSource
is set to an invalid SQL statement at run time and then refreshed.
You can regain the normal dynaset and SQL statement operations by refreshing the object with a valid SQL statement. The Refresh
method treats Null
or empty SQL statements as invalid.
Refresh Method Example (OraDynaset)
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 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
Refresh Method Example (OraSQLStmt)
This example demonstrates the use of parameters, the Refresh
method, and the SQL property for the . object. Copy and paste this code into the definition section of a form. Then, press F5.
Sub Form_Load () 'Declare variables Dim OraSession As OraSession Dim OraDatabase As OraDatabase Dim OraSqlStmt As OraSQLStmt '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&) OraDatabase.Parameters.Add "EMPNO", 7369, 1 OraDatabase.Parameters("EMPNO").ServerType = 2 'ORATYPE_NUMBER OraDatabase.Parameters.Add "ENAME", 0, 2 OraDatabase.Parameters("ENAME").ServerType = 1 'ORATYPE_VARCHAR2 Set OraSqlStmt = OraDatabase.CreateSQL("Begin Employee.GetEmpName (:EMPNO," & _ ":ENAME); end;", 0&) 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Should display SMITH MsgBox OraDatabase.Parameters("ENAME").Value 'Change the value of the empno parameter. OraDatabase.Parameters("EMPNO").Value = 7499 'Refresh the dynaset. OraSqlStmt.Refresh 'Should display ALLEN MsgBox OraDatabase.Parameters("ENAME").Value 'Notice that the SQL statement is NOT modified. MsgBox OraSqlStmt.SQL 'Remove the parameter. OraDatabase.Parameters.Remove ("job") End Sub